SQL Server's expectation statistics or please tell me where it hurts

How many times have you experienced SQL Server performance issues and decided where to look?

One of the rarely used SQL Server performance troubleshooting methodologies is called Expectations and Queues (also known as Expectations Statistics). The basic premise of the methodology is that SQL Server constantly monitors which threads should wait. You can request this information from SQL Server in order to shorten the list of possible causes of performance problems. Expectations is what SQL Server monitors. Queues are resources that threads are waiting to access. The system usually captures a huge number of expectations, and all of them mean waiting for access to various resources. For example, waiting for PAGEIOLATCH_EX means that the stream is waiting to read a data page from disk to the buffer pool. Waiting for LCK_M_X means

The great news is that SQL Server knows exactly what the performance problems are, and all you need to do is ask him ... and then correctly interpret what he says, which can be a little more complicated.

The following information is for people who worry about every expectation and understand what causes it. Expectations always arise . This is how SQL Server’s scheduling system works.

The stream uses the processor and has the status “RUNNING” until it is faced with the need to wait for access to the resource. In this case, it is placed in an unordered list of threads in the suspended state (SUSPENDED). At the same time, the next thread in the queue of threads waiting for access to the processor organized by the FIFO principle (first-in-first out) and having the status “ready to run” (RUNNABLE) gets access to the processor and becomes “running”. If a thread in the “suspended” state receives a notification that its resource is available, it becomes “ready for execution” and is placed at the end of the queue of threads ready for execution. The flow continues its cyclic movement along the chain “running” - “suspended” - “ready for execution” until the task is completed.

SQL Server monitors the time that elapses between a thread exiting from a running state and returning to that state, defining it as a “wait time” and time spent in a ready state, defining it as “time waiting for a signal ”(signal wait time), i.e. how much time does the thread need after receiving a signal about the availability of resources in order to gain access to the processor. We need to understand how much time the thread spends in the “paused” state, called the “resource wait time”, by subtracting the signal timeout from the total timeout.

An excellent source of information that I recommend reading about this is the new (2014) document on expectation statistics.“Tuning SQL Server Performance Using Expectation Statistics: A Beginner's Guide” (English), which I advise you to read. There is also a much older document ( Performance Tuning Using Expectations and Queues (English) with a lot of useful information, but quite outdated at the moment. The best tutorial on various types of expectations (and classes of short-term locks) is my comprehensive library of expectations (English) and short-term locks (English).

You can query SQL Server for accumulated wait statistics using DMV sys.dm_os_wait_stats. Many people prefer to wrap a DMV call in some kind of summary code. Below is the latest version of my script as of 2016, which works with all versions and includes wait types for SQL Server 2016 (see the version of the script for use in Azure here ):

WITH [Waits] AS
    (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],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        -- Maybe uncomment these four if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        -- Maybe uncomment these six if you have AG issues
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

The query result will show the expectations, grouped by percentage of all expectations in the system, in descending order. Expectations that are (potentially) worth paying attention to are at the top of the list and represent most of the expectations SQL Server spends. You see a large list of expectations that are removed from consideration - as I said earlier, they always arise and those that are listed above, we can usually ignore.

You can also reset statistics accumulated by the server using this code:

DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
GO

And, of course, you can come to the point of saving the results every few hours or every day and do some temporary analysis in order to find out the direction of the changes, or automatically track problems if they start to appear.
You can also use the Performance Dashboard to graphically display the results in SQL Server 2005 and the Data Collector in SQL Server 2008. In SQL Server 2000 you can use DBCC SQLPERF (N'waitstats') .

Once you get the results, you will begin to think about how to interpret them and where to look. The document I referenced earlier has a wealth of information on most types of expectations (with the exception of those added in SQL Server 2008).

Now I would like to provide the results of a study that I published some time ago. I asked people to run the code above and let me know the results. I got a huge amount of results - from 1823 servers - thanks!

Below is a graphical representation of the results:



I am not at all surprised at the top 4 results, as I have seen them again and again on my client systems.

In the continuation of my article, I am going to list the most popular types of expectations provided by survey respondents in descending order, and comment in a few words what exactly they can mean if they are fundamental to your system. The list format shows the number of systems from the respondents in which the specified type of waiting is the main one.

• 505: CXPACKET
  Means concurrency, but not necessarily the problem. The coordinator thread in a parallel request always accumulates these expectations. If parallel threads are not busy or one of the threads is blocked, then waiting threads also accumulate CXPACKET waiting, which leads to faster accumulation of statistics on this type - this is the problem. One thread may have more work than the others, and for this reason the entire request is blocked until a long thread finishes its work. If this type of wait is combined with large wait digits PAGEIOLATCH_XX, then it may be scanning large tables due to incorrect non-clustered indexes or due to a poor query execution plan. If this is not the reason, you can try using the MAXDOP option with values ​​of 4, 2, or 1 for problematic queries or for the entire server instance (set on the server with the "max degree of parallelism" parameter). If your system is NUMA based, try setting MAXDOP to the number of processors in a single NUMA node to determine if this is the problem. You also need to determine the effect of installing MAXDOP on mixed-load systems. To be honest, I would play around with the “cost threshold for parallelism” parameter (raise it to 25 for starters) before lowering the MAXDOP value for the whole instance. And don't forget about the Resource Governor in the Enterprise version of SQL Server 2008, which allows you to set the number of processors for a specific group of server connections. If your system is NUMA based, try setting MAXDOP to the number of processors in a single NUMA node to determine if this is the problem. You also need to determine the effect of installing MAXDOP on mixed-load systems. To be honest, I would play around with the “cost threshold for parallelism” parameter (raise it to 25 for starters) before lowering the MAXDOP value for the whole instance. And don't forget about the Resource Governor in the Enterprise version of SQL Server 2008, which allows you to set the number of processors for a specific group of server connections. If your system is NUMA based, try setting MAXDOP to the number of processors in a single NUMA node to determine if this is the problem. You also need to determine the effect of installing MAXDOP on mixed-load systems. To be honest, I would play around with the “cost threshold for parallelism” parameter (raise it to 25 for starters) before lowering the MAXDOP value for the whole instance. And don't forget about the Resource Governor in the Enterprise version of SQL Server 2008, which allows you to set the number of processors for a specific group of server connections. You also need to determine the effect of installing MAXDOP on mixed-load systems. To be honest, I would play around with the “cost threshold for parallelism” parameter (raise it to 25 for starters) before lowering the MAXDOP value for the whole instance. And don't forget about the Resource Governor in the Enterprise version of SQL Server 2008, which allows you to set the number of processors for a specific group of server connections. You also need to determine the effect of installing MAXDOP on mixed-load systems. To be honest, I would play around with the “cost threshold for parallelism” parameter (raise it to 25 for starters) before lowering the MAXDOP value for the whole instance. And don't forget about the Resource Governor in the Enterprise version of SQL Server 2008, which allows you to set the number of processors for a specific group of server connections.
• 304: PAGEIOLATCH_XX
  This is where SQL Server waits to read a data page from disk to memory. This type of wait may indicate a problem in the I / O system (which is the first reaction to this type of wait), but why should the I / O system serve so many reads? Perhaps the pressure is exerted by the buffer pool / memory (there is not enough memory for a typical load), a sudden change in execution plans, leading to large parallel scans instead of searching, bloating the plan cache or some other reasons. Do not assume that the main problem in the input / output system.
• 275: ASYNC_NETWORK_IO
  Here, SQL Server waits for the client to finish receiving data. The reason may be that the client requested too much data or simply receives it sooooo slowly due to bad code - I almost never saw the problem being on the network. Clients often read one line at a time — the so-called RBAR or Row-By-Agonizing-Row — instead of caching data on the client and notifying SQL Server to finish reading immediately.
• 112: WRITELOG
  The log management subsystem is waiting for the log to be written to disk. As a rule, this means that the input / input system cannot provide a timely record of the entire log volume, but on highly loaded systems this may be caused by general restrictions on logging, which may mean that you should share the load between several databases, or even make your transactions a little longer to reduce the number of log entries to disk. To verify that the cause is in the I / O system, use DMV sys.dm_io_virtual_file_stats to examine the I / O delay for the log file and see if it matches the WRITELOG delay time. If WRITELOG lasts longer, you have received internal competition for writing to disk and must share the load. If not, find out why you are creating such a large transaction log.Here (English) and here (English) some ideas can be gleaned.
  (Translator's note: the following query allows you to get statistics on I / O delays for each file of each database on the server in a simple and convenient way:
  
  Hidden text

  -- Плохо: Ср.задержка одной операции > 20 мсек
  USE master
  GO
  SELECT cast(db_name(a.database_id) AS VARCHAR) AS Database_Name
  	 , b.physical_name
  	 --, a.io_stall
  	 , a.size_on_disk_bytes
  	 , a.io_stall_read_ms / a.num_of_reads 'Ср.задержка одной операции чтения'
  	 , a.io_stall_write_ms / a.num_of_writes 'Ср.задержка одной операции записи'
  	 --, *
  FROM
  	sys.dm_io_virtual_file_stats(NULL, NULL) a
  	INNER JOIN sys.master_files b
  		ON a.database_id = b.database_id AND a.file_id = b.file_id
  where num_of_writes > 0 and num_of_reads > 0
  ORDER BY
  	Database_Name
    , a.io_stall DESC
  

• 109: BROKER_RECEIVE_WAITFOR
  Here, Service Broker is waiting for new messages. I would recommend adding this expectation to the exclusion list and re-execute the query with wait statistics.
• 086: MSQL_XP
  Here, SQL Server waits for extended stored procedures to execute. This may indicate a problem in the code of your extended stored procedures.
• 074: OLEDB
  As the name implies, this is an expectation of interaction using OLEDB — for example, with a linked server. However, OLEDB is also used in DMV and the DBCC CHECKDB team, so do not think that the problem is necessarily in the linked servers - it may be an external monitoring system that makes excessive use of DMV calls. If this is indeed a linked server, then analyze the expectations on the linked server and determine what the performance problem is on it.
• 054: BACKUPIO
  Shows when you backup directly to tape, which is soooo slow. I would rather filter out this expectation. (Translator's note: I met this type of expectation when writing a backup to disk, while the backup of a small database took a very long time, not having time to execute during a technological break and causing performance problems for users. If this is your case, it may be the input system / output used for backups, you must consider the possibility of increasing its productivity or revise the maintenance plan (do not complete full backups in short technological breaks, replacing them with differential ones))
• 041: LCK_M_XX
  Here, the thread is just waiting for access to lock on the object and means problems with locks. This can be caused by unwanted escalation of locks or bad code, but it can also be caused by I / O operations taking too long and holding locks longer than usual. Look at lock-related resources using DMV sys.dm_os_waiting_tasks. Do not assume that the main problem is locks.
• 032: ONDEMAND_TASK_QUEUE
  This is normal and is part of a system of background tasks (such as delayed reset, background cleanup). I would add this expectation to the exclusion list and re-execute the query with wait statistics.
• 031: BACKUPBUFFER
  Indicates when you backup directly to tape, which is soooo slow. I would rather filter out this expectation.
• 027: IO_COMPLETION
  SQL Server is waiting for I / O to complete and this type of wait can be an indicator of an I / O system problem.
• 024: SOS_SCHEDULER_YIELD
  Most often it is code that does not fall into other types of wait, but sometimes it can be a competition in a circular lock.
• 022: DBMIRROR_EVENTS_QUEUE
  022: DBMIRRORING_CMD
  These two types indicate that the database mirroring system is sitting and waiting for something to do. I would add these expectations to the exclusion list and re-execute the query with wait statistics.
• 018: PAGELATCH_XX
  This is a competition for access to copies of pages in memory. The most well-known cases are the competition between PFS, SGAM, and GAM, which occur in the tempdb database for certain types of loads . In order to find out which pages are competing for, you need to use DMV sys.dm_os_waiting_tasks in order to find out which pages cause blockages. Regarding problems with the tempdb database, Robert Davis (his blog , twitter ) wrote a good article showing how to solve them . Another common reason I saw was a frequently updated index with competing inserts into an index using a serial key (IDENTITY).
• 016: LATCH_XX
  This is a competition for any non-page structures in SQL Server — so it’s not related to I / O and data in general. The reason for this type of delay can be quite difficult to understand and you need to use DMV sys.dm_os_latch_stats.
• 013: PREEMPTIVE_OS_PIPEOPS
  Here, SQL Server switches to proactive scheduling to request something from Windows. This type of wait was added in the 2008 version and has not yet been documented. The easiest way to find out what it means is to remove the initial PREEMPTIVE_OS_ and look for what is left in MSDN - this will be the name of the Windows API.
• 013: THREADPOOL
  This type says that there are not enough worker threads in the system to satisfy the request. Typically, the reason is the large number of highly parallelized requests trying to execute. (Translator's note: it can also be an intentionally truncated value of the “max worker threads” server parameter)
• 009: BROKER_TRANSMITTER
  Here Service Broker is waiting for new messages to be sent. I would recommend adding this expectation to the exclusion list and re-execute the query with wait statistics.
• 006: SQLTRACE_WAIT_ENTRIES
  Part of the SQL Server listener (trace). I would recommend adding this expectation to the exclusion list and re-execute the query with wait statistics.
• 005: DBMIRROR_DBM_MUTEX
  This is one of the undocumented types and in it there is competition for sending a buffer that is shared between database mirroring sessions. It may mean that you have too many mirroring sessions.
• 005: RESOURCE_SEMAPHORE
  Here, the request waits for memory to execute (the memory used to process the query operators - such as sorting). This may be a lack of memory at a competitive load.
• 003: PREEMPTIVE_OS_AUTHENTICATIONOPS
  003: PREEMPTIVE_OS_GENERICOPS
  Here, SQL Server switches to proactive scheduling to request Windows for something. This type of wait was added in the 2008 version and has not yet been documented. The easiest way to find out what it means is to remove the initial PREEMPTIVE_OS_ and look for what is left in MSDN - this will be the name of the Windows API.
• 003: SLEEP_BPOOL_FLUSH
  This expectation can often be seen and it means that the breakpoint limits itself to avoid overloading the I / O system. I would recommend adding this expectation to the exclusion list and re-execute the query with wait statistics.
• 002: MSQL_DQ
  Here, SQL Server waits for a distributed query to execute. This may mean problems with distributed requests, or it may just be the norm.
• 002: RESOURCE_SEMAPHORE_QUERY_COMPILE
  When there are too many competing recompilations of queries in the system, SQL Server limits their execution. I don’t remember the level of restriction, but this expectation may mean excessive recompilation or, perhaps, too frequent use of one-time plans.
• 001: DAC_INIT
  I had never seen this before and BOL says that the reason is the initialization of the administrative connection. I can’t imagine how this can be a priority expectation on someone’s system ...
• 001: MSSEARCH
  This type is normal for full-text operations. If this is a preemptive expectation, it may mean that your system spends the most time doing full-text queries. You might consider adding this type of wait to the exclusion list.
• 001: PREEMPTIVE_OS_FILEOPS
  001: PREEMPTIVE_OS_LIBRARYOPS
  001: PREEMPTIVE_OS_LOOKUPACCOUNTSID
  001: PREEMPTIVE_OS_QUERYREGISTRY
  Here, SQL Server switches to proactive scheduling to request Windows. This type of wait was added in the 2008 version and has not yet been documented. The easiest way to find out what it means is to remove the initial PREEMPTIVE_OS_ and look for what is left in MSDN - this will be the name of the Windows API.
• 001: SQLTRACE_LOCK
  Part of the SQL Server listener (trace). I would recommend adding this expectation to the exclusion list and re-execute the query with wait statistics.

Hope it was interesting! Let me know if you are interested in something specifically or that you have read this article and enjoyed it!