SQL Server 2005性能排错(15)

同样的你可以运行下列查询，找到查询计划确定是否当选择了较差的计划时有大量的I/O。

select * 
from sys.dm_exec_query_plan 
(0x0600050099EC8520A8619803000000000000000000000000)

Tempdb

Tempdb用于全局存储内部或用户对象，临时表，对象和在SQL Server操作是创建的存储过程。每个SQL Server 实例只有1个单一的tempdb。它可能是一个性能和磁盘空间的瓶颈。有限可用空间和过多的DDL/DML会使Tempdb超过负载。这能导致运行在同一个服务器中的其他无关应用变得运行缓慢或失败。

下面列出一些tempdb的常规问题：

◆Tempdb磁盘空间不足
◆由于tempdb中的I/O瓶颈，导致查询运行缓慢。这在I/O瓶颈部分提到过。
◆过多DDL操作导致系统表的瓶颈
◆分配争夺

Before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. It can be grouped into four main categories.当开始调试tempdb问题前，让我们先看一下在tempdb中如何使用空间。它可以分为4个主要的类别。

tempdb使用的总空间等于用户对象加内部对象加版本存储+可用空间。

这个可用空间等于性能计数器中tempdb的可用空间。

监视tempdb空间

阻止问题更好的方法是随后马上解决它。你可以使用下列性能监视器来监视使用中的tempdb空间。

◆Free Space in tempdb (KB).这个计数器以KB为单位跟踪空闲空间的数量。管理员可以使用这个计数器确定是否tempdb运行在较低的磁盘空间上。

然而，识别前面所定义的不同类别如何使用在tempdb中使用的磁盘空间，是很有兴趣的并有效率的。

下列查询返回了tempdb用户使用的空间和内部组件对象。当前它提供了仅有tempdb中的信息。

Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2

这里有一些示例的输出（空间使用KB为单位）。

user_objets_kb   internal_objects_kb   version_store_kb   freespace_kb
---------------- -------------------- ------------------ ------------
8736               128                    64                    448

注意这些技术不包括其中的混合扩展。页面在混合扩展中可以被分配个别用户和内部对象。

磁盘空间问题排错

用户对象，内部对象和存储的版本可以都可以导致在tempdb中的空间问题。这节我们会考虑针对每个类别如何排错。

用户对象

因为用户对象不被特定的会话拥有，你需要理解创建该对象的应用程序的规范并调整需要的tempdb大小。你可以通过执行exec sp_spaceused @objname='<user-object>'来找到个别用户对象使用的。例如，你可以运行下列脚本枚举所有tempdb对象。

DECLARE userobj_cursor CURSOR FOR 
select 
sys.schemas.name + '.' + sys.objects.name 
from sys.objects, sys.schemas
where object_id > 100 and 
type_desc = 'USER_TABLE'and 
sys.objects.schema_id = sys.schemas.schema_id
go
open userobj_cursor
go
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0) 
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name 
end
close userobj_cursor