查看等待类型

来源：互联网发布：苹果电脑激活windows 编辑：程序博客网时间：2024/05/16 23:33

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

阅读全文

0 0