The SQL Server Wait Type Repository…
12/10/2009: Significant updates to the blog. Check back in 2010 for more updates to the wait type table
As part of my talk at the 2009 US PASS Summit here in Seattle called Inside SQL Server Wait Types, I’m creating this blog post as a reference point that can be used to find out information about wait types in SQL Server 2005 and 2008. My hope is that if you have a question about a wait type you encounter with SQL Server 2005, 2008, or beyond, you will use this blog post as the starting point. This blog post should also be used as an enhancement to what you can find at the following SQL Server Books Online topic:
sys.dm_os_wait_stats (Transact-SQL)
Why use this blog post over other references? Because information in this blog post will be based on the the actual product source code and empirical testing of scenarios that cause these wait types.
The Wait Type Table
This table is to be used a repository of wait types as found in sys.dm_os_wait_stats in SQL Server 2005 and 2008. The current list is based on waits found in my recent talk at PASS, requests in the comments of the blog, or ones I’ve seen in BOL, newsgroup postings, etc I think you will find interesting. There is not documentation for all wait types. Not sure there ever will be. But if you see one you think I need to document, please comment this blog post.
Attached to this blog is the wait type table in the form of a spreadsheet. This will allow you to search and sort the data as necessary. However, for purposes of ensuring the information is searchable on he web, I’ve also pasted in the contents of the spreadsheet as part of this section. Any updates to the table will be in the form of an updated spreadsheet attachment and a repost of the pasted able. By default I’ll sort the pasted table by Type in ascending order.
You may ask “Where are all of the Wait Types documented?”. Well no where actually. Some are documented in the BOL as I’ve listed above. My table certainly won’t list out all ~485 wait types that are listed in sys.dm_os_wait_stats. But I will document ones i think may help you and include ones you send comments on you would like to know more information by commenting this blog. One thing that will help you is that I will try to document as many wait types that I can that you can typically “ignore” such as Usage=Background and waits that you can actually safely ignore because they simply are never used in the code. Below the wait type table I’ll also show you other waits I’m working on.
The spreadsheet has the following format:
Type – the name of the wait type as found in sys.dm_os_wait_stats
Area – This describes what part of the code the wait is used such as I/O, SQLOS, Network, Memory, Broker, Buffer, Query, …. Think of this as a keyword you can search on to find out wait types associated with a particular topic.
Usage – this is a Description of how the wait type is used within the engine. In other words why does the wait type exist. The possible values are:
Resource: Waiting for a resource to become available or complete such as I/O, Network, Thread, Memory
Sync: Waiting for synchronization to access a resource or code such as locks, latches, or other memory structures
Forced: Waiting is forced by the code such as Yielding or Sleep
External: These are waits that indicate code is running in Preemptive Mode
Background: Waits used mainly by background tasks to indicate they are waiting for work to do
Ignore: Waits that we could probably remove from the DMV as they are not even used in the code
Version – SQL Server 2005, SQL Server 2008
Description – A description of what this wait type means, where it is used in the code, and how you might encounter it
Action – Suggestion on possible actions you should take if you see this wait type show up as a possible “problem”
| Wait_Type | Area | Usage | Version | Description | Action |
| ASYNC_IO_COMPLETION | I/O | Resource | SQL 2005;SQL 2008 | Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages | Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type is used is to create files associated with a CREATE DATABASE and for "zeroing" out a transaction log file during log creation or growth. |
| CHECKPOINT_QUEUE | Buffer | Background | SQL 2005;SQL 2008 | Used by background worker that waits on events on queue to process checkpoint requests. This is an "optional" wait type see Important Notes section in blog | You should be able to safely ignore this one as it is just indicates the checkpoint background worker is waiting for work to do. I suppose if you thought you had issues with checkpoints not working or log truncation you might see if this worker ever "wakes up". Expect higher wait times as this will only wake up when work to do |
| CHKPT | Buffer | Background | SQL 2005; SQL 2008 | Used to coordinate the checkpoint background worker thread with recovery of master so checkpoint won't start accepting queue requests until master online | You should be able to safely ignore. You should see 1 wait of this type for the server unless the checkpoint worker crashed and had to be restarted.. If though this is technically a "sync" type of event I left its usage as Background |
| CXPACKET | Query | Sync | SQL 2005;SQL 2008 | Used to synchronize threads involved in a parallel query. This wait type only means a parallel query is executing. | You may not need to take any action. If you see high wait times then it means you have a long running parallel query. I would first identify the query and determine if you need to tune it. Note sys.dm_exec_requests only shows the wait type of the request even if multiple tasks have different wait types. When you see CXPACKET here look at all tasks associated with the request. Find the task that doesn't have this wait_type and see its status. It may be waiting on something else slowing down the query. wait_resource also has interesting details about the tasks and its parallel query operator |
| DISKIO_SUSPEND | BACKUP | Sync | SQL 2005; SQL 2008 | Used to indicate a worker is waiting to process I/O for a database or log file associated with a SNAPSHOT BACKUP | High wait times here indicate the SNAPSHOT BACKUP may be taking longer than expected. Typically the delay is within the VDI application perform the snapshot backup. |
| FT_IFTS_SCHEDULER_IDLE_WAIT | Full-Text | Background | SQL 2008 | Used by a background task processing full-text search requests indicating it is “waiting for work to do:” | You should be able to safely ignore unless some unexplained FTS issue. High wait times are normal |
| IO_COMPLETION | I/O | Resource | SQL 2005; SQL 2008 | Used to indicate a wait for I/O for operation (typically synchronous) like sorts and various situations where the engine needs to do a synchronous I/O | If wait times are high then you have a disk I/O bottleneck. The problem will be determining what type of operation and where the bottleneck exists. For sorts, it is on the storage system associated with tempdb. Note that database page I/O does not use this wait type. Instead look at PAGEIOLATCH waits. |
| KSOURCE_WAKEUP | Shutdown | Background | SQL 2005;SQL 2008 | Used by the background worker "signal handler" which waits for a signal to shutdown SQL Server | You should able to safely ignore this wait. You should only see one instance of this wait but in SQL Server 2008 what will be unusual is the wait time will show up as 0 in sys.dm_os_wait_stats. Other DMVs like sys.dm_exec_requests will show the SIGNAL_HANDLER with a high wait time of this type. |
| LAZYWRITER_SLEEP | Buffer | Background | SQL 2005;SQL 2008 | Used by the Lazywriter background worker to indicate it is sleeping waiting to wake up and check for work to do | You should be able to safely ignore this one. The wait times will appear to "cycle" as LazyWriter is designed to sleep and wake-up every 1 second. Appears as LZW_SLEEP in Xevent |
| LOGBUFFER | Transaction Log | Resource | SQL 2005; SQL 2008 | Used to indicate a worker thread is waiting for a log buffer to write log blocks for a transaction | This is typically a symptom of I/O bottlenecks because other workers waiting on WRITELOG will hold on to log blocks. Look for WRITERLOG waiters and if found the overall problem is I/O bottleneck on the storage system associated with the transaction log |
| LOGMGR_QUEUE | Transaction Log | Background | SQL 2005; SQL 2008 | Used by the background worker "Log Writer" to wait on a queue for requests to flush log blocks to the transaction log. This is an "optional" wait type see Important Notes section in blog | You should be able to safely ignore this wait type unless you believe a problem exists in processing log blocks to flush to the transaction log. This wait type is not a wait indicating I/O bottlenecks. It is only for waiting for other workers to request log block flushes. Note that on SQL Server 2005 this wait type will not show up in sys.dm_exec_requests because the Log Writer task does not show up there. |
| MISCELLANEOUS | Ignore | Ignore | SQL 2005;SQL 2008 | This really should be called "Not Waiting". | This may have been used in SQL 2000 but for 2005/2008, it is not used for any valid wait. It is simply the default wait in a list and isn't used to indicate any real waiting. This type shows up twice in sys.dm_os_wait_stats in SQL 2008 but the "other" instance is an older unused wait type in the code. We should be able to remove it. |
| PREEMPTIVE_XXX | Varies | External | SQL 2008 | Used to indicate a worker is running coded that is not under the SQLOS Scheduling Systems | I will specific PREEMPTIVE_XX wait types or groups of them in 2010. Be sure to read the Important Notes section for bug where this wait type is being over counted by the engine in some situations. Note also that when you see this wait_type in sys.dm_exec_requests the status of the request is RUNNING not SUSPENDED. This is because the engine doesn't really know if the thread is waiting or running "external" code. |
| REQUEST_FOR_DEADLOCK_SEARCH | Lock | Background | SQL 2008 | Used by background worker "Lock Monitor" to search for deadlocks. This is an "optional" wait type see Important Notes section in blog | You should be able to safely ignore this one as it is just and indication the lock monitor thread is temporarily sleeping before it wakes up to do work. This wait type should never exceed 5 seconds in one "wait" as this is the interval the lock monitor wakes up to check for deadlocks |
| RESOURCE_QUERY_SEMAPHORE_COMPILE | Query | Resource | SQL 2005; SQL 2008 | Used to indicate a worker is waiting to compile a query due to too many other concurrent query compilations that require "not small" amounts of memory. | This is a very complicated problem to explain. The problem is more than just concurrent compilations. It is the amount of memory required by the compilations. Typically this problem is not seen on 64bit systems. The biggest thing you can do is find out why you have so many compilations. Furthermore, a high amount of "query memory" can result in less memory available for compilations so check what other users are consuming high query memory. |
| RESOURCE_SEMAPHORE | Query | Resource | SQL 2005; SQL 2008 | Used to indicate a worker is waiting to be allowed to perform an operation requiring "query memory" such as hashes and sorts | High wait times indicate too many queries are running concurrently that require query memory. Operations requiring query memory are hashes and sorts. Use DMVs such as dm_exec_query_resource_semaphores and dm_exec_query_memory_grants |
| SOS_SCHEDULER_YIELD | SQLOS | Forced | SQL 2005;SQL 2008 | Used to indicate a worker has yielded to let other workers run on a scheduler | This wait is simply an indication that a worker yielded for someone else to run. High wait counts with low wait times usually mean CPU bound queries. High wait times here could be non-yielding problems |
| SQLTRACE_BUFFER_FLUSH | Trace | Background | SQL 2005;SQL 2008 | Used by background worker | You should be able to safely ignore unless some unexplained problem with SQLTrace files not getting written to disk properly. |
| THREADPOOL | SQLOS | Resource | SQL 2005; SQL 2008 | Indicates a wait for a task to be assigned to a worker thread | Look for symptoms of high blocking or contention problems with many of the workers especially if the wait count and times are high. Don't jump to increase max worker threads especially if you use default setting of 0. This wait type will not show up in sys.dm_exec_requests because it only occurs when the task is waiting on a worker thread. You must have a worker to become a request. Furthermore, you may not see this "live" since there may be no workers to process tasks for logins or for queries to look at DMVs. |
| WRITELOG | I/O | Sync | SQL 2005; SQL 2008 | Indicates a worker thread is waiting for LogWriter to flush log blocks. | High waits and wait times indicate an I/O bottleneck on the storage system associated with the transaction log |
| XE_DISPATCHER_WAIT | XEvent | Background | SQL 2008 | Used by a background worker to handle queue requests to write out buffers for async targets | You should be able to safely ignore this unless you believe a problem is occurring with processing of events for async targets. Since this works on a queue you can have bursts of high wait times especially when no XEvent sessions are active. |
| XE_TIMER_EVENT | XEvent | Background | SQL 2008 | Used to indicate a background task is waiting for "expired" timers for internal Xevent engine work | You should be able to safely ignore this one. Just used by the Xevent engine for internal processing of its work. If something was possibly wrong with Xevent processing you might see if this thread ever "wakes up" |
Work to be done:
- Go through and find all wait types that are not used in the code and mark these in the table with Ignore
- Broker waits. JonthanK has commented in the blog that many broker waits overall are documented at https://blogs.msdn.com/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx
- Go through BOL documentation on wait types, add them to this table and clarify more details about them
- Research exact usage of FT_IFTSHC_MUTEX. It appears from the code this is a sync type of wait for access to specific code for FTS but not apparent on its exact usage.
- Add specifics for PREEMPTIVE_XXX wait types
Important Notes
In this section, I list out important notes to keep mind about wait types:
1) We have a found a bug in the way PREMPTIVE_XXX wait types work in SQL Server 2008. In some cases, the engine will count a wait for a PREEMPTIVE_XX wait when the code really didn’t switch to preemptive mode. The scope of how often and which PREEMPTIVE_XXX wait types are affected is difficult to predict. This problem was first reported to me after my PASS talk when a customer ran DBCC FREEPROCCACHE. A large number of PREEMPTIVE_OLEDBOPS waits showed up. This is an example of where the engine marked a PREEMPTIVE_OLEDBOPS wait to free up internal information about a plan in cache when it should only apply to plan with linked servers for non SQL Server providers. We know other situations may apply, but I suspect PREEMPTIVE_OLEDBOPS is one of the wait types that is being “over-counted” in SQL Server 2008.
2) One thing I found as part of this research as I was not aware of is that some wait_type names can appear in SQL 2005 in DMVs like sys.dm_exec_requests but not sys.dm_os_wait_stats. An example of this is CHECKPOINT_QUEUE. In SQL Server 2008, some of these wait_type names were changed to be what I’ll call “optional”. By default the “optional” types show up in sys.dm_os_wait_stats. This explains an explosion of rows in this DMV in SQL Server 2008 not just because we added more. As part of this change, we added an undocumented trace flag 8050 which if enabled when querying sys.dm_os_wait_stats will exclude the “optional” wait types. However, When you enable this trace flag in SQL Server 2008 the number of rows is only reduced by about 50 so don’t think that this is an approach to filter out wait types you can “ignore”. But it does explain why some appeared in DMVs in SQL Server 2005 but not in sys.dm_os_wait_stats. So in the wait type table if you see a wait type that says it applies to both SQL Server 2005 and 2008, but you don’t see this wait type in sys.dm_os_wait_stats in SQL Server 2005, you will now know why.
Other Resources
SQLCAT Waits Stats Per Session CodePlex
SQLCAT SQL 2005 Waits and Queues Whitepaper
What’s Next?
I want this blog post and the attached spreadsheet to be a living document and help feed enhancements to the SQL Server Books Online. So if you encounter a question on a wait type that is confusing or not listed in this post, please post a comment to this blog post or email psssql@microsoft.com. This isn’t to obtain “free support” for a problem but to be used to help enhance and create the right content for the community on this topic. I’ll be posting regular updates to this blog throughout 2010 so take a specific feed and look for updates.
Bob Ward
Microsoft