Wait statistics that occur with SQL Server

SELECT *  from sys.dm_os_wait_stats WHERE Wait_time_ms>0ORDERbyDESC

The wait statistics are cleared automatically only when SQL Server restarts, and the statistics can be cleared through the DBCC SQLPERF ("Sys.dm_os_wait_stats", clear) command .

A temporary table and a WAITFOR DELAY statement track changes that have occurred over time to determine what is currently happening.

IF object_id('tempdb: #wait_stats') is  not NULLDROP TABLE#wait_statsSELECT *  into#wait_stats fromsys.dm_os_wait_statsWAITFORDELAY'00:00:05'SELECTWs1.wait_type, Ws2.waiting_tasks_count-Ws1.waiting_tasks_count asWaiting_tasks_count, Ws2.wait_time_ms-Ws1.wait_time_ms asWait_time_ms, Case  whenWs2.max_wait_time_ms>Ws1.max_wait_time_ms ThenWs2.max_wait_time_msELSEWs1.max_wait_time_msEND  asMax_wait_time_ms, Ws2.signal_wait_time_ms-Ws1.signal_wait_time_ms asSignal_wait_time_ms, (Ws2.wait_time_ms-Ws1.wait_time_ms)-(Ws2.signal_wait_time_ms-Ws1.signal_wait_time_ms) asResource_wait_time_ms fromSys.dm_os_wait_stats asws2JOIN#wait_stats asWs1 onWs1.wait_type=Ws2.wait_typeWHEREWs2.wait_time_ms-Ws1.wait_time_ms> 0ORDER  byWs2.wait_time_ms-Ws1.wait_time_msDESC

When looking at the difference between resource wait times and signal wait times, it's best to look at the ratio of the two in total time.

SELECT SUM(Signal_wait_time_ms) asTotal_signal_wait_time_ms,SUM(Wait_time_ms-Signal_wait_time_ms) asResource_wait_time_ms,SUM(Signal_wait_time_ms)* 1.0 / SUM(Wait_time_ms)*  -  asSignal_wait_percent,SUM(Wait_time_ms-Signal_wait_time_ms)* 1.0 / SUM(Wait_time_ms)*  -  asresource_wait_percent fromSys.dm_os_wait_stats

Wait statistics that occur with SQL Server