SQL SERVER->> Wait Stats

The wait state is an important means of isolating SQL Server performance issues, starting at the instance level. You can probably see if SQL Server is in a tight state of resources for some time in the past. It depends on whether you have an effective performance data collection mechanism in place. Sys.dm_os_wait_stats This DMV data is collected from the last boot of the instance.

 withWaits as  (  SELECTWait_type, Wait_time_ms/  +. aswait_time_s, -.*Wait_time_ms/ SUM(Wait_time_ms) Over() aspct, row_number () Over(ORDER  byWait_time_msDESC) asRN fromsys.dm_os_wait_statsWHEREWait_type not inch      ('Clr_semaphore','Lazywriter_sleep','Resource_queue',    'Sleep_task','Sleep_systemtask','Sqltrace_buffer_flush','WAITFOR',    'clr_auto_event','clr_manual_event')    ) --filter out additional irrelevant waits    SELECTW1.wait_type,CAST(w1.wait_time_s as DECIMAL( A,2)) aswait_time_s,CAST(w1.pct as DECIMAL( A,2)) aspct,CAST(SUM(w2.pct) as DECIMAL( A,2)) asrunning_pct fromWaits asW1INNER JOINWaits asW2 onW2.rn<=W1.rnGROUP  byW1.rn, W1.wait_type, w1.wait_time_s, w1.pct having SUM(w2.pct)-w1.pct<  the;--percentage threshold;

You can use the following command to reset data collection

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); GO

Reference:

Https://msdn.microsoft.com/en-us/library/ms179984.aspx

SQL SERVER->> Wait Stats