说明

用户的SQL提交给服务器后，以TASK的形式运行。Task运行过程中，可能在多个状态之间转换，可能会等待某些资源（如IO、网络请求、锁、内存分配），通过等待事件，可以分析服务器在不同资源上消耗的时间，有助于了解系统运行状况，进行针对性的优化。

本文将介绍：

• 如何查看系统中的当前top等待事件

• 常见等待事件的含义和分析

等待事件分析

SQL Server提供了DMV视图，可用于分析等待事件

• sys.dm_os_wait_stats：累计的各等待事件的等待时间。需要注意的是，这个DMV不包含当前处于等待中的事件。只有一个事件完成的时候，才会记录到该DMV。

• sys.dm_os_waiting_tasks：当前处于等待状态的事件。

获取累计的等待事件信息

可通过如下SQL获取数据库的等待事件。需要注意的是，这里查到的是实例启动以来的累计值。

SELECT 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (raw_wait_time_ms) AS raw_wait_time_ms
FROM 
(
    -- global server wait stats (completed waits only)
    SELECT 
        wait_type, 
        waiting_tasks_count, 
        (wait_time_ms - signal_wait_time_ms) AS wait_time_ms,  
        signal_wait_time_ms, 
        wait_time_ms AS raw_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
    UNION ALL 
    -- threads in an in-progress wait (not yet completed waits)
    SELECT 
        wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms, 
        wait_duration_ms AS raw_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    -- Very brief waits quickly will roll into dm_os_wait_stats; we only need to 
    -- query dm_os_waiting_tasks to handle longer-lived waits. 
    WHERE wait_duration_ms > 1000
) AS merged_wait_stats
GROUP BY merged_wait_stats.wait_type;

drop table #wait_stat_start;
drop table #wait_stat_end;

SELECT 
	wait_type,
	CURRENT_TIMESTAMP as check_time,
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (raw_wait_time_ms) AS raw_wait_time_ms
into #wait_stat_start
FROM 
(
    -- global server wait stats (completed waits only)
    SELECT 
        wait_type, 
        waiting_tasks_count, 
        (wait_time_ms - signal_wait_time_ms) AS wait_time_ms,  
        signal_wait_time_ms, 
        wait_time_ms AS raw_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
    UNION ALL 
    -- threads in an in-progress wait (not yet completed waits)
    SELECT 
        wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms, 
        wait_duration_ms AS raw_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    -- Very brief waits quickly will roll into dm_os_wait_stats; we only need to 
    -- query dm_os_waiting_tasks to handle longer-lived waits. 
    WHERE wait_duration_ms > 1000
) AS merged_wait_stats
GROUP BY merged_wait_stats.wait_type;

waitfor delay '00:00:10';

SELECT 
	wait_type,
	CURRENT_TIMESTAMP as check_time,
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (raw_wait_time_ms) AS raw_wait_time_ms
into #wait_stat_end
FROM 
(
    -- global server wait stats (completed waits only)
    SELECT 
        wait_type, 
        waiting_tasks_count, 
        (wait_time_ms - signal_wait_time_ms) AS wait_time_ms,  
        signal_wait_time_ms, 
        wait_time_ms AS raw_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
    UNION ALL 
    -- threads in an in-progress wait (not yet completed waits)
    SELECT 
        wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms, 
        wait_duration_ms AS raw_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    -- Very brief waits quickly will roll into dm_os_wait_stats; we only need to 
    -- query dm_os_waiting_tasks to handle longer-lived waits. 
    WHERE wait_duration_ms > 1000
) AS merged_wait_stats
GROUP BY merged_wait_stats.wait_type;

这里的等待事件中，有一部分是后台等待事件，一般不需要关注。需要关注的是前台等待事件。

常见等待事件

等待事件	描述	（可能的）优化方法
CXPACKET	出现CXPACKET说明有并行查询。需要同时查看其他top等待事件。	查看并行查询的SQL是否能优化。 调整参数max degree of parallelism，降低并行度或禁用并行查询。 检查cost threshold for parallelism是否设置太低
SOS_SCHEDULER_YIELD	可能存在CPU资源不足的情况	检查消耗大量CPU的SQL 查看服务器CPU资源是否充足
THREADPOOL	等待worker线程	检查是否worker线程不够 是否有大量长时间运行的任务占用了worker
LCK_*	事物锁引起	查看是否有事物未提交 优化事物 查看锁定的表是否缺少索引。
PAGEIOLATCH_*, IO_COMPLETION, WRITELOG	等待IO传输	查看系统IO是否存在瓶颈(sys.dm_io_virtual_ file_stats ) 查看是否因为内存不足引起的IO请求频繁 查看IO消耗高的SQL语句
PAGELATCH_*	Page争用	查看是否系统的并发 检查是否存在tempdb PFS, GAM, SGAM页面争用。添加多个tempdb文件。
LATCH_*
ASYNC_NETWORK_IO
OLEDB