SQL Server 收集数据库死锁信息

peiyuan1030

USE [azure_monitor];　　--存放读取死锁信息的存储过程的库名
　　
GO
　　
IF OBJECT_ID('monitor_P_deadlock', 'P') IS  NULL
　　EXEC( 'CREATE  procedure dbo.monitor_P_deadlock AS  ');
　　
GO
　　
/*=============================================
　　
-- Author:    jil.wen
　　
-- Create date: 2017/04/11
　　
-- Description:   监控数据库上死锁情况；
　　
-- demo :   exec dbo.monitor_P_deadlock
　　============================================= */
　　
ALTER PROCEDURE monitor_P_deadlock
　　
AS
　　BEGIN
　　-- DELETE  FROM dbo.monitor_deadlock
　　--  WHERE   [capture_day] = CONVERT([VARCHAR](12), GETDATE(), ( 112 ))
　　--          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_buffers
　　
FROM sys.dm_xe_session_targets t
　　INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
　　
WHERE target_name = 'event_file'
　　
--*/
　　IF OBJECT_ID('tempdb..#Events') IS NOT NULL
　　BEGIN
　　DROP TABLE #Events;
　　END;
　　DECLARE @Target_File NVARCHAR(1000) ,
　　@Target_Dir NVARCHAR(1000) ,
　　@Target_File_WildCard NVARCHAR(1000);
　　SELECT  @Target_File = CAST(t.target_data AS XML).value('EventFileTarget[1]/File[1]/@name',
　　'NVARCHAR(256)')
　　FROM    sys.dm_xe_session_targets t
　　INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
　　WHERE   s.name = @SessionName
　　AND t.target_name = '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(50)') ,
　　e.DeadlockID
　　FROM     #Events e
　　CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess')
　　AS Deadlock ( Victims )
　　),
　　DeadlockObjects
　　AS ( SELECT DISTINCT
　　e.DeadlockID ,
　　ObjectName = Deadlock.Resources.value('@objectname',
　　'nvarchar(256)')
　　FROM     #Events e
　　CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*')
　　AS Deadlock ( 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(( SELECT
　　( ', '
　　+ 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
　　WHERE
　　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 IS NOT NULL
　　THEN 1
　　ELSE 0
　　END ,
　　SPID = Deadlock.Process.value('@spid',
　　'int') ,
　　ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]',
　　'varchar(200)') ,
　　LockMode = Deadlock.Process.value('@lockMode',
　　'char(1)') ,
　　Code = Deadlock.Process.value('executionStack[1]/frame[1]',
　　'varchar(1000)') ,
　　ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp',
　　'varchar(100)'),
　　29)
　　WHEN 'SQLAgent - TSQL JobStep (Job '
　　THEN 'SQLAgent Job: '
　　+ ( SELECT
　　name
　　FROM
　　msdb..sysjobs sj
　　WHERE
　　SUBSTRING(Deadlock.Process.value('@clientapp',
　　'varchar(100)'),
　　32, 32) = ( SUBSTRING(sys.fn_varbintohexstr(sj.job_id),
　　3, 100) )
　　) + ' - '
　　+ SUBSTRING(Deadlock.Process.value('@clientapp',
　　'varchar(100)'),
　　67,
　　LEN(Deadlock.Process.value('@clientapp',
　　'varchar(100)'))
　　- 67)
　　ELSE Deadlock.Process.value('@clientapp',
　　'varchar(100)')
　　END ,
　　HostName = Deadlock.Process.value('@hostname',
　　'varchar(20)') ,
　　LoginName = Deadlock.Process.value('@loginname',
　　'varchar(20)') ,
　　InputBuffer = Deadlock.Process.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 = Deadlock.Process.value('@id',
　　'varchar(50)')
　　) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason
　　
ORDER BY                    DeadlockID DESC;
　　END;