Si vous êtes fréquemment confrontés au verrous bloquants pendant de longues durée, voici un script qui identifie la session en tête de la chaîne de blocage (si le blocage dure depuis plus d’un certain délai, variable et à configurer) et fait un KILL de sa connexion.
Ce n’est en rien une vraie solution au problème, qui est très probablement d’ordre applicatif en raison de transactions trop longues ou jamais clôturées : c’est là qu’il faudra chercher et solutionner.
En attendant, très temporairement, cette solution vous permettra peut-être de limiter une situation de crise et vous aidera à identifier la source du problème : les éléments du processus bloquant sont enregistrés dans une table de journalisation.
Ce script peut-être planifié dans un job SQL Agent, planifié toutes les 30 secondes par exemple, ou encore déclenché par une alerte sur condition de performance (sur un des compteurs d’attente de verrouillage).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
/*------------------------------------------------------------------- [SCRIPT] locks : automatic kill lead blocker [DATABASE] master [DESCRIPTION] use it as SQL Agent Job [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20190122 -------------------------------------------------------------------*/ /* Table de log, à créer dans msdb USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[KillBlockingLog]( [Id] INT IDENTITY PRIMARY KEY NOT NULL, [EventTime] [DATETIME2](7) NOT NULL, [session_id] [SMALLINT] NULL, [lead_blocker_id] [SMALLINT] NULL, [host_name] [NVARCHAR](128) NULL, [KillCmd] [VARCHAR](22) NULL, [Blocked_SQL] [NVARCHAR](MAX) NULL, [Blocking_SQL] [NVARCHAR](MAX) NULL, [wait_duration_ms] [BIGINT] NULL, [wait_type] [NVARCHAR](60) NULL, [dbname] [NVARCHAR](128) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO */ SET NOCOUNT ON IF OBJECT_ID('tempdb.dbo.#Blocking') IS NOT NULL DROP TABLE #Blocking GO DECLARE @killcmd NVARCHAR(100) -- Durée de l'attente plus longue la nuit : à configurer DECLARE @delay INT = CASE WHEN DATEPART(HOUR,GETDATE()) > 21 OR DATEPART(HOUR,GETDATE()) < 6 THEN 1200000 ELSE 60000 END SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH T0 AS ( SELECT session_id, session_id as current_session_id, blocking_session_id, 1 as level, wait_duration_ms , wait_type FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL AND blocking_session_id <> session_id --> pas d'intrablocage (pour éliminer les CXPACKET par exemple) AND wait_duration_ms > @delay --> plus de 60 secondes (minimum 5 secondes afin que les deadlocks n'intérefèrent pas) UNION ALL SELECT T0.session_id, T.session_id, T.blocking_session_id, level + 1, T0.wait_duration_ms , T0.wait_type FROM T0 JOIN sys.dm_os_waiting_tasks AS T ON T0.blocking_session_id = T.session_id ) , T1 AS ( SELECT level, T0.session_id, T0.blocking_session_id, HC.text AS Blocked_SQL, HB.text AS Blocking_SQL, T0.wait_duration_ms , T0.wait_type, ROW_NUMBER() OVER(PARTITION BY T0.blocking_session_id ORDER BY level DESC) AS N FROM T0 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS T ON T0.blocking_session_id = T.session_id JOIN sys.dm_exec_connections AS Cs ON T0.session_id = Cs.session_id CROSS APPLY sys.dm_exec_sql_text(Cs.most_recent_sql_handle) AS HC JOIN sys.dm_exec_connections AS Bs ON T0.blocking_session_id = Bs.session_id CROSS APPLY sys.dm_exec_sql_text(Bs.most_recent_sql_handle) AS HB WHERE T.session_id IS NULL ) SELECT TOP (1) SYSDATETIME() AS EventTime,T1.session_id, blocking_session_id as lead_blocker_id, ss.host_name, level,'KILL ' + CAST(blocking_session_id AS VARCHAR(16)) +';' AS KillCmd, Blocked_SQL, Blocking_SQL, wait_duration_ms , wait_type, DB_NAME(ss.database_id) AS dbname INTO #Blocking FROM T1 JOIN sys.dm_exec_sessions ss ON T1.blocking_session_id = ss.session_id WHERE N = 1 OPTION (MAXRECURSION 10000) -- Kill SELECT TOP (1) @killcmd=KillCmd FROM #Blocking; PRINT @killcmd; EXEC (@killcmd); ---- Log INSERT msdb.dbo.KillBlockingLog SELECT EventTime, session_id, lead_blocker_id, host_name, KillCmd, Blocked_SQL, Blocking_SQL, wait_duration_ms, wait_type, dbname FROM #Blocking |