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