（译）走进SQL Server 2005 tempdb数据库（五）

要使SQL SERVER平稳的运作，必须把tempdb的共享资源管理好。一个低下的查询或者一个设计差的应用程序可能会导致tempdb的资源问题，从而使得整个SQL SERVER瘫痪下来。做为一个数据库管理员，应该对下面的几种类型的资源进行有效的管理：

 

       .      空间问题。这个包括数据和日志文件的使用空间以及未使用空间。

       .      I/O瓶颈问题。

       .      DML操作中的资源争夺。这个包括tempdb中所有对象的页的分配和释放。

    .  DDL操作中的资源争夺。这个包括用户对象创建或删除时对元数据的操作，但注意，    

内部对象是没有反映在元数据表中的。

 

       对于管理tempdb有非常大的挑战，其中之一是：你没有办法在tempdb数据库中分割出基于资源基础上的用户数据库，应用程序，以及用户会话。你甚至不能分割出基于对象类型的资源（如版本存储）。在SQL SERVER里，这些资源通过一个全局的池来管理。SQL SERER 2005提供了一套丰富的工具来监控这些资源，让您可以隔离应用程序或者那些耗费了严重资源的查询，从而可以进行有效的调整。

 

       这个章节描述了哪些资源你需要监控以及有哪些可以利用的工具来完成这些操作，同时还包括了涉及一些特殊资源问题的诊断方法。采取积极的监控可以让您在早期发现问题，并实施相关的解决方法，避免出现后期的灾难性问题。在SQL SERVER 2005中，在监控tempdb的方法中有了重大的改进。

 

Tempdb的空间

 

     大多数间接的（例如通过WORKTABLES）或者直接的（例如用户定义的临时表）查询以及DML语句会在tempdb数据库分配空间。如果tempdb没有空间，数据库将变得不可用。您可能使用完了所有空间，当tempdb达到它的最大值限制或者没有足够的物理磁盘空间存储它的数据或者日志文件。如果在这种情况下，唯一可以做的是杀掉一个或者多个进程/事务，来释放tempdb的空间。

       在SQL SERVER 2005中，管理tempdb的空间会更加重要，因为有很多新特性（例如：行版本，触发器，在线索引等）都会使用大量的空间。因为tempdb对于整个SQL SERVER是一个全局的资源，除非对应用程序的运行是充分的了解，否则很少有管理员可以控制它的空间使用。只能通过监控，在其空间达到我们的上线值之前，采取一些必要的措施。

 

监控空间

 

       这个章节介绍了在监控tempdb空间中，你可以利用的性能计数以及动态视图（DMVs）

 

 

 

性能计数

 

       Database: Log File(s) Size(KB)

              返回数据库中所有日志文件的累计大小。如果你还没有设置tempdb的日志文件的

大小值，这个值是可以增加的。

 

       Database: Log File(s) Used(KB)

              返回数据库中所有日志文件的累计使用大小。

 

       Free Space in tempdb(KB) (SQL Server 2005)

返回tempdb中的剩余空间。数据库管理员可通过这个性能计数来判断是否tempdb运行在不足的剩余空间中，以便可采取适当的处理。这个计数说明了tempdb中的三种类型的空间分配。

 

       Version Store Size(KB) (SQL Server 2005)

监控版本存储的空间大小。如果一个版本存储没有被收缩，意味着阻止这个操作的一个很大的事务的长期运行。

 

       Version Generation Rate(KB/s) (SQL Server 2005)

              监控版本存储中，每秒生成的版本比率（千字节）。

 

       Version  Cleanup Rate(KB/s) (SQL Server 2005)

监控版本存储中，每秒清除的版本存储比率（千字节）。如果版本存储清除的计数比版本存储生成的低，那么版本存储就会越来越占用tempdb的空间。但是，如果版本存储清除的计数是0，而版本生成的计数不是0，则有可能是一个长期运行的很大的事务阻止了版本存储的清除。

 

 

动态视图

 

      SQL SERVER 2005提供了一套丰富的动态视图来跟踪tempdb的空间使用。你可以使用这些视图分别在实例级别，会话级别或者单独的任务（这个任务可以是一个批处理）去跟踪它的空间使用情况。

 

       Sys.dm_db_file_space_usage

 

       这个动态视图返回了与tempdb相关联的空间分配信息。对于实例，这个信息可通过对对象类型（用户，内部，版本存储）进行分组，下面的代码是一个例子：

 

SELECT

SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8  as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

 

接下来的这个例子输出了当一个查询通过HASH连接创建了一个worktable。

 

usr_obj_kb internal_obj_kb version_store_kb freespace_kb mixedextent_kb

8192            4608                 0                3840        1024

 

你可以通过输出信息了解到tempdb的空间的被使用情况，下面几点可以帮助你对这个输出信息进行分析。

 

       .对于用户对象，如果有着较高的百分比分配，意味着这些被应用程序（例如全局和局部的临时表或者表变量）创建的对象是tempdb空间主要的占用者，但它并不一定是值得需要关注的问题。

 

       .对于内部对象，如果有着较高的百分比分配，意味着查询计划占用了比较打的tempdb空间，这不一定是个问题，但你需要看看这些执行计划是否可以重新调整或者添加一些索引，以最大限度的减少tempdb空间的使用。

 

       .对于版本存储，如果有着较高的百分比分配，意味着版本存储的清除与版本存储的生成并不能达到平衡。检查一下是否有长期运行的大的事务阻止了版本存储的清除，或者是一个很大的事务每分钟产生了非常多的版本存储。后台进程每分钟会清除版本存储。

 

       Sys.dm_db_session_file_usage

 

       这个动态视图跟踪了活动会话在tempdb中历史的页的分配和释放信息。当一个用户连接到数据库，这个会话就会被监听。除非连接中断，不然这个会话一直是活动的。在会话活动期间，它有可能提交提交了一个或者多个批处理。一旦批处理完成，这个动态视图就会跟踪其空间的使用情况。下面的代码例子显示了哪5个会话分配了最大的空间，对于用户和系统对象。请注意，这个结果没有考虑版本存储的影响：

 

SELECT top 5 *

FROM sys.dm_db_session_space_usage 

ORDER BY (user_objects_alloc_page_count +

   internal_objects_alloc_page_count) DESC

 

       Sys.dm_db_task_space_usage

 

       这个动态视图跟踪了当前执行任务（包括批处理）对tempdb页的分配和释放的信息。当tempdb空间被使用完的情况下，这个动态视图是非常有用的。通过这个视图，你可以看到哪些任务占用了非常多的tempdb空间，并可以有选择性的杀掉其进程。你可以分析为什么这个任务占用了如此多的空间，并做适当的优化。你还可以关联其他的动态视图以得到其执行计

 

划，从而更深层次的去分析它。下面的代码显示了哪5个任务占用了最多的tempdb空间。请注意，这个结果没有考虑版本存储的影响：

 

SELECT top 5 *

FROM sys.dm_db_task_space_usage

ORDER BY (user_objects_alloc_page_count +

   internal_objects_alloc_page_count) DESC

 

       下一次，将介绍tempdb空间的诊断，请随时关注。