Home » statistics in sql server

statistics in sql server

The SQL Server Wait Types with their descriptions

  • by

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.

Read More »The SQL Server Wait Types with their descriptions