Monitor active sessions and AG sync lag with a SQL Agent job
Monitoring pattern: a SQL Server Agent job records the synchronization lag of Always On databases and the running requests into dedicated tables.
This monitoring pattern uses a SQL Server Agent job that, at each run, records into a dedicated database the synchronization lag of each database in an Always On availability group, then the requests running at that time.
Monitoring tables
USE [MonitorDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activeSessions](
[pid] [int] NULL,
[blocks] [int] NULL,
[server] [nvarchar](255) NULL,
[username] [nvarchar](255) NULL,
[databasename] [nvarchar](255) NULL,
[waittype] [nvarchar](255) NULL,
[waittime] [int] NULL,
[statement] [nvarchar](255) NULL,
[reads] [bigint] NULL,
[writes] [bigint] NULL,
[application] [nvarchar](255) NULL,
[query] [nvarchar](max) NULL,
[duration] [int] NULL,
[executionplan] [xml] NULL,
[timestamp] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Job step script
;WITH UpTime AS
(
SELECT DATEDIFF(SECOND,create_date,GETDATE()) [upTime_secs]
FROM sys.databases
WHERE name = 'tempdb'
),
AG_Stats AS
(
SELECT AR.replica_server_name,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
CAST(DRS.log_send_queue_size AS DECIMAL(19,2)) log_send_queue_size_KB,
(CAST(perf.cntr_value AS DECIMAL(19,2)) / CAST(UpTime.upTime_secs AS DECIMAL(19,2))) / CAST(1024 AS DECIMAL(19,2)) [log_KB_flushed_per_sec]
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
--I am calculating this as an average over the entire time that the instance has been online.
--To capture a smaller, more recent window, you will need to:
--1. Store the counter value.
--2. Wait N seconds.
--3. Recheck counter value.
--4. Divide the difference between the two checks by N.
INNER JOIN sys.dm_os_performance_counters perf ON perf.instance_name = Db_name(DRS.database_id)
AND perf.counter_name like 'Log Bytes Flushed/sec%'
CROSS APPLY UpTime
),
Pri_CommitTime AS
(
SELECT replica_server_name
, DBName
, [log_KB_flushed_per_sec]
FROM AG_Stats
WHERE role_desc = 'PRIMARY'
),
Sec_CommitTime AS
(
SELECT replica_server_name
, DBName
--Send queue will be NULL if secondary is not online and synchronizing
, log_send_queue_size_KB
FROM AG_Stats
WHERE role_desc = 'SECONDARY'
)
INSERT INTO MonitorDb.dbo.activeSync
SELECT p.replica_server_name [primary_replica]
, p.[DBName] AS [database_name]
, s.replica_server_name [secondary_replica]
, CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS BIGINT) [sync_lag_in_secs]
, GETDATE() as [timestamp]
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]
INSERT INTO MonitorDb.dbo.activeSessions
Select s.spid as 'pid'
,s.blocked as 'blocks'
,RTRIM(s.hostname) as 'server'
,RTRIM(s.loginame) as 'username'
,d.name as 'databasename'
,REPLACE(s.lastwaittype,'RESERVED_MEMORY_ALLOCATION_EXT ','RES_MEM_ALLOC_EXT') as 'waittype'
,s.waittime / 1000 as 'waittime' -- wait time in seconds
,s.cmd as 'statement'
,c.num_reads as 'reads' -- number of packet reads
,c.num_writes as 'writes' -- number of packet writes
,s.program_name as 'application'
,t.text as 'query'
,datediff(second,s.last_batch, GETDATE()) as 'duration'
,EQP.query_plan as 'executionplan'
,getdate() as 'timestamp'
-- r.percent_complete
from sys.sysprocesses s
join sys.databases d on d.database_id = s.dbid
left join sys.dm_exec_requests r on r.session_id = s.spid
cross apply sys.dm_exec_sql_text (s.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS EQP
inner join sys.dm_exec_connections c
on c.session_id = s.spid
where s.cmd not like '%AWAITING%' and s.program_name != 'SQLAgent - TSQL JobStep (Job <job_id> : Step 1)'
order by duration desc
Notes
- The lag is estimated by dividing the send queue of the secondary log by the average flush rate of the primary log, calculated over the whole uptime of the instance. For a more recent window, sample the counter twice, as described in the comments of the script.
- The send queue is NULL when the secondary is not online and synchronizing.
- The
activeSynctable is not created in this article. It must contain the columnsprimary_replica,database_name,secondary_replica,sync_lag_in_secsandtimestamp. - The
program_namefilter excludes the job's own step. Replace<job_id>with the job id (columnjob_idofmsdb.dbo.sysjobs), using the program name as it appears insysprocesses, with its padding spaces.