1) Execute below query on your Always on Primary replica,
-- 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
2) Below query can be executed in a query window or as a SQL Server agent job in primary replica. Remember to change “Instance_Name=’Alwayson Database Name'” to appropriate Alwayson database name before executing the query,
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
3) While the above query is getting executed from your query window or SQL Server agent job, you can execute the below query to get the latency values,
use [Tempdb] GO select * from Perfcounters order by CapturedTime desc
4) Remember to stop the query running as part of step 2 in this article on query window or as a SQL Agent job when not required.
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.