Information About Waits Stats in SQL Server ALWAYSON or HADRON Waits

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 ALWAYSON related waits or HADRON waits.

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

HADR_AG_MUTEX
Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the configuration of an availability group.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_AR_CRITICAL_SECTION_ENTRY
Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the runtime state of the local replica of the associated availability group.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_AR_MANAGER_MUTEX
Occurs when an availability replica shutdown is waiting for startup to complete or an availability replica startup is waiting for shutdown to complete. Internal use only.

Note – Availability replica shutdown is initiated either by SQL Server shutdown or by SQL Server handling the loss of quorum by the Windows Server Failover Clustering node. Availability replica startup is initiated either by SQL Server startup or by SQL Server recovering from the loss of quorum by the Windows Server Failover Clustering node.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_BACKUP_BULK_LOCK
The AlwaysOn primary database received a backup request from a secondary database and is waiting for the background thread to finish processing the request on acquiring or releasing the BulkOp lock.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_BACKUP_QUEUE
The backup background thread of the AlwaysOn primary database is waiting for a new work request from the secondary database. (typically, this occurs when the primary database is holding the BulkOp log and is waiting for the secondary database to indicate that the primary database can release the lock).
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_CLUSAPI_CALL
A SQL Server thread is waiting to switch from non-preemptive mode (scheduled by SQL Server) to preemptive mode (scheduled by the operating system) in order to invoke Windows Server Failover Clustering APIs.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_COMPRESSED_CACHE_SYNC
Waiting for access to the cache of compressed log blocks that is used to avoid redundant compression of the log blocks sent to multiple secondary databases.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_FLOW_CONTROL
Waiting for messages to be sent to the partner when the maximum number of queued messages has been reached. Indicates that the log scans are running faster than the network sends. This is an issue only if network sends are slower than expected.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_VERSIONING_STATE
Occurs on the versioning state change of an AlwaysOn secondary database. This wait is for internal data structures and is usually is very short with no direct effect on data access.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_WAIT_FOR_RESTART
Waiting for the database to restart under AlwaysOn Availability Groups control. Under normal conditions, this is not a customer issue because waits are expected here.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
A query on object(s) in a readable secondary database of an AlwaysOn availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_COMMAND
Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_OP_COMPLETION_SYNC
Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_OP_START_SYNC
An AlwaysOn DDL statement or a Windows Server Failover Clustering command is waiting for serialized access to an availability database and its runtime state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBR_SUBSCRIBER
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the runtime state of an event subscriber that corresponds to an availability database. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBR_SUBSCRIBER_FILTER_LIST
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBSTATECHANGE_SYNC
Concurrency control wait for updating the internal state of the database replica.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_BLOCK_FLUSH
The FILESTREAM AlwaysOn transport manager is waiting until processing of a log block is finished.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_FILE_CLOSE
The FILESTREAM AlwaysOn transport manager is waiting until the next FILESTREAM file gets processed and its handle gets closed.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_FILE_REQUEST
An AlwaysOn secondary replica is waiting for the primary replica to send all requested FILESTREAM files during UNDO.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_IOMGR
The FILESTREAM AlwaysOn transport manager is waiting for R/W lock that protects the FILESTREAM AlwaysOn I/O manager during startup or shutdown.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_IOMGR_IOCOMPLETION
The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_MANAGER
The FILESTREAM AlwaysOn transport manager is waiting for the R/W lock that protects the FILESTREAM AlwaysOn transport manager during startup or shutdown.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_GROUP_COMMIT
Transaction commit processing is waiting to allow a group commit so that multiple commit log records can be put into a single log block. This wait is an expected condition that optimizes the log I/O, capture, and send operations.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGCAPTURE_SYNC
Concurrency control around the log capture or apply object when creating or destroying scans. This is an expected wait when partners change state or connection status.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGCAPTURE_WAIT
Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGPROGRESS_SYNC
Concurrency control wait when updating the log progress status of database replicas.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_DEQUEUE
A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
The AlwaysOn availability replica manager is waiting for serialized access to the runtime state of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_STARTUP_SYNC
A background task is waiting for the completion of the startup of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
A background task is waiting for the termination of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_PARTNER_SYNC
Concurrency control wait on the partner list.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_READ_ALL_NETWORKS
Waiting to get read or write access to the list of WSFC networks. Internal use only.
Note
The engine keeps a list of WSFC networks that is used in dynamic management views (such as sys.dm_hadr_cluster_networks) or to validate AlwaysOn Transact-SQL statements that reference WSFC network information. This list is updated upon engine startup, WSFC related notifications, and internal AlwaysOn restart (for example, losing and regaining of WSFC quorum). Tasks will usually be blocked when an update in that list is in progress.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_RECOVERY_WAIT_FOR_CONNECTION
Waiting for the secondary database to connect to the primary database before running recovery. This is an expected wait, which can lengthen if the connection to the primary is slow to establish.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_RECOVERY_WAIT_FOR_UNDO
Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database. This is an expected wait after failovers.Undo progress can be tracked through the Windows System Monitor (perfmon.exe) and dynamic management views.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_REPLICAINFO_SYNC
Waiting for concurrency control to update the current replica state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_SYNC_COMMIT
Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_SYNCHRONIZING_THROTTLE
Waiting for transaction commit processing to allow a synchronizing secondary database to catch up to the primary end of log in order to transition to the synchronized state. This is an expected wait when a secondary database is catching up.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TDS_LISTENER_SYNC
Either the internal AlwaysOn system or the WSFC cluster will request that listeners are started or stopped. The processing of this request is always asynchronous, and there is a mechanism to remove redundant requests. There are also moments that this process is suspended because of configuration changes. All waits related with this listener synchronization mechanism use this wait type. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TDS_LISTENER_SYNC_PROCESSING
Used at the end of an AlwaysOn Transact-SQL statement that requires starting and/or stopping an availability group listener. Since the start/stop operation is done asynchronously, the user thread will block using this wait type until the situation of the listener is known.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TIMER_TASK
Waiting to get the lock on the timer task object and is also used for the actual waits between times that work is being performed. For example, for a task that runs every 10 seconds, after one execution, AlwaysOn Availability Groups waits about 10 seconds to reschedule the task, and the wait is included here.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_DBRLIST
Waiting for access to the transport layer’s database replica list. Used for the spinlock that grants access to it.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_FLOW_CONTROL
Waiting when the number of outstanding unacknowledged AlwaysOn messages is over the out flow control threshold. This is on an availability replica-to-replica basis (not on a database-to-database basis).
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_SESSION
AlwaysOn Availability Groups is waiting while changing or accessing the underlying transport state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_WORK_POOL
Concurrency control wait on the AlwaysOn Availability Groups background work task object.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_WORK_QUEUE
AlwaysOn Availability Groups background worker thread waiting for new work to be assigned. This is an expected wait when there are ready workers waiting for new work, which is the normal state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_XRF_STACK_ACCESS
Accessing (look up, add, and delete) the extended recovery fork stack for an AlwaysOn availability database.
Applies to: SQL Server 2012 through SQL Server 2014.

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 *