SQL SERVER ->> Wait Stats
时间：2021-07-01 10:21:17
帮助过：3人阅读
Waits
AS
(
SELECT
wait_type,
wait_time_ms / 1000.
AS wait_time_s,
100.
* wait_time_ms
/ SUM(wait_time_ms)
OVER()
AS pct,
ROW_NUMBER() OVER(
ORDER BY wait_time_ms
DESC)
AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
(‘CLR_SEMAPHORE‘,
‘LAZYWRITER_SLEEP‘,
‘RESOURCE_QUEUE‘,
‘SLEEP_TASK‘,
‘SLEEP_SYSTEMTASK‘,
‘SQLTRACE_BUFFER_FLUSH‘,
‘WAITFOR‘,
‘CLR_AUTO_EVENT‘,
‘CLR_MANUAL_EVENT‘)
) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s
AS DECIMAL(
12,
2))
AS wait_time_s,
CAST(W1.pct
AS DECIMAL(
12,
2))
AS pct,
CAST(
SUM(W2.pct)
AS DECIMAL(
12,
2))
AS running_pct
FROM Waits
AS W1
INNER JOIN Waits
AS W2
ON W2.rn
<= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct)
- W1.pct
< 95;
-- percentage threshold;
可以通过下面命令reset数据收集
DBCC SQLPERF (‘sys.dm_os_wait_stats‘, CLEAR);
GO
参考：
https://msdn.microsoft.com/en-us/library/ms179984.aspx
SQL SERVER ->> Wait Stats
标签：
