Home » The SQL Server Wait Types with their descriptions

The SQL Server Wait Types with their descriptions

sql server DBA 999

The SQL Server Wait Types with their descriptions

Wait Statistics in SQL Server indicate sql server performance in relation to different resources like CPU, Disk usage, Memory etc..

SQL Server Activity monitor waiting tasks is another important place to observe what SQL Server is waiting on. Activity monitor has different sections like Processes, Resource Waits, Recent Expensive Queries and Data IO Stats.

“Wait_Type, Area, Usage, Version” Description Action
“ASYNC_IO_COMPLETION
I/O
Resource
SQL 2005-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-2008”
Used by background worker that waits on events on queue to process checkpoint requests. 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-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-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-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.

The Top Waits Stats rollup query can be used to identify Top Waits Stats with running percentage here. It is used to Isolate top waits for server instance since last restart.

The SQL Server Wait Types with their description Continuation

“Wait_Type, Area, Usage, Version” Description Action
“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-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-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-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-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-2008”
Used by the background worker “Log Writer” to wait on a queue for requests to flush log blocks to the transaction log. 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-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.
“REQUEST_FOR _DEADLOCK_SEARCH
Lock
Background
SQL 2008”
Used by background worker “Lock Monitor” to search for deadlocks. 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-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-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-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-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-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-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”

Leave a Reply

Your email address will not be published. Required fields are marked *