USE [Temporary];
GO
DECLARE @max_increment_id INT
------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------
SELECT @max_increment_id = MAX(increment_id)
FROM dbo.TEMP_dm_os_wait_stats;
------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------
WITH Waits AS
(
SELECT DOWS1.wait_type,
((DOWS1.wait_time_ms - DOWS2.wait_time_ms)/1000) AS [wait_time_s],
100. * (DOWS1.wait_time_ms - DOWS2.wait_time_ms) / SUM(DOWS1.wait_time_ms - DOWS2.wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC) AS rn
FROM
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM dbo.TEMP_dm_os_wait_stats
WHERE increment_id = @max_increment_id
)AS DOWS1
INNER JOIN
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM dbo.TEMP_dm_os_wait_stats
WHERE increment_id = (@max_increment_id - 1)
)AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0
)
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;