There are plenty of scripts out there that can show you the waits that are occurring on your server, however, a lot of them do a lot more than just that. I recently wanted a script that does just the absolute minimum for me to specify a time window and get that wait stats.
If you’ve ever looked at this you’ll probably know you can see the wait stats in dm_os_wait_stats, however, this is aggregated so in order to see them over a window of time we need to first take a snapshot of how the stats look at the start of our window and compare it to the end. I’ve also included a number of wait types to filter out which are primarily based around ones I’ve not wanted to see so you will probably want to change this for your environment…
/* HH:MM:SS */
DECLARE @DelayString NVARCHAR(8) = '00:00:05'
SELECT * INTO #WaitTypesToIgnore
FROM(
SELECT 'SLEEP_TASK' AS WaitType
UNION ALL SELECT 'DIRTY_PAGE_POLL'
UNION ALL SELECT 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
UNION ALL SELECT 'LOGMGR_QUEUE'
UNION ALL SELECT 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
UNION ALL SELECT 'ONDEMAND_TASK_QUEUE'
UNION ALL SELECT 'FT_IFTSHC_MUTEX'
UNION ALL SELECT 'REQUEST_FOR_DEADLOCK_SEARCH'
UNION ALL SELECT 'LAZYWRITER_SLEEP'
UNION ALL SELECT 'SOS_WORK_DISPATCHER'
UNION ALL SELECT 'CHECKPOINT_QUEUE'
UNION ALL SELECT 'XE_TIMER_EVENT'
UNION ALL SELECT 'FT_IFTS_SCHEDULER_IDLE_WAIT'
UNION ALL SELECT 'BROKER_TO_FLUSH'
UNION ALL SELECT 'BROKER_TASK_STOP'
UNION ALL SELECT 'BROKER_EVENTHANDLER'
UNION ALL SELECT 'WAITFOR'
UNION ALL SELECT 'DBMIRROR_DBM_MUTEX'
UNION ALL SELECT 'DBMIRROR_EVENTS_QUEUE'
UNION ALL SELECT 'DBMIRRORING_CMD'
UNION ALL SELECT 'DISPATCHER_QUEUE_SEMAPHORE'
UNION ALL SELECT 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
UNION ALL SELECT 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
UNION ALL SELECT 'SP_SERVER_DIAGNOSTICS_SLEEP'
UNION ALL SELECT 'XE_DISPATCHER_WAIT'
UNION ALL SELECT 'REQUEST_FOR_DEADLOCK_SEARCH'
UNION ALL SELECT 'SQLTRACE_BUFFER_FLUSH'
UNION ALL SELECT 'XE_DISPATCHER_WAIT'
UNION ALL SELECT 'BROKER_RECEIVE_WAITFOR'
UNION ALL SELECT 'CLR_AUTO_EVENT'
UNION ALL SELECT 'DIRTY_PAGE_POLL'
UNION ALL SELECT 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
UNION ALL SELECT 'CLR_MANUAL_EVENT'
) x
SELECT dm_os_wait_stats.*
INTO #StartStats
FROM
sys.dm_os_wait_stats
LEFT JOIN #WaitTypesToIgnore ON dm_os_wait_stats.wait_type = #WaitTypesToIgnore.WaitType
WHERE
#WaitTypesToIgnore.WaitType IS NULL
WAITFOR DELAY @DelayString
SELECT
[now].wait_type,
[now].waiting_tasks_count - ISNULL([before].waiting_tasks_count,0) waiting_tasks_count,
[now].[wait_time_ms] - ISNULL([before].wait_time_ms,0) wait_time_ms,
[now].[max_wait_time_ms] - ISNULL([before].max_wait_time_ms,0) max_wait_time_ms,
[now].[signal_wait_time_ms] - ISNULL([before].signal_wait_time_ms,0) signal_wait_time_ms
FROM
sys.dm_os_wait_stats [now]
LEFT JOIN #StartStats [before] ON [before].wait_type = [now].Wait_type
LEFT JOIN #WaitTypesToIgnore ON #WaitTypesToIgnore.WaitType = [now].Wait_type
WHERE
#WaitTypesToIgnore.WaitType IS NULL AND
(
([now].waiting_tasks_count - ISNULL([before].waiting_tasks_count,0)) > 0
OR ([now].[wait_time_ms] - ISNULL([before].wait_time_ms,0)) > 0
OR ([now].[max_wait_time_ms] - ISNULL([before].max_wait_time_ms,0)) > 0
OR ([now].[signal_wait_time_ms] - ISNULL([before].signal_wait_time_ms,0)) > 0
)
ORDER BY [now].[wait_time_ms] - ISNULL([before].wait_time_ms,0) DESC