HADR_AR_UNLOAD_COMPLETED SQL Server Wait Type

Wait Type HADR_AR_UNLOAD_COMPLETED

The wait type HADR_AR_UNLOAD_COMPLETED is ranked #278 by Stedman Solutions and Database Health Monitor.

Wait statistics, in the context of SQL Server, refer to the amount of time that a query spends waiting to access data in the database. When a client application requests data from the database, the request is placed in a queue and the client application must wait for its turn to access the data. The time that the query spends waiting is called a "wait" and is tracked by SQL Server. This information can be used to identify potential performance bottlenecks and optimize the performance of the database. Wait statistics are commonly used by database administrators to diagnose and troubleshoot performance issues in SQL Server.


Managing SQL Server in high availability environments involves monitoring a variety of wait types to ensure optimal performance. One such wait type, specific to Always On Availability Groups, is HADR_AR_UNLOAD_COMPLETED. Understanding this wait type can help you identify and address potential issues in your high availability setup.

What is HADR_AR_UNLOAD_COMPLETED?

The HADR_AR_UNLOAD_COMPLETED wait type occurs when SQL Server is waiting for an Always On Availability Groups availability replica to complete the unload process. This happens when a replica is being removed or when a failover process is finalizing.

In simpler terms, this wait type signals that SQL Server is cleaning up resources and tasks related to a specific availability replica. It’s part of the process of maintaining consistency and stability during changes in the Always On environment.

Why Does HADR_AR_UNLOAD_COMPLETED Happen?

Several scenarios can lead to HADR_AR_UNLOAD_COMPLETED waits, including:

  • Failover events in Always On Availability Groups, especially if they are frequent.
  • Manual or automatic removal of replicas from an availability group.
  • Resource contention or delays on the server handling the replica unload process.
  • High workloads during the transition, causing slow cleanup operations.

Short-lived occurrences of this wait type are normal during replica transitions. However, prolonged waits can indicate potential issues that need to be addressed.

How to Monitor HADR_AR_UNLOAD_COMPLETED Waits

Tracking HADR_AR_UNLOAD_COMPLETED waits and their frequency is critical for managing Always On Availability Groups effectively. The Database Health Monitor is an excellent tool for monitoring such wait types. Its Historic Waits Monitoring feature allows you to analyze wait trends over time, helping you spot patterns and correlate them with other system events.

With Database Health Monitor, you can easily see if high HADR_AR_UNLOAD_COMPLETED waits align with failovers, resource contention, or other activities, providing the insights needed to fine-tune your environment.

What Can You Do About HADR_AR_UNLOAD_COMPLETED Waits?

If you experience frequent or extended HADR_AR_UNLOAD_COMPLETED waits, consider the following actions:

  • Review your failover policies and configuration to reduce unnecessary transitions.
  • Optimize workloads during expected transitions to minimize resource contention.
  • Check the health and performance of your availability replicas to ensure they can handle transition tasks efficiently.
  • Evaluate system resources such as CPU, memory, and disk I/O, especially on the server responsible for the unload process.
  • Monitor and improve network reliability between replicas, as communication issues can delay transitions.

By addressing these factors, you can reduce HADR_AR_UNLOAD_COMPLETED waits and improve the overall performance and reliability of your Always On Availability Groups.

How Stedman Solutions Can Help

At Stedman Solutions, we specialize in SQL Server performance tuning and troubleshooting, including resolving issues with wait types like HADR_AR_UNLOAD_COMPLETED. Our managed services offer proactive monitoring and optimization to keep your Always On Availability Groups running smoothly. Additionally, our Database Health Monitor provides powerful tools to track and analyze SQL Server waits, ensuring your database environment performs at its best.

If you’re experiencing performance challenges or wait-related issues, contact us today. Let us help you maintain a reliable, high-performing SQL Server environment!


Watch on YouTube


Find out more about our SQL Server Managed Services

Applies to

    Related Waits

    HADR_AG_MUTEX
    HADR_AR_CRITICAL_SECTION_ENTRY
    HADR_AR_MANAGER_MUTEX
    HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
    HADR_BACKUP_BULK_LOCK
    HADR_BACKUP_QUEUE
    HADR_CLUSAPI_CALL
    HADR_COMPRESSED_CACHE_SYNC
    HADR_CONNECTIVITY_INFO
    HADR_DATABASE_FLOW_CONTROL
    HADR_DATABASE_VERSIONING_STATE
    HADR_DATABASE_WAIT_FOR_RESTART
    HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
    HADR_DB_COMMAND
    HADR_DB_OP_COMPLETION_SYNC
    HADR_DB_OP_START_SYNC
    HADR_DBR_SUBSCRIBER
    HADR_DBR_SUBSCRIBER_FILTER_LIST
    HADR_DBSEEDING
    HADR_DBSEEDING_LIST
    HADR_DBSTATECHANGE_SYNC
    HADR_FABRIC_CALLBACK
    HADR_FILESTREAM_BLOCK_FLUSH
    HADR_FILESTREAM_FILE_CLOSE
    HADR_FILESTREAM_FILE_REQUEST
    HADR_FILESTREAM_IOMGR
    HADR_FILESTREAM_MANAGER
    HADR_GROUP_COMMIT
    HADR_LOGCAPTURE_SYNC
    HADR_LOGCAPTURE_WAIT
    HADR_LOGPROGRESS_SYNC
    HADR_NOTIFICATION_DEQUEUE
    HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
    HADR_NOTIFICATION_WORKER_STARTUP_SYNC
    HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
    HADR_PARTNER_SYNC
    HADR_READ_ALL_NETWORKS
    HADR_RECOVERY_WAIT_FOR_CONNECTION
    HADR_RECOVERY_WAIT_FOR_UNDO
    HADR_REPLICAINFO_SYNC
    HADR_SYNC_COMMIT
    HADR_SYNCHRONIZING_THROTTLE
    HADR_TDS_LISTENER_SYNC
    HADR_TDS_LISTENER_SYNC_PROCESSING
    HADR_TIMER_TASK
    HADR_TRANSPORT_DBRLIST
    HADR_TRANSPORT_FLOW_CONTROL
    HADR_TRANSPORT_SESSION
    HADR_WORK_POOL
    HADR_WORK_QUEUE
    HADR_XRF_STACK_ACCESS

    See Also


    All Wait Types
    HADR_AR_UNLOAD_COMPLETED SQL Server Wait Type