SQL Server2005性能调优（简化翻译版） - 3
龟速查询
阻塞和索引问题，是常见的导致sql以龟速执行的罪魁。
阻塞
阻塞主要等待逻辑锁，如请求一个X锁。关于锁的信息，遍地都是，msdn或者google都可以。SQL Server 2005提供了125中等待类型（2000是76种）。
假设我们sp_who看到了一个block在56号上，那么通过这个可以看到详细信息
select * from sys.dm_os_waiting_tasks where session_id=56
（在2000下，你可以通过dbcc inputbuffer(56)来看当前执行的文本）
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被53阻塞，并且等待了1103500毫秒了。
通过使用sys.dm_tran_locks，我们可以看到56被53以X模式锁住了，53持有1:143:3这个资源。
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 obt_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
输出如下
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
而在SQL Server 2000里面，可以从sysprocesses看到。
select * from master..sysprocesses where blocked <> 0.
更详细的阻塞信息
2005中提供了一个新的DMV：Sys.dm_db_index_operational_stats，它提供了针对每个表、索引、分区的详细阻塞情况，如：访问历史、锁、阻塞、waits等。详细信息如下：
· 页/行等持有锁的个数
· 页/行等锁或waits的个数
· 页/行等锁或waits的时间
· 页闩的waits个数（闩与hotspot，就是所谓的热点有关，下同）
· 页闩的waits时间
· 页I/O闩的waits时间
除了阻塞的信息，还有一些索引的信息。
· 访问方式，如某个range，或者lookup
· 在叶子层的插入/更新/修改
· 叶子层之上的插入/更新/修改，就是索引维护。每个叶子页面的第一行，指向了该层的上一层。假如说在叶子上分配了一个新页面，那么上面那层的页面就包含一个指向该层第一行的指针信息。
在原文的附录B中，包含了一系列的索引信息相关的存储过程。下面是使用该sp的步骤
1. 使用init_index_operational_stats来初始化indexstats表
2. 使用insert_indexstats建立一个基线
3. 运行你的负载
4. 到合时的实际，使用insert_indexstats捕获索引状态的快照
5. 跑get_indexstats来分析索引状态。诸如很高的阻塞或者很高的waits，基本可以表明索引有问题。
这里列出了一些使用上述sp的例子。
· 所有数据库中，使用最多的前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个最大的行锁waits时间的索引状态
exec get_indexstats @dbid=-1, @top='top 5', @order='avg row lock wait ms'
· 前10个，在dbid=5的数据库中，所有阻塞率大于10%的索引状态
exec get_indexstats @dbid=-1, @top='top 10', @order='block %', @threshold='[block %] > 0.1'
注：sql2000没有提供任何关于对象或者索引使用状态
监视索引使用情况
2005中提供了一个非常有用的DMV：sys.dm_db_index_usage_stats，通过它我们可以找到哪些索引正在被当前query使用，或者没被使用。注意的是，这些数据仅保留在内存中，并没有被持久化存储。所以，如果sql发生down机了，这些数据就都丢掉了。当然，我们可以把这些save到表中，供日后分析。
索引上的操作分为两种：用户方式和系统方式。一个索引，通过dbid、对象id和索引id三列信息唯一标示。索引id为0的时候，代表一个heap table；1的时候，聚集索引；大于1的时候，非聚集索引。
2005下，seek/scan/lookup的规则与定义如下：
· SEEK: 使用B-tree结构访问数据的次数。
· SCAN: 不使用B-tree结构访问数据的次数。
· LOOKUP: 使用不合适的非聚集索引配合聚集索引来寻找数据，如2000中的书签查找。
下面这个DMV可以得到“当前”所有数据库所有对象的索引状态。
select object_id, index_id, user_seeks, user_scans, user_lookups from sys.dm_db_index_usage_stats order by object_id, index_id
假设是下面的结果
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- ----------- ------
521690298 1 0 251 123
521690298 2 123 0 0
该结果表明，有251次的聚集索引scan，123次的书签查找，123次的非聚集索引seek。
如果想知道从上次sql启动之后，到现在为止，某个表中没有被使用过的索引状况，执行下面的sql。
select i.name from sys.indexes i where i.object_id=object_id('<table_name>') and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id and database_id = <dbid> )
所有未被使用的索引：
select object_name(object_id), i.name from sys.indexes i where i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id and database_id = <dbid> ) order by object_name(object_id) asc
结束了
原文后面有N多的sp，大家可以参考原文，在你的sql2005上跑一次。当然，能够在客户的生产环境中，用这些sp进行实际测试，会得到更好的体会。
我个人认为，更好的利用这些DMVs和sp的前提是，对于sql的基本概念要有所了解。索引、锁、阻塞、死锁等，为什么会产生，他们在SqlServer这种数据库下面是如何处理的，等等。否则，看着那些DMVs，很容易发懵。
