Retrieve SQL Server Wait Status based on sys.dm_os_wait_stats

use master
go

-- create begin snapshot
select * into #dm_os_wait_stats_b from sys.dm_os_wait_stats
go

-- wait for 300 seconds
WAITFOR DELAY '00:05:00'
go

-- create end snapshot
select * into #dm_os_wait_stats_e from sys.dm_os_wait_stats
go

-- retrieve top 50 wait events
select top 50 wait_type,
  delta_waiting_tasks_count,
  delta_wait_time_ms,
  max_wait_time_ms,
  delta_signal_wait_time_ms
from
(
select e.wait_type,
 e.waiting_tasks_count-b.waiting_tasks_count delta_waiting_tasks_count,
 e.wait_time_ms-b.wait_time_ms delta_wait_time_ms,
 e.max_wait_time_ms,
 e.signal_wait_time_ms-b.signal_wait_time_ms delta_signal_wait_time_ms
from #dm_os_wait_stats_b b join #dm_os_wait_stats_e e
 on b.wait_type=e.wait_type
) delta
    where wait_type not in
(
 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
      'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
      'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','XE_TIMER_EVENT'
) -- filter out additional irrelevant waits
and delta_wait_time_ms>0
order by delta_wait_time_ms desc;


-- clean up
drop table #dm_os_wait_stats_b
go

drop table #dm_os_wait_stats_e
go