SQL Server WaitStats Script

• yusufkahveci

  SSC-Addicted

  Points: 466

  More actions

  January 8, 2018 at 1:06 pm

  #325294

  Comments posted to this topic are about the item SQL Server WaitStats Script

  SQL Server Consultant
  yusufkahveci@sqlturkiye.com
  www.sqlturkiye.com

• vesa.juvonen

  SSC-Addicted

  Points: 400

  More actions

  January 18, 2018 at 3:05 am

  #1976286

  Thanks for your effort, but why inventing the wheel once again...?
  Hashtag Paul Randal...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 18, 2018 at 4:05 am

  #1976289

  I don't think he's reinvented the wheel, so much as copied it from here.

  John

• chuck.hamilton

  SSCommitted

  Points: 1539

  More actions

  June 12, 2018 at 9:01 am

  #1993701

  Even better is to capture the wait_stats at two (or more) different times and query the delta. I have this script that gives me a quick 10 second snapshot of server activity. I have a much more complex system that captures snapshots every 30 minutes and an SSRS report that shows the deltas graphically.

  
-- Report waits over a 10 second interval. Ignore's ignorable waits.
-- To change the interval change next line.
DECLARE @delay VARCHAR(10) = '0:0:10' -- hh:mm:ss

  IF object_id('tempdb.dbo.#waits1') IS NOT NULL
    DROP TABLE #waits1

  IF object_id('tempdb.dbo.#waits2') IS NOT NULL
    DROP TABLE #waits2

  DECLARE @start BIGINT, @start_time datetime
    ,@end BIGINT, @end_time datetime

  SELECT @start = cntr_value, @start_time = getdate()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'

  SELECT *
INTO #waits1
FROM master.sys.dm_os_wait_stats

  WAITFOR DELAY @delay

  SELECT *
INTO #waits2
FROM master.sys.dm_os_wait_stats

  SELECT @end = cntr_value, @end_time = getdate()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'

  SELECT @start_time as start_time, @end_time as end_time,
(@end - @start) / datediff(s,@start_time, @end_time) AS [Batch Requests/sec]

  SELECT b.wait_type [Wait Type]
    ,b.wait_time_ms - a.wait_time_ms AS [Wait Time in MS (Delta)]
    ,(b.wait_time_ms - a.wait_time_ms)/datediff(second,@start_time,@end_time) as [Wait Time ms/sec]
    ,b.max_wait_time_ms [Max Wait Time in MS (Since Startup)]
    ,b.signal_wait_time_ms - a.signal_wait_time_ms AS [Signal Wait Time in MS (Delta)]
    ,b.waiting_tasks_count - a.waiting_tasks_count as [Number of Waits]
    ,(b.wait_time_ms - a.wait_time_ms) / (b.waiting_tasks_count - a.waiting_tasks_count) as [Avg Wait in MS]
FROM #waits1 a
JOIN #waits2 b ON b.wait_type = a.wait_type
WHERE b.wait_type NOT IN (    -- ignore "idle" events
            'REQUEST_FOR_DEADLOCK_SEARCH'            ,'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
            ,'SQLTRACE_BUFFER_FLUSH'                ,'LAZYWRITER_SLEEP'
            ,'XE_TIMER_EVENT'                        ,'XE_DISPATCHER_WAIT'
            ,'FT_IFTS_SCHEDULER_IDLE_WAIT'            ,'LOGMGR_QUEUE'
            ,'CHECKPOINT_QUEUE'                        ,'BROKER_TO_FLUSH'
            ,'BROKER_TASK_STOP'                        ,'BROKER_EVENTHANDLER'
            ,'SLEEP_TASK'                            ,'WAITFOR'
            ,'DBMIRROR_DBM_MUTEX'                    ,'DBMIRROR_EVENTS_QUEUE'
            ,'DBMIRRORING_CMD'                        ,'DISPATCHER_QUEUE_SEMAPHORE'
            ,'BROKER_RECEIVE_WAITFOR'                ,'CLR_AUTO_EVENT'
            ,'DIRTY_PAGE_POLL'                        ,'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
            ,'ONDEMAND_TASK_QUEUE'                    ,'FT_IFTSHC_MUTEX'
            ,'CLR_MANUAL_EVENT'                        ,'SP_SERVER_DIAGNOSTICS_SLEEP'
            ,'UCS_SESSION_REGISTRATION'                ,'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
            ,'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
        )
        and b.wait_type not like 'BROKER\_%' escape '\'
        and b.wait_type not like 'HADR\_%' escape '\'
and b.waiting_tasks_count > a.waiting_tasks_count -- ignore events that didn't occur during window
ORDER BY 2 DESC