LLI Wiki — My Digital Knowledge Base

Superviser les sessions actives et le retard de synchronisation AG

Motif de supervision : un job SQL Server Agent enregistre le retard de synchronisation des bases d'un groupe Always On et les requêtes en cours dans des tables dédiées.

Ce motif de supervision utilise un job SQL Server Agent qui, à chaque exécution, enregistre dans une base dédiée le retard de synchronisation de chaque base d'un groupe de disponibilité Always On, puis les requêtes en cours d'exécution.

Tables de supervision

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

Script du job

;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

Remarques

Mastodon LLIMastodon SocialFosstodon