查看等待类型

WITH    [wait_time_ms]
          AS ( SELECT   [wait_type] ,
                        [wait_time_ms] AS [wait_time_ms] ,
                        ( [wait_time_ms] - [signal_wait_time_ms] ) AS [resource_wait_time_ms] ,
                        [signal_wait_time_ms] / 1000.0 AS [signal_wait_time_ms] ,
                        [waiting_tasks_count] AS [waiting_tasks_count] ,
                        100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER ( ) AS [percent_wait_time] ,
                                                              ROW_NUMBER() OVER ( ORDER BY [wait_time_ms] DESC ) AS [RowNum]
               FROM                                           sys.dm_os_wait_stats
               WHERE                                          [wait_type] NOT IN (
                                                              N'CLR_SEMAPHORE',
                                                              N'LAZYWRITER_SLEEP',
                                                              N'RESOURCE_QUEUE',
                                                              N'SQLTRACE_BUFFER_FLUSH',
                                                              N'SLEEP_TASK',
                                                              N'SLEEP_SYSTEMTASK',
                                                              N'WAITFOR',
                                                              N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                                                              N'CHECKPOINT_QUEUE',
                                                              N'REQUEST_FOR_DEADLOCK_SEARCH',
                                                              N'XE_TIMER_EVENT',
                                                              N'XE_DISPATCHER_JOIN',
                                                              N'LOGMGR_QUEUE',
                                                              N'FT_IFTS_SCHEDULER_IDLE_WAIT',
                                                              N'BROKER_TASK_STOP',
                                                              N'CLR_MANUAL_EVENT',
                                                              N'CLR_AUTO_EVENT',
                                                              N'DISPATCHER_QUEUE_SEMAPHORE',
                                                              N'TRACEWRITE',
                                                              N'XE_DISPATCHER_WAIT',
                                                              N'BROKER_TO_FLUSH',
                                                              N'BROKER_EVENTHANDLER',
                                                              N'FT_IFTSHC_MUTEX',
                                                              N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                                                              N'DIRTY_PAGE_POLL',
                                                              N'SP_SERVER_DIAGNOSTICS_SLEEP' )
             )
    SELECT  [W1].[wait_type] AS [WaitType] ,
            CAST ([W1].[wait_time_ms] AS DECIMAL(14, 2)) AS [wait_time_ms] ,
            CAST ([W1].[resource_wait_time_ms] AS DECIMAL(14, 2)) AS [resource_wait_time_ms] ,
            CAST ([W1].[signal_wait_time_ms] AS DECIMAL(14, 2)) AS [signal_wait_time_ms] ,
            [W1].[waiting_tasks_count] AS [waiting_tasks_count] ,
            CAST ([W1].[percent_wait_time] AS DECIMAL(4, 2)) AS [percent_wait_time] ,
            CAST (( [W1].[wait_time_ms] / [W1].[waiting_tasks_count] ) AS DECIMAL(14,
                                                              4)) AS [avg_wait_time_ms] ,
            CAST (( [W1].[resource_wait_time_ms] / [W1].[waiting_tasks_count] ) AS DECIMAL(14,
                                                              4)) AS [avg_resource_wait_time_ms] ,
            CAST (( [W1].[signal_wait_time_ms] / [W1].[waiting_tasks_count] ) AS DECIMAL(14,
                                                              4)) AS [avg_signal_wait_time_ms]
    FROM    [wait_time_ms] AS [W1]
            INNER JOIN [wait_time_ms] AS [W2]
            ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1].[RowNum] ,
            [W1].[wait_type] ,
            [W1].[wait_time_ms] ,
            [W1].[resource_wait_time_ms] ,
            [W1].[signal_wait_time_ms] ,
            [W1].[waiting_tasks_count] ,
            [W1].[Percent_wait_time]
    HAVING  SUM([W2].[percent_wait_time]) - [W1].[percent_wait_time] < 95; -- percentage threshold

/*
--CXPACKET
在执行并行查询计划时，由于各并行线程之间任务分配不均匀或某个线程被阻塞，导致CXPACKET 值增加

导致CXPACKET等待高的原因有很多，不能盲目地修改 MAXDOP的值或修改实例级别的最大并发度
可能原因有：
1>统计过期导致生成低效的执行计划
2>缺乏索引导致表扫描
3>中间结果集无法预估结果集行数，导致执行计划低效
4>某个线程因其他资源被阻塞

解决方案
1>检查执行计划是否高效
2>修改语句的并发度
3>修改实例级别的最大并发度

补充：
建议将MAXDOP的值设置为小于逻辑CPU的数，以避免单个查询阻塞所有请求
*/

--清除 sys.dm_os_wait_stats 视图中所有内容
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO