SQL Server WaitStats Script

  • yusufkahveci

    SSC-Addicted

    Points: 466

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

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

  • vesa.juvonen

    Old Hand

    Points: 340

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

  • John Mitchell-245523

    SSC Guru

    Points: 148777

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

    John

  • chuck.hamilton

    Default port

    Points: 1408

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply