Benchmark SQL Server Wait Statistics

Posted on October 8, 2013 by | 12 Comments

One of the secret weapons in performance tuning with SQL Server is understanding wait statistics. Every time a process (spid) changes its status from running to suspended it is typically due to resource wait or a signal wait. For example, you are typically waiting on PAGEIOLATCH_SH when you run a query that needs to pull data from disk into memory for a select statement. Understanding wait statistics can be a very helpful tool in your tool-belt if you are brought in to troubleshoot a critical issue due to slow performance. Wait statistics can quickly eliminate several resources that aren’t causing your performance problem and allow you to focus on the root cause.

** Download all scripts here **

How Do We Get Wait Statistics?

Starting in SQL Server 2005 we were given access to dynamic management views and functions. One that is very helpful with collecting waits is sys.dm_os_wait_stats.  You can access your top waits by using the following query.

SELECT TOP 10 *
FROM sys.dm_os_wait_stats
where wait_time_ms > 0
order by wait_time_ms desc

This DMV collects waits from the time the instance starts unless its cleared with the following DBBC command below. I strongly recommend that you grab snapshots of this view and compare instead of purging the history. Once you run the DBCC SQLPERF command you lose all history of your waits. We will talk a little bit more about this in the next section.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

Mistakes with Wait Statistics

Typically in the field I have seen two different mistakes with Wait Statistics. Both mistakes are fixable. The first is an easy fix, the second one takes some knowledge to prevent knee jerk reactions that can cause more problems.

The first mistake I see is people constantly running DBCC SQLPERF to clear out the sys.dm_os_wait_stats to get a starting point. Typically, one would clear out all the waits wait a little bit and use a great query like Paul Randal’s – tell me why it hurts script or Glen Berry’s DMV scripts  to see what waits occurred in the last few seconds. Logically, this seems okay but you just dumped your history of wait statistics. You cannot go back and see what were your top resource waits over time.  I strongly recommend capturing a snapshot of this view, waiting a few seconds and then capturing it again and then compare the two captures. This way you still have your history.

The second mistake is not understanding the wait types or making bad decisions based on the top wait types.  Are there good waits types? What reactions should I take due to the wait types? These are both very good questions and we cover them at a basic view in this post. First, thing I recommend doing before running any wait stats queries is get a good reference list of waits types. I personally recommend MS Whitepaper on Waits and Queues and The SQL Server Wait Type Repository by CSS SQL Server Engineers. These can be very helpful when you need to understand your top waits or build an action plan behind your waits.

Let’s take a look at the TOP 10 query result set from the query above.
Top WaitsIf you look at the top four waits in CSS SQL Server Engineers blog you will notice that for various reasons it is safe to skip these wait types. Therefore, when were collecting waits we would want to bypass waits that are not indication of resource pressure actionable.

Finally, you need to be careful about the advice on the internet. It can be like taking candy from strangers. There still quite a bit of misleading advice that can cause more harm than good. An easy example is CXPACKET waits. There are articles that recommend setting Max Degree of Parallelism to 1 for the instance. This would actually be the last thing I would want to do by just seeing CXPACKET as a top wait type.  I would want to find the queries causing  CXPACKET waits see if they are effecting the business  and tune them if needed. If that isn’t possible than I would consider this recommendation.

Capture Wait Statisitcs

So, now if you are still reading you have read about why understanding wait statistics is a good thing, basics of collecting wait statistics, and common mistakes with wait statistics. Let’s jump to how I go about collecting wait statistics.

The following script will utilize three temp tables. One for a snapshot of wait statistics and another for comparing waits, and the last table for wait types we would want to skip due to the waits being background processes or waits that could safely be ignored. It will get a delta of your wait statistics for five minutes.

/* Create temp tables to capture wait stats to compare */
IF OBJECT_ID('tempdb..#WaitStatsBench') IS NOT NULL
DROP TABLE #WaitStatsBench
IF OBJECT_ID('tempdb..#WaitStatsFinal') IS NOT NULL
DROP TABLE #WaitStatsFinal
IF OBJECT_ID('tempdb..#BenignWaits') IS NOT NULL
DROP TABLE #BenignWaits

CREATE TABLE #WaitStatsBench (WaitType varchar(200), wait_S decimal(12,5), Resource_S decimal (12,5), Signal_S decimal (12,5), WaitCount bigint)
CREATE TABLE #WaitStatsFinal (WaitType varchar(200), wait_S decimal(12,5), Resource_S decimal (12,5), Signal_S decimal (12,5), WaitCount bigint)
CREATE TABLE #BenignWaits (WaitType varchar(200))

/* Insert Benign Waits */

INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_SEMAPHORE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('LAZYWRITER_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('RESOURCE_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_TASK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_SYSTEMTASK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_BUFFER_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('WAITFOR')
INSERT INTO #BenignWaits (WaitType)
VALUES ('LOGMGR_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CHECKPOINT_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('REQUEST_FOR_DEADLOCK_SEARCH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_TIMER_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TO_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TASK_STOP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_MANUAL_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_AUTO_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DISPATCHER_QUEUE_SEMAPHORE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_WAIT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_JOIN')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_EVENTHANDLER')
INSERT INTO #BenignWaits (WaitType)
VALUES ('TRACEWRITE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('FT_IFTSHC_MUTEX')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_RECEIVE_WAITFOR')
INSERT INTO #BenignWaits (WaitType)
VALUES ('ONDEMAND_TASK_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DBMIRROR_EVENTS_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DBMIRRORING_CMD')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TRANSMITTER')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_WAIT_ENTRIES')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_BPOOL_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_LOCK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DIRTY_PAGE_POLL')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION')
GO

/* Get baseline */
INSERT INTO #WaitStatsBench (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_type not in (select BW.WaitType from #BenignWaits BW)
AND wait_time_ms > 0

/**** Wait some time... ******/
DECLARE @WaitSeconds int = 300
WAITFOR DELAY @WaitSeconds
GO

INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_type not in (select BW.WaitType from #BenignWaits BW)
AND wait_time_ms > 0
SELECT f.WaitType,
f.wait_S - b.wait_S as Wait_S,
f.Resource_S - b.Resource_S as Resource_S,
f.Signal_S - b.Signal_S as Signal_S,
f.WaitCount - b.WaitCount as WaitCounts,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S
FROM #WaitStatsFinal f
LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)
WHERE (f.wait_S - b.wait_S) > 0
ORDER BY 2 desc, 4 desc

Benchmark Wait Statistics

Finally, I will include the code used for creating a stored procedure that will give you control on how long the wait statistics capture runs and how often we should collect during the capture process.

/***************************************************************************
    Author : John Sterrett, Procure SQL LLC

    File:     WaitStats.sql

    Summary:  This script leverages sys.dm_os_wait_stats and runs twice with 
              a wait specified by @WaitSeconds in between. These two captures 
              are then compaired and then saved.    

    Parameter: @WaitSeconds int - is used to wait between captures to compare.

    Date:      October 2013

    Version:  SQL 2005+ 
  

  ---------------------------------------------------------------------------
  
  For more scripts and sample code, check out 
    https://johnsterrett.com

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.

  Other related links:
 http://technet.microsoft.com/library/Cc966413

    Further Reading (Not By Me):

SQL Server Wait Statistics (or please tell me where it hurts…)

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
https://www.simple-talk.com/sql/database-administration/a-first-look-at-sql-server-2012-availability-group-wait-statistics/

************************************************************************/


     

*/
If NOT EXISTS (Select 1 from sys.schemas where name = N'Waits')
        execute sp_executesql @stmt = N'CREATE SCHEMA [Waits] AUTHORIZATION [dbo];'



CREATE TABLE Waits.WaitStats (CaptureDataID bigint, WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint, Avg_Wait_S numeric(10, 6), Avg_Resource_S numeric(10, 6),Avg_Signal_S numeric(10, 6), CaptureDate datetime)
CREATE TABLE Waits.BenignWaits (WaitType varchar(200))
CREATE TABLE Waits.CaptureData (
ID bigint identity PRIMARY KEY,
StartTime datetime,
EndTime datetime,
ServerName varchar(500),
PullPeriod int
)

INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LAZYWRITER_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('RESOURCE_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_TASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_SYSTEMTASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_BUFFER_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LOGMGR_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CHECKPOINT_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('REQUEST_FOR_DEADLOCK_SEARCH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_TIMER_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES  ('BROKER_TO_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TASK_STOP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_MANUAL_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_AUTO_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DISPATCHER_QUEUE_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_JOIN')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_EVENTHANDLER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('TRACEWRITE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTSHC_MUTEX')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_RECEIVE_WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('ONDEMAND_TASK_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRROR_EVENTS_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRRORING_CMD')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TRANSMITTER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_WAIT_ENTRIES')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_BPOOL_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_LOCK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DIRTY_PAGE_POLL')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_WORK_QUEUE')

insert Waits.BenignWaits (WaitType) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
insert Waits.BenignWaits (WaitType) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
GO

--DROP PROCEDURE Waits.GetWaitStats
CREATE PROCEDURE Waits.GetWaitStats 
    @WaitTimeSec INT = 60,
    @StopTime DATETIME = NULL
AS
BEGIN
    DECLARE @CaptureDataID int
    /* Create temp tables to capture wait stats to compare */
    IF OBJECT_ID('tempdb..#WaitStatsBench') IS NOT NULL
        DROP TABLE #WaitStatsBench
    IF OBJECT_ID('tempdb..#WaitStatsFinal') IS NOT NULL
        DROP TABLE #WaitStatsFinal

    CREATE TABLE #WaitStatsBench (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)
    CREATE TABLE #WaitStatsFinal (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)

    DECLARE @ServerName varchar(300)
    SELECT @ServerName = convert(nvarchar(128), serverproperty('servername'))
    
    /* Insert master record for capture data */
    INSERT INTO Waits.CaptureData (StartTime, EndTime, ServerName,PullPeriod)
    VALUES (GETDATE(), NULL, @ServerName, @WaitTimeSec)
    
    SELECT @CaptureDataID = SCOPE_IDENTITY()
     
/* Loop through until time expires  */
    IF @StopTime IS NULL
        SET @StopTime = DATEADD(hh, 1, getdate())
    WHILE GETDATE() < @StopTime
    BEGIN

        /* Get baseline */
        
        INSERT INTO #WaitStatsBench (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
        SELECT
                wait_type,
                wait_time_ms / 1000.0 AS WaitS,
                (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
                signal_wait_time_ms / 1000.0 AS SignalS,
                waiting_tasks_count AS WaitCount
            FROM sys.dm_os_wait_stats
            WHERE wait_time_ms > 0.01 
            AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)
        

        /* Wait a few minutes and get final snapshot */
        WAITFOR DELAY @WaitTimeSec;

        INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
        SELECT
                wait_type,
                wait_time_ms / 1000.0 AS WaitS,
                (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
                signal_wait_time_ms / 1000.0 AS SignalS,
                waiting_tasks_count AS WaitCount
            FROM sys.dm_os_wait_stats
            WHERE wait_time_ms > 0.01
            AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)
        
        DECLARE @CaptureTime datetime 
        SET @CaptureTime = getdate()

        INSERT INTO Waits.WaitStats (CaptureDataID, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Avg_Wait_S, Avg_Resource_S,Avg_Signal_S, CaptureDate)
        SELECT  @CaptureDataID,
            f.WaitType,
            f.wait_S - b.wait_S as Wait_S,
            f.Resource_S - b.Resource_S as Resource_S,
            f.Signal_S - b.Signal_S as Signal_S,
            f.WaitCount - b.WaitCount as WaitCounts,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S,
            @CaptureTime
        FROM #WaitStatsFinal f
        LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)
        WHERE (f.wait_S - b.wait_S) > 0.0 -- Added to not record zero waits in a time interval.
        
        TRUNCATE TABLE #WaitStatsBench
        TRUNCATE TABLE #WaitStatsFinal
 END -- END of WHILE
 
 /* Update Capture Data meta-data to include end time */
 UPDATE Waits.CaptureData
 SET EndTime = GETDATE()
 WHERE ID = @CaptureDataID
END

Now, you can run the following block of code to capture your wait statistics.

DECLARE @EndTime datetime, @WaitSeconds int
SELECT @EndTime = DATEADD(MINUTE, 30, getdate()),
@WaitSeconds = 30

EXEC Waits.GetWaitStats
@WaitTimeSec = @WaitSeconds,
@StopTime = @EndTime

If you liked this blog post check out my next one where I focus on finding the queries that caused my waits.

This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

12 responses to “Benchmark SQL Server Wait Statistics”

  1. Pingback: What Queries are Causing My Waits? - SQL Server - SQL Server - Toad World

  2. Pingback: What Queries are Causing My Waits? - SQL Server - SQL Server - Toad World

  3. Pingback: (SFTW) SQL Server Links 11/10/13 • John Sansom

  4. Pingback: Finding Top Offenders From Cache - SQL Server - SQL Server - Toad World

  5. Pingback: SQL Server Performance Root Cause Analysis in 10 Minutes - SQL Server - SQL Server - Toad World

  6. Pingback: SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002 | Journey to SQL Authority with Pinal Dave

  7. Pingback: T-SQL Tuesday #49: Wait for it | SQL Studies

  8. Pingback: T-SQL Tuesday #49: Wait for it - SQL Server - SQL Server - Toad World

  9. Pingback: T-SQL Tuesday #50: Automation for LazyDBAs! - SQL Server - SQL Server - Toad World

  10. Pingback: Benchmark Azure SQL Database Wait Stats | JohnSterrett.com SQL Server Consulting

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.