DaveWentzel.com            All Things Data

Types of Waits and Their Meanings

 

For some background, check out SQL Server Execution Model
 
 
Wait Types
 
Waits are categorized into wait types.  A partial list of the wait types for 2005 are listed in the DMV sys.dm_os_wait_stats.  Some additional info can be found on the page <strong>dm_os_waiting_tasks</strong>.  If you really want to see every possible wait type then dbcc traceon (8001,-1). 
 
There are 4 basic categories of wait types:
  1. Resource - the largest
  2. Timer/Queue - where the task is waiting for the expiration of a timer or a new item in a q
  3. IO - includes network and disk IO. 
  4. External - anything external to the sql process.  Extended stored procs. 

Since I created the table below the CSS engineers have a continuously updated blog post that supposedly covers all wait types.  I'm going to keep my table just in case this becomes "proprietary" and removed later. 

 

WaitType
Purpose
Signal Waits
Look in dm_os_wait_stats for this.  Time sitting in Signal Waits is pure CPU time.  Your bottleneck is CPU.   It is the time a task waited between the completion of the wait event and the time it took to get on the scheduler again to continue its work.  As signal wait times get higher, all wait times get higher.  Nothing fixes signal waits other than additional CPU. 
CMEMTHREAD waiting on a thread-safe memory object.  Happens most often when ad hoc queries are inserting plans rapidly into the plan cache.  To avoid this try to limit ad hoc queries or parameterize as much as possible. 
CXPACKET
Parallelism.  If your OLTP system experiences > 5% of these, you have a problem that reduces throughput.  You could have a missing index or incomplete WHERE clause.  A good OLTP query will not parallelize. 
EXCHANGE or PSS_CHILD Parallelism.  Very similar to CXPACKET.  Generally, non-data page I/O.  Data page I/O is PAGEIO_LATCH_x.  Use dm_io_virtual_file_stats
PARALLEL_PAGE_SUPPLIER Parallelism.  Synchronizes the retrieval of pages. 
ASYNCH_IO_COMPLETION
IO bottleneck.  Use dm_io_virtual_file_stats for more info.
ASYNCH_NETWORK_IO occurs on network writes.  Ensure the client is processing data from the SQL Server.  Could be a server-side cursor fetch issue
IO_COMPLETION
IO bottleneck potentially.  Often it is due to poor client response.  If the client can't process the results as fast as sql server can send them.  This may also be a spill into tempdb, in other words the copy to tempdb disk is waiting. 
LOGMGR
IO bottleneck
WRITELOG
IO bottleneck
PAGEIOLATCH_x
(see below for more) IO bottleneck.  Disk to memory transfers.  Also look at this PerfMon counter:  SQL Server Buffer Manager: Page Life Expectancy (PerfMon Page)
PAGELATCH_X generally used to synchronize access to buffer pages. 
LATCH_x a general lightweight synchronization object.  Generally not related to IO.  Generally not used to synchronize access to buffer pages either (that is PAGELATCH_x).  Most common cause is contention on caches (not the buffer cache).  Watch your use of heaps and text. 
LCK_M_x if dm_os_wait_stats is high for any of these you may have a blocking problem. 
OLEDB can be a variety of things including querying DMV's which are implemented using OLEDB rowset providers.  linked server calls, heavy profiler tracing. 
RESOURCE_SEMAPHORE memory cannot be granted immediately due to high query concurrency.  Look for hash joins.  On DSS this is more common. 
RESOURCE_SEMAPHORE_MUTEX out of threads.  Occurs when synchronozing query compiles with memory grants requests.
RESOURCE_SEMAPHORE_COMPILE excessive compilations, recompilations, or uncachable plans
SLEEP_TASK spill into tempdb.  If it's transient and only occurs once in a while then it could be something else and can probably be ignored.  
THREADPOOL could mean max worker thread config option is too low. 

 

Causes of Page Latch Waits

If the wait times are high the problem is likely a malfunctioning piece of IO hardware.  If the times are low then it is load related.  Other causes are related to pressure on data pages, system allocation tables, etc...ie, hotspots in the database. sys.dm_io_pending_io_requests will provide more information.  Memory pressure also comes into play if too many pages are needed yet the buffer cache is too small.  Look for table scans in this case. 

Regarding system page contention we are concerned with the PFS, GAM, and SGAM. 

  • PFS:  it is always Page 1 and then a multiple of 8088
  • GAM:  Page 2 in each file, then every 511232 pages (~4GB)
  • SGAM:  Page 3 in each file, then every 511232 pages (~4GB)

Further, whenever you see an UP mode (PAGEIOLATCH_UP) you can bet it is for one of these 3 system pages.  These waits show a lack of file parallelism within a file group.  Solution...add more files to the filegroup.  Especially with tempdb!

For contention on other pages use DBCC PAGE to try to identify the object in question.  Causes may be inserts to hotspots, lots of page splits, other things. 

More info on Page latching and tempdb


Blocking is different from waiting in that the wait is not voluntary, it is forced on the waiter by another task.  This occurs when the tasks attempt simultaneous access of a shared resource. 

The DMV dm_os_waiting_tasks shows you a ton of information on waits, locking, and blocking.  

Add new comment