目录
wait event 的基本 troubleshooting
虚拟文件信息（ virtual file Statistics ）
性能调优很难有一个固定的理论。调优本来就是处理一些特殊的性能问题。
通常一旦拿到一个服务器那么就先做一下性能检查。查看整个数据库是运行在什么样的状况下的。
分析收集的数据想像这种情况是否合理。
确定思路
一个数据库操作的时间都是执行时间 + 等待时间，在无法估计执行时间的时候看要看看等待时间。
那么等待时间分为锁等待时间和资源等待时间。
那么就先用 sys.dm_os_wait_stats 动态性能视图，查看主要的状况。如果 pageiolatch_sh 等待很大，那么就说明， session 在等待 buffer pool 的页。当一个 session 要 select 一些数据，但是刚刚好，这些数据并没有在 buffer pool 中，那么 sql server 就会分配一些缓存这些缓存是属于 buffer pool 的，用来存放从磁盘读取出来的数据，在读取的时候都会给这些缓存上 latch （可以看成是锁）。当存在 io 瓶颈的时候，那么磁盘上的数据不能立即读到 buffer pool 中就会出现等待 latch 的情况。这个可能是 io 过慢，也有可能是在做一些多余的 io 造成的。
那么接下来查看 sys.dm_io_virtual_file_stats 性能视图来确定哪个数据库造成了怎么大的延迟。并且通过 physical disk \avg.disk reads/sec 和 physical disk\avg.disk writes/sec 来确定到底数据库有多少 io 负载。
接下来通过 sys.dm_exec_query_stats 查看执行计划，通过查看高物理读的 sql 和执行计划看看有没有优化的空间。如添加索引，修改 sql ，优化引擎访问数据的方法。
有可能， sql 语句已经不能再优化，但是性能还是不行，往往这种 sql 是报表查询类的 sql ，会从磁盘中读取大量数据，很多数据往往在 buffer pool 找不到那么就会发生大量的 pageiolatch_sh 等待。这时，我们就要看看是否是内存不足照成的，用 perfmon 查看 page life expectancy( 页寿命长度 ) ， free list stalls/sec( 等待空页的次数 ) 和 Lazy writes/sec 。 page life expectancy 波动很厉害， free list stalls/sec 一直大于 0 ， Lazy writes/sec 的量也很大，那么就说明 buffer pool 不够大。但是也有可能是 sql 写的不严谨， select 了很多没必要的数据。
在上面的 troubleshooting 过程中，很容易走入一个误区， sys.dm_io_virtual_file_stats 和 一些性能指标，就会很容易断定说 io 有问题，需要额外的预算来扩展 io 的性能，但是扩展 io 是比较贵的。 io 性能不理想很有可能 miss index 或者 buffer pool 的压力造成的。如果单纯的添加物理设备，但是没有找到根本原因，当数据量增长后，依然会出现相同的问题。
wait event 的基本 troubleshooting
wait statistics 是 SQLOS 跟踪得到的
SQLOS 是一个伪操作系统，是 SQL Server 的一部分，有调度线程，内存管理等其他操作。
SQLOS 比 windows 调度器更好的调度 sql server 线程。 SQLOS 的调度器间的交互，会比强占式的系统调度又更好的并发性
当 sql server 等待一个 sql 执行的时候，等待的时间会被 sqlos 捕获，这些时间都会存放在 sys.dm_os_wait_stats 性能视图中。各种等待时间的长度，并且和其他的性能视图，性能计数器结合，可以很明显的看出性能问题。
对于未知的性能问题 sys.dm_os_wait_stats 用来判断性能问题是很好用的，但是在服务器重启或者 dbcc 命令 清空 sys.dm_os_wait_stats 后 会很好分析，时间一长就很难分析，因为等待时间是累计的，搞不清楚哪个是你刚刚执行出来的时间。当然可以考虑 先捕获一份，当 sql 执行完后，再捕获一份，进行比较。
查看 wait event ，得到的信息 只是实际性能问题的其中一个症状，为了更利用 wait event 信息，你需要了解资源等待和非资源等待的区别，还有需要了解其他 troubleshooting 信息。
在 sql server 中有一部分的 sql 是没问题的，可以使用一下 sql 语句查看说有的 session 的 wait event
SELECT DISTINCT
wt . wait_type
FROM sys . dm_os_waiting_tasks AS wt
JOIN sys . dm_exec_sessions AS s ON wt . session_id = s . session_id
WHERE s . is_user_process = 0
因为很大一部分是正常的，所以提供了一个 sql 来过滤正常查询操作
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM ( wait_time_ms ) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM ( signal_wait_time_ms ) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM ( wait_time_ms ) OVER ( ) AS percent_total_resource_waits
FROM sys . dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK' , 'BROKER_TASK_STOP' , 'BROKER_TO_FLUSH' ,
'SQLTRACE_BUFFER_FLUSH' , 'CLR_AUTO_EVENT' , 'CLR_MANUAL_EVENT' ,
'LAZYWRITER_SLEEP' , 'SLEEP_SYSTEMTASK' , 'SLEEP_BPOOL_FLUSH' ,
'BROKER_EVENTHANDLER' , 'XE_DISPATCHER_WAIT' , 'FT_IFTSHC_MUTEX' ,
'CHECKPOINT_QUEUE' , 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,
'BROKER_TRANSMITTER' , 'FT_IFTSHC_MUTEX' , 'KSOURCE_WAKEUP' ,
'LAZYWRITER_SLEEP' , 'LOGMGR_QUEUE' , 'ONDEMAND_TASK_QUEUE' ,
'REQUEST_FOR_DEADLOCK_SEARCH' , 'XE_TIMER_EVENT' , 'BAD_PAGE_PROCESS' ,
'DBMIRROR_EVENTS_QUEUE' , 'BROKER_RECEIVE_WAITFOR' ,
'PREEMPTIVE_OS_GETPROCADDRESS' , 'PREEMPTIVE_OS_AUTHENTICATIONOPS' ,
'WAITFOR' , 'DISPATCHER_QUEUE_SEMAPHORE' , 'XE_DISPATCHER_JOIN' ,
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
检查 wait event 一般只关注前几个等待信息，查看高等待时间的等待类型。
CXPACKET ：
表明并发查询的等待时间，通常不会立刻产生问题，也可能是因为别的性能问题，导致 CXPACKET 等待过高。
SOS_SCHEDULER_YIELD
任务在执行的时候被调度器中断，被放入可执行队列等待被运行。这个时间过长可能是 cpu 压力造成的。
THREADPOOL
一个任务必须绑定到一个工作任务才能执行， threadpool 就是 task 等待被绑定的时间。出现 threadpool 过高可能是， cpu 不够用，也可能是大量的并发查询。
LCK_*
这中等待类型过高，说明可能 session 发生堵塞，可以看 sys.dm_db_index_operational_stats 获得更深入的内容
PAGEIOLATCH_*,IO_COMPLETION,WRITELOG
这些往往和磁盘的 io 瓶颈关联，根本原因往往都是效率极差的查询操作消费了过多的内存。 PAGEIOLATCH_* 和数据库文件的读写延迟相关。 writelog 和事务日 志文件的读写相关。这些等待最好和 sys.dm_io_virtual_file_stats 关联确定问题是发生在数据库，数据文件，磁盘还是整个实例。
PAGELATCH_*
在 buffer pool 中非 io 等待 latch 。 PAGELATCH_* 大量的等待通常是分配冲突。当 tempdb 中大量的对象要被删除或者创建，那么系统就会对 SGAM ， GAM 和 PFS 的分配发生冲突。
LATCH_*
LATCH_* 和内部 cache 的保护，这种等待过高会发生大量的问题。可以通过 sys.dm_os_latch_stats 查看详细内容。
ASYNC_NETWORK_IO
这个等待不完全表明网络的瓶颈。事实上多数情况下是客户端程序一行一行的处理 sql server 的结果集导致。发生这种问题那么就修改客户端代码。
简单的解释了主要的等待，减少在分析 wait event 的时候走的弯路。
为了确定是否已经排除问题可以用 DBCC SQLPERF ( 'sys.dm_os_wait_stats' , clear ) 清除 wait event 。也可以用 2 个 wait event 信息相减。
虚拟文件信息（ virtual file Statistics ）
通常，当使用 wait event 分析问题的时候，都为认为很想 io 的性能问题。但是 wait event 并不能说明 io 是怎么发生的，所以很有可能会误判
这就是为什么要使用 sys.dm_os_latch_stats 查看的原因，可以查看累计的 io 统计信息，每个文件的读写信息，日志文件的读写，可以计算读写的比例， io 等待的次数，等待的时间。
SELECT DB_NAME ( vfs . database_id ) AS database_name ,
vfs . database_id ,
vfs . FILE_ID ,
io_stall_read_ms / NULLIF ( num_of_reads , 0 ) AS avg_read_latency ,
io_stall_write_ms / NULLIF ( num_of_writes , 0 )
AS avg_write_latency ,
io_stall / NULLIF ( num_of_reads + num_of_writes , 0 )
AS avg_total_latency ,
num_of_bytes_read / NULLIF ( num_of_reads , 0 )
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF ( num_of_writes , 0 )
AS avg_bytes_per_write ,
vfs . io_stall ,
vfs . num_of_reads ,
vfs . num_of_bytes_read ,
vfs . io_stall_read_ms ,
vfs . num_of_writes ,
vfs . num_of_bytes_written ,
vfs . io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys . dm_io_virtual_file_stats (NULL, NULL) AS vfs
JOIN sys . master_files AS mf ON vfs . database_id = mf . database_id
AND vfs . FILE_ID = mf . FILE_ID
ORDER BY avg_total_latency DESC
查看是否读写过大，平均延时是否过高。通过这个可以知道是否是 io 的问题。
如果数据文件和日志文件是共享磁盘队列的， avg_total_latency 比预期的要高，那么就有可能是 io 的问题了
如果当前的数据库是用来归档数据到比较慢的存储中，可能会有很高的 PAGEIOLATCH_* 和 io_stall 那么我们就需要确定怎么高的等待是否属于归档的线程，因此在 troubleshooting 的时候要注意你的服务器的类型。
如果你的磁盘读写比例是 1:10 ，而且又很高的 avg_total_latency 那么就考虑把磁盘队列换成 raid5 ，为 io 读提供更多的主轴。
性能指标
在最开始的 troubleshooting ，性能指标是非常有用的。也可以用来验证自己的判断是否正确。
PLA 是一个很好的性能日志分析工具 http://pal.codeplex.com . 可惜没有中文版，当然可以去 codeplex 下载源代码自己修改。这个工具内嵌了性能收集集合，也就是通常要收集的一些性能指标。也内嵌了阀值模板，可以在性能指标收集完之后做分析。
sql server 对自己的性能指标有对应的性能视图 sys.dm_os_performance_counters 。对于性能指标有些是累计值，因此需要做 2 个快照，相减计算结果。
DECLARE @CounterPrefix NVARCHAR ( 30 )
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END ;
-- Capture the first counter set
SELECT CAST ( 1 AS INT ) AS collection_instance ,
[OBJECT_NAME] ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys . dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page life expectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time (ms)'
)OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Re-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Compilations/sec'
)
-- Wait on Second between data collection
WAITFOR DELAY '00:00:01'
-- Capture the second counter set
SELECT CAST ( 2 AS INT ) AS collection_instance ,
OBJECT_NAME ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_second
FROM sys . dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page life expectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time (ms)'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Re-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Compilations/sec'
)
-- Calculate the cumulative counter values
SELECT i . OBJECT_NAME ,
i . counter_name ,
i . instance_name ,
CASE WHEN i . cntr_type = 272696576
THEN s . cntr_value - i . cntr_value
WHEN i . cntr_type = 65792 THEN s . cntr_value
END AS cntr_value
FROM #perf_counters_init AS i
JOIN #perf_counters_second AS s
ON i . collection_instance + 1 = s . collection_instance
AND i . OBJECT_NAME = s . OBJECT_NAME
AND i . counter_name = s . counter_name
AND i . instance_name = s . instance_name
ORDER BY OBJECT_NAME
-- Cleanup tables
DROP TABLE #perf_counters_init
DROP TABLE #perf_counters_second
主要收集一下性能指标：
• SQLServer:Access Methods\Full Scans/sec
• SQLServer:Access Methods\Index Searches/sec
• SQLServer:Buffer Manager\Lazy Writes/sec
• SQLServer:Buffer Manager\Page life expectancy
• SQLServer:Buffer Manager\Free list stalls/sec
• SQLServer:General Statistics\Processes Blocked
• SQLServer:General Statistics\User Connections
• SQLServer:Locks\Lock Waits/sec
• SQLServer:Locks\Lock Wait Time (ms)
• SQLServer:Memory Manager\Memory Grants Pending
• SQLServer:SQL Statistics\Batch Requests/sec
• SQLServer:SQL Statistics\SQL Compilations/sec
• SQLServer:SQL Statistics\SQL Re-Compilations/sec
这里又 2 个 Access Methods 性能指标，说明了访问数据库不同的方式， full scans/sec 表示了发生在数据库中索引和表扫描的次数。
如果 io 出现瓶颈，并且伴随着大量的扫描出现，那么很有可能就是 miss index 或者 sql 代码不理想照成的。那么多少次数到多少时可以认为有问题呢？在通常状况下 index searches/sec 比 full scans/sec 高 800-1000 ，如果 full sacans/sec 过高，那么很有可能是 miss index 和多余的 io 操作引起的。
Buffer Manager 和 memory manager 通常用来检测是否存在内存压力， lazy writes/sec ， page life expectancy ,free list stalls/sec 用来佐证是否处于内存压力。
很多网上的文章和论坛都说，如果 Page Life expectancy 低于 300 秒的时候，存在内存压力。但是这只是对于以前只有 4g 内存的服务器的，现在的服务器一般都是 32g 以上内存 5 分钟的阀值已经不能在说明问题了。 300 秒虽然已经不再适用，但是我们可以用 300 来作为基值来计算当前的 PLE 的阀值 (32/4)*300 = 2400 那么如果是 32g 的服务器设置为 2400 可能会比较合适。
如果 PEL 一直低于阀值，并且 lazy writes/sec 一直很高，那么有可能是 buffer pool 压力造成的。如果这个时候 full scans/sec 值也很高，那么请先检查是不是 miss index 或者 读取了多余的数据。
general statistics\processes blocked,locks\lock waits/sec 和 locks\lock wait time(ms) 如果这 3 个值都是非 0 那么数据库会发生堵塞。
SQL Statistics 计数器说明了 sql 的编译或者重编译的速度， sql compilations/sec 和 batch requests/sec 成正比，那么很有可能大量 sql 访问都是 ad hoc 方式无法通过执行计划缓冲优化它们，如果 SQL Re-compilations/sec 和 batch requests/sec 成正比，那么应用程序中可能又强制重新编译的选项。
memory manager\momory grants pending 表示等待授权内存的等待，如果这个值很高那么增加内存可能会有效果。但是也有可能是大的排序， hash 操作也可能造成，可以使用调整索引或者查询来减小这种状况。
执行计划缓冲的使用
执行计划缓冲是 sql server 的内部组件，可以使用 sys.dm_exec_query_stats 查询，下面有个 sql 查询 物理读前十的计划
SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
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_physical_reads ,
t . text
FROM sys . dm_exec_query_stats AS s
CROSS APPLY sys . dm_exec_sql_text ( s . sql_handle ) AS t
ORDER BY avg_physical_reads DESC
在执行计划里面的这些值可以看出哪些查询物理 io 操作很频繁，也可以和 wait event 和 虚拟文件结合分析有问题的 io 操作。
我们也可以使用 sys.dm_exec_query_plan() 查看存在内存里面的执行计划。
这里又 2 本书深入的讲述了查询执行计划：《 SQL Server 2008 Query performance tuning distilled 》，《 Inside Microsoft SQL Server 2008:T-SQL Querying 》。
sys.dm_exec_query_stats 还用来查询 cpu 时间，最长执行时间，或者最频繁的 sql
在 sql server 2008 中加入了 2 个额外的列， query_hash,query_plan_hash 用来聚合相似的 sql 的。对于 ad hoc 过大的服务器可以用来分析 相似的 sql ，不同的编译的总数。
总结
上面各个部分都讲了一个思维，一个思路。要想性能调优调的好，那么就先系统体系结构，你要清楚 如前面说的 miss index 一旦发生，那么不知会影响 io ，还会影响内存和 cpu 。接下来要会分析，从一开始的简单的性能统计信息，往下分析，用其他统计信息排除问题，得到性能问题的真正原因。
文章来源于： Troubleshooting SQL Server: A Guide for the Accidental DBA 如果看不懂的或者想更深入了解的，可以看原文。
