《Microsoft Sql server 2008 Internals》读书笔记--第三章Databases and Database Files(3)

 《Microsoft Sql server 2008 Internals》读书笔记--目录索引

 

在上篇文章中，主要简单介绍了数据库的存放机制和snapshot的简单应用。下来我们看看一个基本的系统数据库Tempdb,关于tempdb，MSDN有详细的描述，

请参考;http://msdn.microsoft.com/zh-cn/library/ms190768.aspx

一些关于tempdb的基本常识：

tempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存下列各项： * 显式创建的临时用户对象，例如全局或局部临时表、临时存储过程、表变量或游标。 * SQL Server 数据库引擎创建的内部对象，例如，用于存储假脱机或排序的中间结果的工作表。 * 由使用已提交读（使用行版本控制隔离或快照隔离事务）的数据库中数据修改事务生成的行版本。 * 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。tempdb 中的操作是最小日志记录操作。这将使事务产生回滚。每次启动 SQL Server 时都会重新创建 tempdb，从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。不允许对 tempdb 进行备份和还原操作。 tmpdb的主数据逻辑名为tempdev，文件名为tempdb.mdf,10%自增长到硬盘空间满。日志逻辑名为templog,文件名为templog.ldf,10%自增长到2TB。tempdb 的大小可以影响系统性能。例如，如果 tempdb 的大小太小，则每次启动 SQL Server 时，系统处理可能忙于数据库的自动增长，而不能支持工作负荷要求。可以通过增加 tempdb 的大小来避免此开销。不能对 tempdb 数据库执行以下操作： * 添加文件组。 * 备份或还原数据库。 * 更改排序规则。默认排序规则为服务器排序规则。 * 更改数据库所有者。tempdb 的所有者是 dbo。 * 创建数据库快照。 * 删除数据库。 * 从数据库中删除 guest 用户。 * 启用变更数据捕获。 * 参与数据库镜像。 * 删除主文件组、主数据文件或日志文件。 * 重命名数据库或主文件组。 * 运行 DBCC CHECKALLOC。 * 运行 DBCC CHECKCATALOG。 * 将数据库设置为 OFFLINE。 * 将数据库或主文件组设置为 READ_ONLY。

以上部分来自MSDN,现在补充一些：

１、tempdb和其他数据库最大的不同是：它每次在SQL Server启动时是被重建(Re-Create)的，而不是被复原(Recovered)的。我们可以把它看成一个工作空间，有点像Eclipse或powerDesigner的工作空间(workspace),这个workspace存放了临时的用户对象和内部对象(internal Objects),这些对象被SQL自身显式创建。

２、 每次tempdb被重建时，它会从model数据库继承大多数的数据库选项，但是还原模式下不会copy这些选项，因为　tempdb总是使用简单恢复(Simple recovery)。

３、在简单恢复模式(SIMPLE recovery model)下 ,tempdb的日志不断被清空,并且不能被恢复。因为每次SQL重新启动时，前一个用户创建的所有临时对象都消失了。

４、日志文件与其他日志不同，仅仅保存了一些用于回滚事务的必要信息，而不能恢复非事务以外的其他信息，

５、还原一个正在运行的数据库的第一步，是创建快照。我们无法还原tempdb,是因为我们无法创建它的一个快照。 这意味着我们不能使用DBCCCheckDB选项，另外一个区别是DBCC收缩时，SQL Server将跳过所有的分配(Allocation)和分类(Catalog)检查。

tempdb的对象(Objects)

tempdb存储的对象包括：用户对象、内部对象和一些版本存储，主要是用于快照隔离。

用户对象(user Objects)包括所有以#或##开头的临时表，还有表变量(table variables)和表值函数(table-valued function) ,所有这些需要空间来物理存放。

内部对象(intenal Objects) 用正常的工具看不到，但他们仍然占用空间，这些对象不能被目录视图(catalog View)列出，因为，它们的元数据是存放在内存中的，三种基本类型的内部对象是：工作表(work tables)、工件文件（work files)、排序单元(sort units)

在执行下列操作时工作表被SQL Server创建：

１、 执行一个大查询(large query)时后台存放中间结果。

２、运行DBCC CHECKDB或DBCC CHECKTABLE

3、与XML变量或Varchar(Max)一起工作时

４、处理数据库中问对象(Broker Objects)时

５、与静态或键集(keyset)的游标一起工作时

工作文件，在SQL Server处理一个哈希操作或关联/聚合查询时被使用。

排序单元在执行一个排序子句时被创建，排序单元保存了一些被排序的数据，通常是order by 或聚合操作的排序结果

版本存储(version store)提供了对Row-Level行级数据的支持，所有已更新的旧行在以下情况下被保留：

１、当一个Alter trigger被触发时

２、在一个允许快照事务的数据库中，当一个DML(Data Modification Language)语句被执行时

3、当一个MARS(multiple active result sets) 被客户端应用程序调用时。

４、创建一个在线索引或索引中有一个并发的DML语句时重建索引。

 

tempdb的优化

tempdb的优化可以参看MSDN:

 http://msdn.microsoft.com/zh-cn/library/ms175527.aspx

这里再补充一些：

１、tempdb有可能是生产环境中创建或删除新对象最多的数据库，如果可能，请采用多个数据文件的形式，这样.SQL Server会自动进行有效的空间分配。并会计划每个单独文件的自由空间。

２、如果需要删除(drop)一个工作表，或者一个小于8M的用户对象时，一个IAM和一个extent（范围）被存储，这会重新引起空间的再分配，这时需要借助tempdb的cache功能。

３、删除(drop)一个大表时，所有数据库的删除立即进行，不必线程等待，一个后台线程会清除所有分配给已删除表的空间，但此时tempdb的分配空间仍然未变。

 优化有最佳实践：

１、默认情况下，tempdb数据库创建时只有一个数据文件，你可能会发现，用多个文件会使你的I/O性能更佳，并且在全局分配结构（如GAM,SGAM,and PFS页)减少争夺(contention)，一个推荐的初始化设置是每CPU一个Data File,不过，你最好根据自己的数据量和使用模式(usage pattern)作测试。为了取得计划填充算法的最佳效率，各个文件应该保持一致的大小。多个文件的副作用是每个对象将拥有多个IAM页，这将增加访问该对象时切换成本。无论一个或多个文件，你都应该把数据文件放在最快的分区。一个日志文件足够，但也最好放在最快的分区。注意，邀月提醒：默认文件tempdb位于初始安装目录下，所以SQL2008强烈不推荐安装在默认的X:/Program Files/Microsoft SQLServer/MSSQL10.AGRONET08/MSSQL/DATA/下,可以像我这样，G:/SQL2008/MSSQL10.AGRONET08/MSSQL/DATA、这个G盘的性能是最佳的,呵呵。

２、 为了决定tempdb的最优大小。请结合你的数据量和应用程序做测试，但是知道tempdb什么时候和怎样被使用将有助于你作初步的评估。记住：每个SQL实例只有一个tempdb,一个很蹩脚的应用程序将会影响到所有其他应用程序中的所有用户。

３、不建议收缩tempdb数据库，特别是自动收缩选项将直接被忽略。收缩tempdb的最佳方式是alter database改变文件大小，然后停止并重启SQL Server以便tempdb被重建到完全的大小。你应该允许tempdb自增长以防止空间不够而出错，但文件的大小完全由自增长来控制是更错误的做法，文件的大小还是应该籍由计划测试得出。

下面是一些最佳做法的小提示：

• 充分利用tempdb对象缓存
• 事务尽可能的短，特别是使用事务隔离,MARS或触发器的事务。
• 如果你预计更多的分配页面争夺，尽量强制查询计划更少的使用tempdb。原文是：If you expect a lot of allocation pages contention,force a query plan that uses tempdb less.
• 通过保持列固定大小而不是可变大小，避免空间的分配和释放，例如update就比"一个delete紧跟一个insert"要好的多。
• 在同一个数据库实例中，如果版本(versioning)被使用，不要混合位于不同数据库中的长事务与短事务。

tempdb的空间监测

只有一个视图可以用于tempdb的空间监测，sys.dm_db_file_space_usage

 小结：tempdb是一个很重要也很独特的系统数据库，本文对其基本特性作了一些描述，后面的章节将会继续提到相关的内容。

下面一章将是第五章Tables，更具有实战意义的一章。

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]