SQL Server Always On Series Finding Transaction Latency in an Always on Synchronous Replica #alwayson #sqlserver

Create required objects to collect the data
use [Tempdb]
GO
IF OBJECT_ID('Perfcounters') IS NOT NULL
DROP TABLE Perfcounters
GO
CREATE TABLE Perfcounters
(CapturedTime DATETIME NOT NULL,
[Transaction Delay] BIGINT, [Mirrored Write Transactions/sec] BIGINT
)
GO

ALTER TABLE [dbo].Perfcounters
ADD [Transaction Delay in MS]
AS (convert(decimal(5,2),[Transaction Delay]*1.0/[Mirrored Write Transactions/sec]*1.0))
PERSISTED NOT NULL

use [Tempdb]
GO
set nocount on
while (1=1)
begin
insert into Perfcounters
SELECT CURRENT_TIMESTAMP as Time
,pt.* FROM(SELECT RTRIM(object_name) + ' : ' + counter_name CounterName
, cntr_value from sys.dm_os_performance_counters
where Instance_Name='Alwayson Database Name' and counter_name in ('Transaction Delay','Mirrored Write Transactions/sec')) as SourceData
PIVOT
(SUM(cntr_value)
FOR CounterName
IN ([SQLServer:Database Replica : Transaction Delay],[SQLServer:Database Replica : Mirrored Write Transactions/sec])
) pt
waitfor delay '00:00:03' Change this as per your requirement for collection
End

use [Tempdb]
GO
select * from Perfcounters order by CapturedTime desc

http://jbswiki.com/2019/03/05/finding...

HADR_SYNC_COMMIT indicates the time between when a transaction ready to commit in the primary replica, and all secondary synchronouscommit replicas have acknowledged the hardening of the transaction commit LSN in an AG. It means a transaction in the primary replica cannot be committed, until the primary replica received greater hardened LSNs from all secondary synchronouscommit replicas. If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one PrimarySecondary replica data movement flow, or at least one secondary replica is slow in log hardening.

Reference : https://techcommunity.microsoft.com/t...