SQL Server collects database deadlock information

Background we monitor the database blocking situation in the database blocking timely email alert alert, in order to better maintain the database, especially to enhance the end customer user experience, we should try to avoid the deadlock in the database situation. We know that collecting deadlocks can turn on trace flags such as 1204, and then view deadlock-related information in the log, or use Profiler to track deadlocks, and we want all of the deadlock information to be collected in a table for our post-optimization analysis use, and we can use relatively lightweight, self-contained extended events (system_ Health) to complete this requirement. Test environment for Microsoft SQL Server 2012-11.0.2100.60 (X64)Feb 19:39:15Copyright (c) Microsoft CorporationEnterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200:)Implementation process

A. Create a new table to hold the deadlock

IF db_id (' Azure_monitor ') is a NULL BEGIN CREATE DATABASE azure_monitor; END GO ALTER DATABASE azure_monitor SET RECOVERY simple;    GO use [azure_monitor]; --The library name that holds the deadlock information goif object_id (' Monitor_deadlock ', ' U ') is not NULL DROP TABLE dbo.monitor_deadlock; Gocreate TABLE [dbo].    [Monitor_deadlock] ([ServerName] [VARCHAR] (+), [DataBaseName] [NVARCHAR] (+) NULL, [Deadlockid] [BIGINT] null, [Tra Nsactiontime] [DATETIME] null, [deadlockgraph] [XML] null, [deadlockobjects] [NVARCHAR] (MAX) NULL, [Vict      IM] [int] NOT NULL, [SPID] [int] null, [procedurename] [VARCHAR] (+) NULL, [Lockmode] [CHAR] (1) NULL, [Code] [VARCHAR] (+) null, [ClientApp] [NVARCHAR] (245) NULL, [HostName] [varchar] () NULL, [LoginName] [varchar] (20) NULL, [InputBuffer] [VARCHAR] (+) NULL, [Capture_date] [DATETIME] NOT NULL, [Capture_day] as (CONVERT ([VARCHAR] (+), [capture_date], (112))) , [Comfirm_user] [NVARCHAR] () NULL, [Comfirm_flag] [INT] not NULL) on [PRIMARY] textimage_on [PRIMARY]; Goalter TABLE [dbo]. [Monitor_deadlock] ADD CONSTRAINT [DF__MONITOR_D__CAPTU__2CF2ADDF] DEFAULT (GETDATE ()) for [capture_date]; Goalter TABLE [dbo]. [Monitor_deadlock] ADD CONSTRAINT [df__monitor_d__comfi__2de6d218] DEFAULT ((1)) for [Comfirm_flag]; GO

B. Create a new read deadlock stored procedure

use [Azure_monitor];   --The library name of the stored procedure that holds the read deadlock information goif object_id (' Monitor_p_deadlock ', ' P ') is a NULL EXEC (' CREATE procedure dbo.monitor_p_deadlock as ‘); go/*=============================================--author:jil.wen--Create date:2017/04/11--Description: Deadlock on monitoring database situation;--demo:exec Dbo.monitor_p_deadlock ============================================= */ALTER PROCEDURE Monitor_P_dea Dlockas BEGIN--DELETE from Dbo.monitor_deadlock--WHERE [Capture_day] = CONVERT ([VARCHAR], Getdat        E (), (())--and Comfirm_flag = 1;        DECLARE @SessionName sysname;        DECLARE @Servername VARCHAR (50);        SELECT @Servername = @ @SERVERNAME; SELECT @SessionName = ' system_health ';/*select session_name = S.name, S.blocked_event_fire_time, S.dropped_buffer_  Count, S.dropped_event_count, S.pending_buffersfrom sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s On s.address = T.event_session_addresswhere target_name = ' Event_File '--*/IF object_id (' tempdb.            #Events ') is a not NULL BEGIN DROP TABLE #Events;        END; DECLARE @Target_File NVARCHAR (+), @Target_Dir NVARCHAR (+), @Target_File_WildCard NVARCHAR (10        00);                                                              SELECT @Target_File = CAST (T.target_data as XML). Value (' eventfiletarget[1]/file[1]/@name ', ' NVARCHAR ') from Sys.dm_xe_session_targets t INNER JOIN sys.d M_xe_sessions s on s.address = t.event_session_address WHERE s.name = @SessionName and T.target_na        me = ' event_file ';                                                              SELECT @Target_Dir = Left (@Target_File, LEN (@Target_File)-CHARINDEX (' \ ',        REVERSE (@Target_File)));  SELECT @Target_File_WildCard = @Target_Dir + ' \ ' + @SessionName + ' _*.xel ';--keep this as a separate table Because it ' s Called twice in the next query.        You don ' t want this running twice. SELECT deadlockgraph = CAST (Event_data as XML), Deadlockid = Row_number () over (ORDER by file_name, file                                                _offset) into #Events from Sys.fn_xe_file_target_read_file (@Target_File_WildCard, NULL,        NULL, NULL) as F WHERE event_data like ' <event name= ' xml_deadlock_report% ';                                                              With victims as (SELECT Victimid = Deadlock.Victims.value (' @id ',     ' varchar '), e.deadlockid from #Events e cross APPLY e.deadlockgraph.nodes ('/event/data/value/deadlock/victim-list/vic                  Timprocess ') as Deadlock (victims)), deadlockobjects As (SELECT DISTINCT E.                                                              Deadlockid, ObjectName = Deadlock.Resources.value (' @objectname ',                                ' nvarchar ') from #Events E Cross APPLY e.deadlockgraph.nodes ('/event/data/value/deadlock/resource-list/* ') as Deadl                      Ock (Resources)) INSERT into Monitor_deadlock (ServerName,                      DataBaseName, Deadlockid, Transactiontime,                      Deadlockgraph, deadlockobjects, victim, SPID,                      ProcedureName, Lockmode, Code, ClientApp,                    HostName, LoginName, InputBuffer) SELECT @Servername as Servername, DatabaseName, Deadlockid,                            Transactiontime, Deadlockgraph, Deadlockobjects,                            Victim, SPID, ProcedureName,                            Lockmode, Code, ClientApp,    HostName, LoginName, InputBuffer from                                                              (Select DatabaseName = Left (SUBSTRING (', '                                                              + o.objectname)                                                From Deadlockobjects o              WHERE O.deadlockid = E.deadlockid                                                              ORDER by O.objectname                                                              For XML                                                            PATH (")"), 3, 4000), CHARINDEX ('. ', SUBSTRING                                                              (SELECT (', ')                                                              + O.objectname) from Deadlockobjects o wher                       E                                       O.deadlockid = E.deadlockid                                                              ORDER by O.objectname for                                                              XML PATH (")                                        ), 3, 4000)-1), E.deadlockid,                                                              Transactiontime = Deadlock.Process.value (' @lasttranstarted ',                                        ' DateTime '), Deadlockgraph, Deadlockobjects = SUBSTRING ((SELECT (                                                              ', ' + o.objectname) FROM deadlockobjects o                                                              WHERE O.deadlockid = E.deadlockid                                                              ORDER by O.objectname                                                              For XML                                        PATH (")"), 3, 4000), Victim = case when V.victimid was not NULL TH                                        EN 1 ELSE 0 END,                                                              SPID = Deadlock.Process.value (' @spid ',  ' int '),                                      ProcedureName = Deadlock.Process.value (' executionstack[1]/frame[1]/@procname [1] ', ' varchar '), LOCKMO                                        de = Deadlock.Process.value (' @lockMode ', ' char (1) '),                                                              Code = Deadlock.Process.value (' executionstack[1]/frame[1] ', ' varchar '), ClientApp = case Left (deadlock.pr                                                              Ocess.value (' @clientapp ', ' varchar (100) '), When ' Sqlagent-tsql                                                         JobStep (Job ' then ' SQLAgent job: '  + (SELECT name From msdb:                                                              Sysjobs SJ WHERE                                                              SUBSTRING (Deadlock.Process.value (' @clientapp '), ' varchar ') = (SUBSTRING (sys.fn_varbintohexs                                                             TR (sj.job_id), 3, 100)) ) + '-' + SUBSTRING (deadlock.process.v                                                              Alue (' @clientapp ', ' varchar (100) '),                 67,                                             LEN (' @clientapp ', Deadlock.Process.value                                                      ' varchar (100) '))-67)                                                              ELSE Deadlock.Process.value (' @clientapp ',                                        ' varchar ') END, HostName = Deadlock.Process.value (' @hostname ', ' VA                                                              Rchar '), LoginName = Deadlock.Process.value (' @loginname ', ' varchar '), InputBuffer = DEADLOCK.PR                              Ocess.value (' inputbuf[1] ', ' varchar (1000) ') From #EVents e cross APPLY e.deadlockgraph.nodes ('/event/data/value/deadlock/process-list/ Process ') as Deadlock (process) left JOIN Victims V on v.deadlockid = e.deadlockid and V.victimid = Dea Dlock.                            Process.value (' @id ', ' varchar (50) ')                    ) X--in A subquery to make filtering easier (use column names, not XML parsing), no other reasonorder by    Deadlockid DESC;  END;

C. Call the "monitor_p_deadlock" stored procedure above in Agent new job

Omitted, details can be referred to the database blocking timely Mail Alert alert (next)

D. The results of the collection are as follows

Precautions

• If you need to use agent agents to send alert messages, you should pay attention to the agent is working properly;
• Whether there is a database user right to invoke the above script;
• Whether the database comes with extended event System_health is running properly;

　　

SQL Server collects database deadlock information