SQL Server 2005性能排错白皮书(Part 4)---From MS Customer Support Service部门

版本存储

SQL Server 2005提供了行版本架构用于实现一些特性。如下列出了使用行版本架构的特性。更多关于下列特性的信息，请参考SQL Server 联机丛书。

◆触发器
◆MARS
◆联机索引
◆基于行版本隔离级别：需要在数据库级设置选项

行版本需要跨会话共享。当行版本被回收时，行版本的创建者没有控制权。你需要找到并杀掉阻止行版本清理的运行最长的事务。下列查询返回依赖于版本存储运行最长的2个事务。

select top 2 transaction_id, transaction_sequence_num, elapsed_time_seconds from sys.dm_tran_active_snapshot_database_transactionsorder by elapsed_time_seconds DESC

这是示例的输入，显示了序列号为3，事务ID为8609的事务已经运行了6523秒。

transaction_id       transaction_sequence_num elapsed_time_seconds-------------------- ------------------------ --------------------8609                 3                        652320156                25                       783

因为第2个事务运行了相对短的时间，你可以通过杀掉第1个事务来释放大量的版本存储。可是，没有方法能评估通过杀掉进能释放的版本空间。你也许需要杀掉一些事务来释放更多的空间。

你可以通过改变用于版本存储的tempdb属性或通过尽可能的消除在快照隔离级别的长事务，或在read-committed-snapshot下运行的长查询来减轻这个问题。你能使用下列公式粗略的评估行版本存储的大小。

[Size of version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

在所有使用了基于孤立级别行版本，为一个事物每分钟生成版本存储的数据和每分钟生成的日志一样。然而这也有一些异常：只有更新的差异部分生成日志；如果使用了批量导入操作并且恢复模式不是完全恢复时，新插入的数据行不依赖日志，则不被记录版本。

你也可以使用Version Generation Rate 和Version Cleanup Rate性能计数器来调整你的计算。如果Version Cleanup Rate为0，这暗示着有长时间运行的事务阻止版本存储的清理。

附带地，在产生tempdb空间不足错误前，SQL Server 2005会做最后一次尝试强制版本存储收缩。在这个收缩过程中，没有生成行版本运行最长的事务会被标识为牺牲者。这可以释放他们使用的版本存储。在错误日志中为牺牲的事务生成一个消息3967，它能不再从版本存储中读取行版本或创建新的版本存储。如果收缩版本存储成功，这样在tempdb中会有更多的可用空间。否则tempdb将耗尽内存。

内部对象

内部对象在每条语句中被创建和销毁，除非想在前面所描述的。如果你注意到有大量的tempdb空间分配，你将需要了解那个会话或任务占用了空间，然后进肯能做一些矫正的操作。

SQL Server 2005提供了2个额外的DMV：:  sys.dm_db_session_space_usage 和sys.dm_db_task_space_usage 来跟踪分配给个别会话和任务所用的tempdb空间。尽管任务运行在会话的上下文，当任务完成后，任务使用的空间还会被占用。你可以使用下列查询来找到为内部对象分配最多的会话。注意这个查询只包括在会话中已完成的任务。

select session_id, internal_objects_alloc_page_count, internal_objects_dealloc_page_countfrom sys.dm_db_session_space_usageorder by internal_objects_alloc_page_count DESC

你可以使用下列查询找到分配对象最多的会话，包括正在运行的任务。

SELECT t1.session_id,(t1.internal_objects_alloc_page_count + task_alloc) as allocated,(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated from sys.dm_db_session_space_usage as t1, (select session_id, sum(internal_objects_alloc_page_count)as task_alloc,sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2where t1.session_id = t2.session_id and t1.session_id >50order by allocated DESC

这是示例的输出。

session_id allocated            deallocated---------- -------------------- --------------------52         5120                 513651         16                   0

一旦你隔离出生成大量对象分配的任务或会话，你能找到任务的那条Transact-SQL语句和它的查询计划来做更详细地分析。

select t1.session_id, t1.request_id, t1.task_alloc,t1.task_dealloc,t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handlefrom (Select session_id, request_id,sum(internal_objects_alloc_page_count) as task_alloc,sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id, request_id) as t1, sys.dm_exec_requests as t2where t1.session_id = t2.session_id and (t1.request_id = t2.request_id)order by t1.task_alloc DESC

这是示例的输出。

session_id request_id  task_alloc           task_dealloc  ---------------------------------------------------------       52         0           1024                 1024             sql_handle        statement_start_offset ----------------------------------------------------------0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172   356  statement_end_offset  plan_handle      ---------------------------------                   -1                0x06000500D490961BA8C19503000000000000000000000000

你可以利用如下语句通过sql_handle和plan_handle来得到SQL语句和查询计划：

select text from sys.dm_exec_sql_text(@sql_handle)select * from sys.dm_exec_query_plan(@plan_handle)

注意当你访问查询计划时，查询计划可能已经不在缓存中了。为保证查询计划可以使用，你应该需要经常为查询计划缓存保存否则该计划会被清除，同时应该将结果尽可能保存在表中，这样用于以后的查询。

当SQL Server 重新启动，tempdb大小将初始化到配置的大小，并基于需求增长。这可以导致tempdb的分裂，这样会招致过多的开销，包括在数据库自动增长时分配新的扩展的阻塞。这能影响你的工作负载性能。建议你重新分配tempdb到一个适当的大小。

过多的DLL和分配操作

在tempdb中2个原因可以导致这个结果。

◆创建和删除大量的临时表和标变量导致在元数据上的争夺。在SQL Server 2005中，本地的临时表和标变量被缓存来最小化元数据的争用。然而只有下列条件满足时，表才会被缓存。
◆在表中没有命名的约束。
◆在Create语句后在表中没有DDL(例如，Create Index和Create Statistics)。
◆典型情况下，大部分临时/工作表是在堆上；因此插入，删除或删除操作能引起在Page Free Space (PFS) 页面上的严重争用。如果大部分的表小于64KB并且使用了混合扩展来分配或处理位置，这能带来在Share Global Allocation Map(SGAM)页面上的争用。SQL Server 2005为本地的临时表缓存一个数据页和一个IAM页来最小化分配争用。这种缓存在SQL Server 2000中是使用在工作表上的。

因为SGAM和PFS在数据文件中分页出现在固定的间隔，这样很容易找到他们所用资源的描述。例如，2:1:1表示了在tempdb第1个PFS页面(database-id为2，file-id为1，page-id为1)，2:1:3表示了第1个SGAM页面。SGAM页面每511232个页面出现1次，PFS页面每8088个页面出现1次。你能使用这个规则在tempdb的所有文件中找到所有其他的PFS和SGAM页面。无论什么时候，当任务等待占有所有页面时，它将在sys.dm_os_waiting_tasks中显示。因为这种等待是短暂的，你需要频繁的查询这个表（大约每10秒一次）并收集这些数据以后分析。例如，你可以使用下列查询将在tempdb中所有等待页面的任务加载到在分析数据库中的waiting_task表。

-- get the current timestampdeclare @now datetime select @now = getdate()-- insert data into a table for later analysisinsert into analysis..waiting_tasksselect session_id, wait_duration_ms, resource_description, @nowfrom sys.dm_os_waiting_taskswhere wait_type like ‘PAGE%LATCH_%’ andresource_description like ‘2:%’

当需要时你可以看到在tempdb页面中等待锁的任务，这样你可以分析是否归咎于PFS或SGAM分页。如果是，这意味着在tempdb上有分配争用。如果你在tempdb上的其他页面争用，如果你确定这个页面属于系统表，这意味着由于过度的DDL导致争用。

你也可以使用下列的性能计数器监视临时对象分配/定位操作得不正常的增加，

◆SQL Server:Access Methods\Workfiles Created /Sec
◆SQL Server:Access Methods\Worktables Created /Sec
◆SQL Server:Access Methods\Mixed Page Allocations /Sec
◆SQL Server:General Statistics\Temp Tables Created /Sec
◆SQL Server:General Statistics\Temp Tables for destruction

解决

如果是由于过多的DDL操作导致在tempdb争用，你需要考虑你的应用程序，并查看是否你能减少DDL操作。你可以尝试下列建议。

◆如果你使用存储过程范围内的临时表，考虑是否这些表可以移动到存储过程外。否则每次执行存储过程将会导致创建/删除临时表。
◆查看查询计划，是否一些计划创建大量的临时对象，池，排序或工作表。你需要评估一些临时对象。例如，在一个列上创建一个用于ORDER BY操作的索引可以除去查询时的排序

如果争用是由于在SGAM 和PFS页面上的争用，你可以通过尝试下列操作减轻争用：

◆通过增加tempdb数据文件将等量负载分布在所有磁盘和文件上。理论上，你应该将文件数量设置为CPU数量等同（主要考虑亲和性）。
◆使用TF-1118消除混合扩展分配。

运行缓慢的查询

缓慢或长时间运行的查询可以占用过多资源并能导致阻塞查询。

过多的资源占用是没有限制CPU资源的使用，但是也能包括I/O存储带宽和内存带宽。即使SQL Server查询被设计为可以通过合理WHERE子句限制结果集的方法避免整表扫描 ，如果没有合适的索引支持这个特殊的查询，他们可能不会按照我们期望的方式执行。同样，WHERE子句能依赖于用户输入被动态的通过应用程序构建。假设存在的索引不能覆盖所有可能的约束。通过Transact-SQL语句占用过度的CPU，I/O和内存的情况在本白皮书前面已经描述了。

除了缺失索引外，也可能有索引没有被使用。当所有的索引不得不维护时，这不影响查询的性能，但是影响DML查询。

因为等待逻辑锁和系统资源的状态会阻塞查询，查询也会运行的比较缓慢。阻塞的原因可能是较差的应用程序设计，坏的查询计划，缺乏有用的索引和不正确的SQL Server实例配置。

这节主要集中在缓慢查询的2个原因-阻塞和索引问题。

阻塞

阻塞主要是等待逻辑锁，例如等待在资源上获取排他锁或等待从更低级别的同步结果，例如闩。

当做出一个在已经锁定的资源上获得一个不兼容的锁的请求产生时，逻辑锁等待发生。在特殊的Transact-SQL语句运行时，通过使用锁可以基于事务隔离级别提供数据一致性的功能，这样给最终用户的感觉是SQL Server运行缓慢。当查询被阻塞时，它不占用任何系统资源，所以你将发现它运行很长时间但是资源占用却很少。更多关于并发控制和阻塞的信息请查看SQL Server联机丛书。

如果你的系统没有被配置为处理这种负载就会导致等待底层的原始同步。

一般阻塞和等待的场景是：

◆识别阻塞者
◆识别长的阻塞
◆阻塞每个对象
◆页面闭锁问题
◆阻塞影响整体性能

如果系统资源（或锁）当前不能服务于请求，这个SQL Server会话将被置于等待状态。换句话说，资源有一个等待请求的队列。DMV能提供任何等待资源的会话的信息。

SQL Server 2005提供了更详细和一致的等待信息，有大约125种等待类型而SQL Server 2000只有76种可用的等待类型。DMV提供的信息范围从sys.dm_os_wait_statistics中表现SQL Server全面和积累的等待信息，到sys.dm_os_waiting_tasks中与会话相关分解的等待信息。下列DMV提供了详细的等待某些资源的任务等待队列。它同样表现了在系统中所有的等待队列。例如你可以运行下列查询找到关于阻塞会话56的详细信息。

select * from sys.dm_os_waiting_tasks where session_id=56waiting_task_address session_id exec_context_id wait_duration_ms     wait_typeresource_address   blocking_task_address blocking_session_id blocking_exec_context_id resource_description--------------------------------------------------------------------------0x022A8898           56         0               1103500              LCK_M_S  0x03696820         0x022A8D48            53                  NULL            ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X associatedObjectId=72057594038321152

这个结果显示了会话56被会话54阻塞了，会话56已经为一个锁等待了1103500毫秒。

为了找到准许的锁或等待锁的会话，你可以使用sys.dm_tran_locks DMV。每行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁，准许请求指出了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。例如下列查询显示会话56被阻塞在资源1:143:3，该资源被会话53的X模式锁占有。

            
select 

            request_session_id as spid, 

            resource_type as rt,  

            resource_database_id as rdb, 

            (case resource_type

            WHEN 'OBJECT' then object_name(resource_associated_entity_id)

            WHEN 'DATABASE' then ' '

            ELSE (select object_name(object_id) 

            from sys.partitions 

            where hobt_id=resource_associated_entity_id)

            END) as objname, 

            resource_description as rd,  

            request_mode as rm, 

            request_status as rs

            from sys.dm_tran_locks
            
Here is the sample output

            spid     rt           rdb         objname       rd            rm           rs

            ----------------------------------------------------------------------------- 
            
56    DATABASE    9                               S          GRANT

            53    DATABASE    9                             S          GRANT

            56    PAGE        9       t_lock      1:143       IS         GRANT

            53    PAGE        9       t_lock      1:143       IX         GRANT

            53    PAGE        9       t_lock      1:153       IX         GRANT

            56   OBJECT       9       t_lock                  IS         GRANT

            53   OBJECT       9        t_lock                 IX         GRANT

            53    KEY         9        t_lock      (a400c34cb X          GRANT

            53    RID         9        t_lock      1:143:3    X          GRANT

            56    RID         9        t_lock      1:143:3    S        WAIT
            

 
实际上，你能连接上面的2个DMV，就像使用存储过程sp_block锁展示的。在图1种阻塞报告列出了被阻塞的会话和阻塞它的会话。你可以在附录B中找到sp_block的源代码。如果你需要添加/删除在可选择列表中的列时，你可以根据需求修改存储过程。可选的@spid参数提供了在锁请求和阻塞这个spid的会话信息。

 

图1：sp_block 报表

在SQL Server 2000中，你能通过下列语句查看被阻塞的spid信息。

            
select * from master..sysprocesses where blocked <> 0. 
            

联合锁可以通过存储过程sp_lock存储过程。

识别长时间的阻塞

之前我们提到，在SQL Server中阻塞是很正常的，使用逻辑锁来维护事务一致性的。然而当等待的锁超过了阀值，它会影响响应时间。为了识别长时间运行的阻塞，你能使用BlockedProcessThreshold配置参数来建立一个用户配置的服务端阻塞阀值。阀值定义一个秒级的间隔。任何超过阀值的阻塞将出发事件并被SQL Trace捕获。

例如，1个200秒的阻塞进程阀值可以在SQL Management Studio中配置。例如：

            
Execute Sp_configure ‘blocked process threshold’, 200  

            Reconfigure with override
            

一旦阻塞处理阀值被建立，下一步是捕获跟踪的事件。跟踪阻塞超过用户配置的阀值事件可以通过SQL Trace 或Profiler捕获。

1.如果使用SQL Trace，使用sp_trace_setevent过程，event_id参数为137
2.如果使用SQL Server Profiler，选择Blocked Process Report 事件类（在Error和Warnings对象下），如图2。

 

图2：跟踪长时间的阻塞和死锁

注意这是一个轻量级的跟踪，事件仅在当锁超过阀值，或发生死锁时被捕获。每有200秒的间隔一个锁被阻塞，1个跟踪事件被触发。图3意味着1个锁阻塞了600秒，发生了3次跟踪事件。

 

图3：Reporting Blocking > block threshold

跟踪事件包括阻塞者和被阻塞者整个SQL语句。图中所示”Update Customer”阻塞了”Select from Customer”语句。

通过与SQL Server 2000比较，检查长期阻塞场景代码在Sysprocesses并在后续处理结果。知识库文章271509包含了一段可以用于监视阻塞的示例代码。

通过sys.dm_db_index_operational_stats查看阻塞的每个对象

新的SQL Server 2005 DMV Sys.dm_db_index_operational_stats提供了全面的索引使用统计，包括阻塞。根据阻塞，它提供了每个表，索引，分区的锁统计的详细信息。例如，在给定索引和表上的访问历史，锁数量（row_lock_count），阻塞数量（row_lock_wait_count）和等待时间(row_lock_wait_in_ms)等信息。

这个DMV包括的类型信息有：

◆占有的锁的数量，例如行或页。
◆阻塞或等待的数量，例如，行或页。
◆阻塞或等待持续的时间，例如行或页。
◆页面上闩的等待。
◆page_latch_wait持续时间：这包括特殊页上的争用，升序键的插入。在这种情况，热点是最后的页面，所以多个写入这到最后的页面每次尝试获取高级的页面闩在同样的时间。这将作为Pagelatch waits暴露。
◆page_io_latch_wait持续的时间：当用户请求一个不在缓存池的页面时发生的I/O闩。一个慢速的I/O子系统或过多工作的I/O子系统将遇到很高的PageIOlatch等待，这实际上是I/O问题。这个问题被混在于缓存清除和缺失索引中。
◆页面闩等待持续的时间。

除了阻塞相关信息外，这还有额外的信息。

◆访问类型, 包括range, singleton lookups.
◆在页级的插入，更新，删除。
◆在页级之上插入，更新或删除。在叶上的活动是索引维护。在每个叶级页面中的第一行有这个级别之上的条目。如果新的页面被分配到叶级别上，这级别之上将为新的叶级页面的第一行创建新的项。
◆在叶级别的页面合并将表现为重新分配的空页，因为行已经删除了。
◆索引维护。在叶级上页面合并就是将空白页重分配，这导致在叶上行被删除，因此留下的中间级别页面是空白的。在叶级页面的第一行有一个条目在该层上。如果在叶级别删除了足够的行，原来包含第一行叶级页面条目的中间层索引页面也会是空白的。这导致在叶结点上的合并发生。

这些信息积累了从实例启动以来的信息。信息不会一直保留直到实例被重新启动，并且没有其他的方法可以重置它。这个DMV返回的数据仅在元数据缓存对象表现的堆或索引可用的情况下存在。只要堆和索引的元数据被加载到了元数据缓存，每个列的这个值将被设置为0。统计是被累加的直到缓存对象被从元数据缓存中删除。然而，你可以周期性的查询这个表来收集在表中的信息，用于更进一步的分析。

附录B定义一套存储过程可以用于收集索引操作的数据。你能分析你感兴趣的时间短的数据。这里是如何使用定义的存储过程的步骤。

1.使用init_index_operational_stats初始化indexstats 表
2.使用insert_indexstats. 捕获基线数据
3.运行负载
4.通过使用insert_indexstats. 捕获最后索引统计
5.为了分析收集的索引统计，运行存储过程get_indexstats来生成每个索引上锁的平均数(索引和分区的row_lock_count)，阻塞和等待。高的blocking %和/或高的平均等待指出设计不好的索引或查询公式。

这有一些示例展现了你能使用这些存储过程的类型

◆获取所有数据库上使用率最高的5个索引

            
exec get_indexstats 

            @dbid=-1,

            @top='top 5', 

            @columns='index, usage', 

            @order='index usage'
            

◆获取前5个索引锁提示（所有列）

            
exec get_indexstats 

            @dbid=-1,

            @top='top 5',

            @order='index lock promotions',

            @threshold='[index lock promotion attempts] > 0'
            

◆获取5个平均行锁等待大于2ms的独立查询，返回行包括wait,scan,singleton信息。以'singleton lookups'排序

            
exec get_indexstats 

            @dbid=5,

            @top='top 5',

            @columns='wait,scan,singleton',

            @order='singleton lookups',

            @threshold='[avg row lock wait ms] > 2'
            

◆获取所有数据库行锁等待大于1，列包含‘avg,wait’，以'row lock wait ms'排序

            
exec get_indexstats 

            @dbid=-1,

            @top='top 10 ',

            @columns='wait,row',

            @order='row lock wait ms', 

             @threshold='[row lock waits] > 1'
            

◆获取前5个索引状态，通过'avg row lock wait ms'排序

            
exec get_indexstats 

            @dbid=-1,

            @top='top 5',

            @order='avg row lock wait ms'
            

◆获取前5个索引状态，通过'avg page latch wait ms'排序

            
exec get_indexstats 

            @dbid=-1,

            @top='top 5',

            @order='avg page latch wait ms'
            

◆获取前 5%索引状态, 通过 avg pageio latch waits.

            
exec get_indexstats 

            @dbid=-1,

            @top='top 3 percent',

            @order='avg pageio latch wait ms',

            @threshold='[pageio latch waits] > 0'
            

◆获取所有在数据库5中的索引状态并且block% > 0.1,以block%排序。

            
exec get_indexstats 

            @dbid=-1,

            @top='top 10',

            @order='block %',

            @threshold='[block %] > 0.1'
            

如图4示例，阻塞分析报告

 

图4：阻塞分析报告

SQL Server 2000部提供任何对象或索引的统计利用信息。