[FWD] SQL Server: SQL Server Delays Demystified

Link: http://technet.microsoft.com/en-us/magazine/hh781189.aspx


Our second sample script using the sys.dm_os_wait_stats DMV will help determine the resources on which SQL Server is spending the most time waiting...

Inside Microsoft SQL Server 2008: T-SQL Querying P137 的 query 是

...

AND wait_type NOT LIKE N'%SLEEP%'

AND wait_type NOT LIKE N'%IDLE%'

AND wait_type NOT LIKE N'%QUEUE%'
...

整理一下：

WITH 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', 

'SQLTRACE_BUFFER_FLUSH', 

'REQUEST_FOR_DEADLOCK_SEARCH', 

'XE_TIMER_EVENT', 

'BROKER_TO_FLUSH', 

'BROKER_TASK_STOP', 

'CLR_MANUAL_EVENT', 

'CLR_AUTO_EVENT', 

'XE_DISPATCHER_WAIT', 

'XE_DISPATCHER_JOIN'

    ) 

    AND wait_type NOT LIKE N'%SLEEP%'

AND wait_type NOT LIKE N'%IDLE%'

AND wait_type NOT LIKE N'%QUEUE%'

) 

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

OR W1.rn <= 5

ORDER BY W1.rn;


這個 query 跟計算累積營收很像，必須重複參考同個 table，接著用 W2.rn <= W1.rn 類似技巧整理出最後要的東西。