SQL Technology Insider -13 separation of the SQL optimization methodology weight-level wait

Code

--Isolate top Waitswith Waits as (SELECT wait_type, Wait_time_ms/ +. As wait_time_s, -. * Wait_time_ms/SUM (Wait_time_ms) over () as the PCT, Row_number () over (ORDER by Wait_time_ms DESC) as RN, -. * Signal_wait_time_ms/wait_time_ms assignal_pct from sys.dm_os_wait_stats WHERE Wait_time_ms>0and wait_type not like N'%sleep%'and wait_type not like N'%idle%'and wait_type not like N'%queue%'and Wait_type not in (N'clr_auto_event', N'Request_for_deadlock_search', N'Sqltrace_buffer_flush'                         /*filter out additional irrelevant waits*/) ) SELECT W1.wait_type, CAST (w1.wait_time_s as NUMERIC ( A,2) as wait_time_s, CAST (w1.pct as NUMERIC (5,2) as PCT, CAST (SUM (w2.pct) as NUMERIC (5,2) as running_pct, CAST (w1.signal_pct as NUMERIC (5,2) as Signal_pctfrom Waits as W1 JOIN Waits as W2 on W2.rn<=W1.rngroup by W1.rn, W1.wait_type, w1.wait_time_s, w1.pct, w1.signal_pcthaving SUM (w2.pct)-w1.pct < the--percentage threshold OR w1.rn<=5ORDER by W1.rn; GO

View Code

1. Total wait time (in seconds) for the wait type from the last reboot of the system or counter emptied

2. Percentage of wait time for this type of wait time

3. The continuous percentage from the most heavyweight wait type to the current wait type.

4. Signal wait time as a percentage of waiting time, (remember, Wait_time_ms contains Signal_wait_time_ms)

SQL Technology Insider -13 separation of the SQL optimization methodology weight-level wait