maandag, oktober 09, 2006

Calculate waitstats - 2005

WITH TotalCTE (wait_type, wait_time_ms, waiting_tasks_count) AS
(
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP', 'RESOURCE_QUEUE', 'OLEDB','WAITFOR', 'SQLTRACE_BUFFER_FLUSH', 'SLEEP_TASK', 'LAZYWRITER_SLEEP', 'MISCELLANEOUS')
),
TotalWait (TotalWaitMs) AS
(
SELECT SUM(wait_time_ms) FROM TotalCTE
)
SELECT wait_type, (wait_time_ms * 1. / TotalWaitMs ) * 100 as 'Percentage', wait_time_ms / 1000 as WaitTimeInSecs, wait_time_ms / (waiting_tasks_count + .001) as AvgWaitTime, waiting_tasks_count
FROM TotalCTE, TotalWait
ORDER BY Percentage DESC