Wait Type trong SQL Server

Tìm top những wait type trong hệ thống
Clear Wait Stats (as needed)

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); 
-- Isolate top waits for server instance since last restart or statistics clear 
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('SLEEP_TASK', 'BROKER_TASK_STOP', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP')) 
-- filter out some 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 for waits

Tham khảo:

• http://sqlserverperformance.wordpress.com/2009/11/08/sql-server-wait-type-repository/ 
• http://www.brentozar.com/responder/triage-wait-stats-in-sql-server/ 
• http://www.brentozar.com/sql/wait-stats/