Information About Waits Stats in SQL Server Waits – Part 1

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Backup, Network and SQL Server Audit related waits.

ABR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

ASSEMBLY_LOAD
Occurs during exclusive access to assembly loading.

ASYNC_DISKPOOL_LOCK
Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.

ASYNC_IO_COMPLETION
Occurs when a task is waiting for I/Os to finish.

ASYNC_NETWORK_IO
Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

AUDIT_GROUPCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.

AUDIT_LOGINCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.

AUDIT_ON_DEMAND_TARGET_LOCK
Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.

AUDIT_XE_SESSION_MGR
Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.

BACKUP
Occurs when a task is blocked as part of backup processing.

BACKUP_OPERATOR
Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.

BACKUPBUFFER
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPIO
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPTHREAD
Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.

PARALLEL_BACKUP_QUEUE
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

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