{"id":107414,"date":"2025-07-25T12:00:00","date_gmt":"2025-07-25T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107414"},"modified":"2026-03-09T12:40:02","modified_gmt":"2026-03-09T12:40:02","slug":"measuring-replication-latency-in-sql-server-synchronous-availability-groups","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/measuring-replication-latency-in-sql-server-synchronous-availability-groups\/","title":{"rendered":"Measure Replication Latency in SQL Server AlwaysOn AGs"},"content":{"rendered":"\n

Synchronous replicas in SQL Server Availability Groups<\/a> promise no data loss, but they don\u2019t promise zero delay; under heavy load they can still fall behind. To measure replication latency between synchronous replicas in SQL Server AlwaysOn Availability Groups, monitor two performance counters: Mirrored Write Transactions\/sec and Transaction Delay. <\/p>\n\n\n\n

These counters reveal how much time each transaction spends waiting for the secondary to acknowledge receipt – even in synchronous mode, latency accumulates under heavy I\/O workloads like index rebuilds or bulk data loads. This article shows how to capture these counters over time, calculate effective replication delay, and use the data to schedule maintenance operations safely.<\/p>\n\n\n\n

Why Measure Latency?<\/h2>\n\n\n\n

Availability Groups are useful for creating Highly Available (HA) database systems, but that doesn\u2019t mean they are entirely immune to performance problems. In busy systems, limitations in the infrastructure can introduce replication lag that is severe enough to affect database performance in ways that aren\u2019t immediately obvious.<\/p>\n\n\n\n

In SQL Server Availability Groups<\/a><\/strong>, the relevant difference between synchronous<\/strong> and asynchronous<\/strong> replicas comes down to how and when<\/em> a transaction on the primary server is considered “committed”: Although it is easy to measure the lag between asynchronous replicas by using the dashboard in SSMS<\/a> or by querying the DMVs (Dynamic Management Views)<\/a>, it takes more work to find the latency between synchronous replicas. This article explains how to measure that latency using internal performance counters, offering a simple technique for monitoring the cost of synchronous replication.<\/p>\n\n\n\n

The Effect of Stress on a Synchronous Replica<\/h2>\n\n\n\n

You might think that there would be no delay in committing the transaction on the primary only after it is safely on the secondary – after all it is supposed to be synchronous. However, if you work with a busy server using AlwaysOn including synchronous replicas, you may have seen high numbers of HADR_SYNC_COMMIT<\/a> waits, especially during IO-intensive operations such as index rebuilds or data loads. This type of wait means \u2018Your synchronous replica can\u2019t keep up!<\/em>\u2019 <\/p>\n\n\n\n

By understanding why this happens, we can learn about the nature of the bottlenecks in our infrastructure and throttle or reschedule operations to reduce the risk of replication latency.<\/p>\n\n\n\n

Read also:<\/strong> In-memory optimized tables for high-throughput workloads<\/a><\/p>\n\n\n\n

Understanding the Counters<\/h2>\n\n\n\n

Our first (but not last!) step in understanding counters should be the documentation, so let\u2019s take a look at https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/performance-monitor\/sql-server-database-replica<\/a>.<\/p>\n\n\n\n

The two metrics that we will use are Mirrored Write Transaction\/sec and Transaction Delay. Let\u2019s see what the documents say:<\/p>\n\n\n\n

\"Definition<\/figure>\n\n\n\n
\"Transaction<\/figure>\n\n\n\n

Read also:<\/strong> Temporary tables and tempdb performance in SQL Server<\/a><\/p>\n\n\n\n

Mirrored Write Transaction\/sec<\/h2>\n\n\n\n

With these metrics, we can start by seeing what Mirrored Write Transaction\/sec looks like on a system with no traffic. For this I will use the sys.dm_os_performance_counters<\/a> dynamic management view (DMV) that lets you see the OS performance counters that SQL Server is presenting to the OS performance monitor.<\/p>\n\n\n\n

SELECT\n\tinstance_name\n\t,CAST(cntr_value AS BIGINT) counter_value\n\t,counter_name\nFROM\n\tsys.dm_os_performance_counters perf\nWHERE object_name LIKE 'SQLServer:Database Replica%'\n\tAND perf.counter_name LIKE 'Mirrored Write Transactions\/sec%'\n\tAND instance_name = '_Total';\n<\/pre><\/div>\n\n\n\n
When you execute this statement, it returns:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If the documentation is to be taken literally, this means that our offline system had 106,512,300 transactions in the last second \u2013 quite the load if you believe that number! If we wait about a minute and execute the query again on our system, the result shows a very similar number \u2013 106,512,310. Too similar if you ask me! This smells like a tally rather than a metric: maybe a tally of how much time the server has spent waiting on synchronous replicas. We need to investigate.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
By running this query several times, it becomes apparent that the counter actually tracks a cumulative number of transactions since the last reboot or counter reset, rather than reflecting activity within the last second.<\/p>\n\n\n\n
With this understanding, we can rewrite the Microsoft definition:<\/p>\n\n\n\n
Mirrored Write Transaction\/sec:<\/strong><\/em> Number of transactions that were written to the primary database and then waited to commit until the log was sent to the secondary database, since the last reboot.<\/em><\/p>\n\n\n\n
Transaction Delay<\/h2>\n\n\n\n
Thankfully, Transaction Delay does exactly what it says on the tin. The tin also helpfully notes that we can divide this value by the Mirrored Write Transactions\/sec counter to get an average delay per transaction.<\/p>\n\n\n\n
Dividing these counters will give us the average latency since the last reboot but this, by itself, is not particularly useful in this context. We are more likely to need to know how closely the latency relates to various types of server load.<\/p>\n\n\n\n
Despite the \/sec suffix, most of these counters are actually just cumulative tallies. You have to do your own math if you want the actual per-second rate.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Storing the Counters<\/h2>\n\n\n\n
If we store these values over time, we can then calculate a minute-by-minute metric that is far more useful for understanding the speed and robustness of a system. To do this we will create a table to track these counters and a stored procedure to write to that table at a regular cadence.<\/p>\n\n\n\n
Let\u2019s start by creating a table to hold our observations:<\/p>\n\n\n\n
IF (SELECT object_id('dbo.AGLagObservations')) IS NULL\nCREATE TABLE dbo.AGLagObservations\n(\n    id            INT          IDENTITY(1,1)\n    ,instance_name SYSNAME\n    ,trancount     BIGINT\n    ,totaldelayMS  BIGINT\n    ,ts            DATETIME2(7) DEFAULT (getdate())\n)\n<\/pre><\/div>\n\n\n\n
Since these counters are stored in the same column in sys.dm_os_performance_counters, our procedure will use a temp table to pivot them into separate columns, making further analysis easier.<\/p>\n\n\n\n
CREATE OR ALTER PROC dbo.CreateAGLagObservation\nAS\nBEGIN\n\tCREATE TABLE #t\n\t(\n\t\tinstance_name SYSNAME\n\t\t,counter_value BIGINT\n\t\t,counter_name  SYSNAME\n\t)\n--Fetch all the counters we want into one temp table\n\tINSERT INTO #t\n\t\t(instance_name,counter_value,counter_name)\n\tSELECT\n\t\tinstance_name\n\t\t,CAST(cntr_value AS BIGINT) counter_value\n\t\t,counter_name\n\tFROM\tsys.dm_os_performance_counters perf\n\tWHERE object_name LIKE 'SQLServer:Database Replica%'\n\tAND (perf.counter_name \n            LIKE 'Mirrored Write Transactions\/sec%'\n\tOR perf.counter_name LIKE 'Transaction Delay%')\n\n\t--Join the temp table to itself to flatten the two \n\t--counters into one row\n\tINSERT INTO dbo.AGLagObservations\n\t\t(instance_name, trancount,totaldelayMS)\n\tSELECT\n\t\tt1.instance_name\n\t\t,t1.counter_value \n\t\t,t2.counter_value\n\tFROM\t#t t1 --'Mirrored Write Transactions\/sec%'\n\tJOIN #t t2 --'Transaction Delay%'\nON t1.instance_name = t2.instance_name\n\tAND t1.counter_name \n               LIKE 'Mirrored Write Transactions\/sec%'\n\tAND t2.counter_name LIKE 'Transaction Delay%';\nEND\nGO\n<\/pre><\/div>\n\n\n\n
Now we can start a loop to gather our data. This code can be placed in a SQL Agent Job<\/a> or just kicked off from SSMS:<\/p>\n\n\n\n
DECLARE @stop_time_local DATETIME2(7) = GETDATE() + 1\n\t, @time_delay VARCHAR(10) = '00:01:00'\n\t, @now DATETIME2(7) = GETDATE()\n\nWHILE @now < @stop_time_local\nBEGIN\n\tEXEC dbo.CreateAGLagObservation\n\tSELECT @now = GETDATE()\n\tWAITFOR DELAY @TIME_DELAY\nEND<\/pre><\/div>\n\n\n\n
You could also just use a job that runs every minute or so to execute: EXEC dbo.CreateAGLagObservation<\/em>, depending on your infrastructure.<\/p>\n\n\n\n