/***
Waitstats T-SQL script v1.0
Downloaded from:
http://dev.guzowski.info/files/waitstats.sql

(c) Marcin Guzowski
http://guzowski.info
***/

IF EXISTS (SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '##waitstats%')
DROP TABLE ##waitstats;

CREATE TABLE ##waitstats (
wait_type nvarchar(60),
waiting_tasks_count bigint,
wait_time_ms bigint,
max_wait_time_ms bigint,
signal_wait_time_ms bigint,
[time] datetime default getdate()
);

-- silent mode
SET NOCOUNT ON;

DECLARE @delay varchar(50);

/*** CONFIGURATION GOES HERE ***/
SET @delay = '00:00:20';
/*** CONFIGURATION GOES HERE ***/

-- collect initial waitstats values
INSERT INTO ##waitstats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('WAITFOR');

-- wait and let the workload do his job
WAITFOR DELAY @delay;

-- collect final waitstats values
INSERT INTO ##waitstats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('WAITFOR');

-- return StartTime and EndTime
SELECT (SELECT MIN([time]) FROM ##waitstats) as StartTime, (SELECT MAX([time]) FROM ##waitstats) as EndTime;

-- aggregate and return the final result
WITH cte_aggr ( WaitType, WaitTime, SignalWaitTime ) 
AS (
	SELECT wait_type as WaitType, 
	MAX(wait_time_ms)-MIN(wait_time_ms) as WaitTime, 
	MAX(signal_wait_time_ms)-MIN(signal_wait_time_ms) as SignalWaitTime
	FROM ##waitstats
	GROUP BY wait_type 
)
SELECT *, 
CAST(ROUND(CAST(WaitTime as numeric(18,5))/(SELECT SUM(WaitTime) FROM cte_aggr)*100,2) as numeric(5,2))
as [% of TotalWaitTime],
CASE WaitTime WHEN 0 THEN 0 ELSE
CAST(ROUND(CAST(SignalWaitTime as numeric(18,5))/WaitTime*100,2) as numeric(5,2))
END as [SignalTime to WaitTime]
FROM cte_aggr
UNION ALL 
SELECT '*** TOTAL ***', SUM(WaitTime), SUM(SignalWaitTime), 100,
CASE SUM(WaitTime) WHEN 0 THEN 0 ELSE
CAST(ROUND(CAST(SUM(SignalWaitTime) as numeric(18,5))/SUM(WaitTime)*100,2) as numeric(5,2)) END
FROM cte_aggr
ORDER BY WaitTime DESC;

-- End --