名次解释
DMVs：dynamic management views
三个点
· 资源瓶颈: CPU、内存、I/O（这里面不考虑网络问题）
· Tempdb瓶颈:
· User query瓶颈，可能是统计信息的变化、不恰当的索引、阻塞或者死锁等
上述三点，可能是相互影响的。
资源瓶颈
1. System Monitor (PerfMon)：windows自带
2. SQL Server Profiler: 2005继续有
3. DBCC commands: 参考联机文档
4. DMVs: 见上名次解释
CPU瓶颈
CPU瓶颈，是突然并且不可预料的。一般来讲，没有优化的查询计划、系统低配置、设计不合理等，很容易导致这些问题。
在perfmon中，我们一般需要监视Processor:% Processor Time，如果每个CPU持续高于80%，CPU就是瓶颈了。当然，在强大的2005下我们也可监视sys.dm_os_schedulers ，如果有内容，表明有任务等待CPU来分配给它。如下面这个DMVs的查询：
select scheduler_id,current_tasks_count,runnable_tasks_count from sys.dm_os_schedulers where scheduler_id < 255
下面的查询，更高级点。分析方法是，看结果的number_of_statements，如果该值大于1，说明可能有问题，要进一步分析。
select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count,count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc
在sql2005中的一个改进，就是对于某个sp，进行recompile的时候，只需要针对改变的部分进行编译，sql2000只能是全部都搞一遍。
Recompile的原因很多，如：
· Schema的变更 changed
· Statistics变更
· 延迟编译
· SET option的执行
· 临时表的变化
· Sp使用了RECOMPILE提示或者使用了OPTION (RECOMPILE)
诊断方法，老朋友了，继续使用perfmon或者sql profiler。
对于perfmon，监视下面的 计数器
· SQL Server: SQL Statistics: Batch Requests/sec
· SQL Server: SQL Statistics: SQL Compilations/sec
· SQL Server: SQL Statistics: SQL Recompilations/sec
对于profiler抓到的trace，分析这几个event：SP:Recompile / SQL:StmtRecompile / Showplan XML For Query Compile。如果我们抓到了trace，对于文件，可以这么做：
select spid,StartTime,Textdata,EventSubclass,ObjectID,SQLHandle from fn_trace_gettable ( 'e:\recompiletrace.trc' , 1) where EventClass in(37,75,166)
这里面，EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile
如果你事先保存到了 table jq中，那么把上面的from修改为from jq即可。
或者使用这个DMVs： sys.dm_exec_query_optimizer_info（注意一个技巧！多执行几次，看中间的差异）
select * from sys.dm_exec_query_optimizer_info
另外一个DMVs是：sys.dm_exec_query_stats，如执行这个sql：
select * from sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
另外， plan_generation_num标示出了被recompile的所有query。如下面这个
select top 25 sql_text.text,sql_handle,plan_generation_num, execution_count,dbid,objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc
解决办法
· 如果因为使用了Set option，那么通过profiler来观察是什么引起的变化。
· 如果因为临时表，那么尽量使用表变量，而不是临时表。（对于表变量的限制，请查看联机文档）另一个解决办法，使用KEEP PLAN查询提示，这会把临时表当作普通表一样对待，会进行statistics的跟踪
· 关闭对于索引或者索引视图上的状态自动更新（偶个人不建议）
· 对象名称写全了，如dbo.Table1
· 尽量少用延迟编译。如你的SP或者query里面，有N多的if/else之类的。
· 运行索引调优向导（sql2000里面就有）
· 看看sp是不是使用了WITH RECOMPILE来建立的，或者RECOMPILE查询提示。
每个查询执行之前，sqlserver都会“试图”优化一个最快的查询计划出来。注意的是，这里的最快的，不代表I/O最小，也不代表CPU占用最小。它是一个权衡后的值。
对于Hash join或者sort等，它们都是与CPU密切相关的。对于nested loop，很可能会因为大量的index lookups，导致I/O迅速上涨。如果search的数据散落在各个pages里面，很可能会导致缓冲命中率下降。
诊断方法
使用这个DMVs： sys.dm_exec_query_stats，它可以有效地监视CPU的使用情况。
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,q.dbid,q.objectid,q.number,q.encrypted,q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc
解决办法
· 使用优化向导调优
· 检查一下，WHERE条件是不是限制的足够好？
游标问题
强烈建议，尽量减少使用游标。可以使用perfmon监视SQL Server:Cursor Manager By Type – Cursor Requests/Sec。或者使用DMVs：
select cur.* from sys.dm_exec_connections con cross apply sys.dm_exec_cursors(con.session_id) as cur where cur.fetch_buffer_size = 1 and cur.properties LIKE 'API%'
如果使用profiler，可以监视sp_cursorfetch（前提是包含了RPC:Completed这个event class）
对于VAS和AWE概念，请自行查找MSDN。我印象中，M$的人强烈建议不要在32bit windows上面使用AWE或者3BG之类的东西。
检测内存问题
打开taskmgr看物理内存中的Avaiable，如果持续低于10M，恭喜你，系统内存压力太大！通过perfmon，监视Memory: Available Mbytes，一样的效果。
对于AWE使用，可以用这个DMVs来看：
select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks
对于虚拟内存等，可以观察Commit Charge中的Total，与limit的对比。如果两者很接近，虚拟内存可能不够了。如果你好奇，想看看sqlserver自己的内存分配，可以执行DBCC MEMORYSTATUS。具体内容，见联机文档。
除非操作系统能够，并且内存足够大，把你的db放到物理内存里，否则，I/O我们永远回避不过去。使用perfmon的话，可以监视
· PhysicalDisk Object: Avg. Disk Queue Length，如果经常性的大于2*磁盘个数，磁盘有性能问题。
· Avg. Disk Sec/Read，如果<10ms，很好。20以下，一般。50以下，密切观察。50以上，换硬盘吧！
· Avg. Disk Sec/Write，这个和上面的两个值，如果持续大于物理磁盘的指标的85%，说明磁盘已经到极限了。
· Physical Disk: %Disk Time，一般如果超过50%，I/O有瓶颈。
如果用了raid，采用下面这个公式来计算：
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
如下面这个例子，2个磁盘，raid1，监测到的结果：
Disk Reads/sec 80
Disk Writes/sec 70
Avg. Disk Queue Length 5
解决办法
· 检查sqlserver的内存配置
· 增加或者替换更快的硬盘，读写缓存越高越好
· 检查执行计划，找到I/O大的地方。如这个DMVs
select top 50 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, s2.text plan_handle from sys.dm_exec_query_stats cross APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by (total_logical_reads + total_logical_writes) Desc
小TIP，如果要清除缓存的作用，执行这个：
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
TempDB
每个实例只有一个tempdb，所以这里很可能成为性能或者磁盘空间的瓶颈。
常见的tempdb问题如下：
· 把磁盘空间用光了
· 因为tempdb的瓶颈，导致I/O很差。参见第一部分。
· DDL带来的对系统表的瓶颈
· 内容分配
诊断问题之前，先看看tempdb是如何利用空间的。
用户对象
· 表和索引
· 全局临时表 (##t1)和索引
· 局部临时表和索引(#t1) and index.
· 当前连接的
· 存储过程内的
· 表变量（同上）
内部对象
· Work file (hash join)
· Sort run
· Work table (cursor, spool和临时大对象)
版本存储
２００５新增的
空闲空间
tempdb暂时没有用到的磁盘剩余空间.
监视tempdb剩余空间很简单，监测这个指标即可。Free Space in tempdb (KB)。下面这个DMVs很强大的说，上面四个都能看到。
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
分析空间使用问题
用户对象
跑这个，能看出来到底谁干的。
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
版本存储
select top 2 transaction_id, transaction_sequence_num, elapsed_time_seconds from sys.dm_tran_active_snapshot_database_transactions order by elapsed_time_seconds DESC
这里显示一个带有XSN3的事务（ID是8609），已经激活了6523秒。
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783
Kill掉第一个trans，我们可以释放iding数量的version store。但是，没有办法能够估计出来，kill掉之后，究竟能释放多少。
内部对象
SQL Server 2005提供了两个DMVs: sys.dm_db_session_space_usage和 asys.dm_db_task_space_usage，用以跟踪sessions和tasks在tempdb中的空间变化。
select session_id, internal_objects_alloc_page_count, internal_objects_dealloc_page_count from sys.dm_db_session_space_usage order 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 t2 where t1.session_id = t2.session_id and t1.session_id >50 order by allocated DESC
这里一个示例输出
session_id allocated deallocated
---------- -------------------- --------------------
52 5120 5136
51 16 0
对于tasks，可以执行下面的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_handle from (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 t2 where 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以龟速执行的罪魁。
阻塞
阻塞主要等待逻辑锁，如请求一个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，很容易发懵。
（原文地址，点这里）