Checking Availability Group synchronization performance

One of the most important aspects of a successful deployment of SQL Server Availability Group is the synchronization speed of the secondary replica with the primary replica fulfills your production application performance requirement. Your application will be slowed down if a secondary replica with synchronous commit mode is lagging behind the primary replica. Also if any one of the secondary replicas far behind from the primary replica, transaction log reuse will be hindered, which makes the transaction log file keeps growing until disk full and the database becomes non-updatable. You can check the transaction log reuse wait by querying the sys.databases DMV's log_reuse_wait column.

The easiest way to check the status of AG is through the built-in dashboard in SSMS, you can open it by expanding AlwaysOn High Availability folder in the SSMS Object Explorer > Availability Groups > right-click the AG group > Show Dashboard. The default layout of the dashboard doesn't provide a lot of details, but you can add additional details into the layout through the Add/Remove Columns link on the dashboard, as shown below:

The description of these columns in the dashboard can be checked in the online documentation sys.dm_hadr_database_replica_states DMV. Below are some useful columns that I always add:

• log_send_queue_size : Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).
• log_send_rate : Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.
• redo_queue_size: Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).
• redo_rate : Average Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.