LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon