Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Declare @Waits Table (
- WaitID int identity(1, 1) not null primary key,
- wait_type nvarchar(60),
- wait_time_s decimal(12, 2));
- 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 additional irrelevant waits
- Insert Into @Waits (wait_type, wait_time_s)
- SELECT W1.wait_type,
- CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
- 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
- WaitFor Delay '0:01:00';
- 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 additional irrelevant waits
- Insert Into @Waits (wait_type, wait_time_s)
- SELECT W1.wait_type,
- CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
- 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
- Select wait_type, MAX(wait_time_s) - MIN(wait_time_s) WaitDelta
- From @Waits
- Group By wait_Type
- Order By WaitDelta Desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement