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
- Le retard est estimé en divisant la file d'envoi du log secondaire par le débit moyen de flush du log primaire, calculé sur toute la durée de fonctionnement de l'instance. Pour une fenêtre plus récente, il faut échantillonner le compteur deux fois, comme indiqué dans les commentaires du script.
- La file d'envoi est NULL si le secondaire n'est pas en ligne et synchronisé.
- La table
activeSyncn'est pas créée dans cet article. Elle doit contenir les colonnesprimary_replica,database_name,secondary_replica,sync_lag_in_secsettimestamp. - Le filtre
program_nameexclut l'étape du job elle-même. Remplacer<job_id>par l'identifiant du job (colonnejob_iddemsdb.dbo.sysjobs), en reprenant le nom de programme tel qu'il apparaît danssysprocesses, avec ses espaces de remplissage.