Query Tuning -System Waits - SQL

This query, from http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139 obtains either the top 90% or at least 10 waits in the system:


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 LIKE '%SLEEP%')
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
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 < 90
OR W1.rn <= 10
ORDER BY W1.rn;GO


To reset the statistics prior to undertaking analysis:


DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);