PerformanceCollector.Addin_CaptureDeadlocks.sql 3.88 KB
Newer Older
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 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
IF EXISTS (select 1 from sys.objects where object_id=object_id('PerformanceCollector.CaptureDeadlocks') and type='P')
begin
  drop procedure PerformanceCollector.CaptureDeadlocks  
end
go

set QUOTED_IDENTIFIER on
go

create procedure PerformanceCollector.CaptureDeadlocks 
as
begin
  set nocount on
  set QUOTED_IDENTIFIER on
  declare @guid uniqueidentifier, @ThisSp varchar(128), @now datetime, @nowprev datetime,
  @alert_name varchar(128), @alert_yellow int, @alert_red int, @execution_target_database varchar(128)
  select @guid = newid(),@ThisSp = 'Addin_CaptureDeadlocks', @execution_target_database = '*', @now = GETDATE()

  Declare @retentionRows int, @TotalDeadlockRecordCount int, @recordcount int
  SELECT @nowprev = MAX(CurrTime) from PerformanceCollector.BlockAndWaitStats   
  
  DECLARE @ConfigValueText DATETIME
  SELECT @ConfigValueText = COALESCE(ConfigValueText,'1/1/1980') 
  FROM PerformanceCollector.Config
  WHERE ConfigSetting = 'LastDeadlockRun'   

  --If the event session does not exist then create it.
  IF NOT EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='RingBufferDeadlockTarget')
  BEGIN
    CREATE EVENT SESSION RingBufferDeadlockTarget  --DROP EVENT session RingBufferDeadlockTarget ON SERVER
    ON SERVER 
    ADD EVENT sqlserver.xml_deadlock_report    ,
    ADD EVENT sqlserver.lock_deadlock 
    ( 
      ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username, 
	  sqlserver.session_id, sqlserver.request_id) --, sqlserver.xml_deadlock_report) 
      )
      ADD TARGET package0.ring_buffer    
          (SET max_memory = 4096) 
      WITH (max_dispatch_latency = 1 seconds)
  END

  --If the event session is not running then start it
  IF NOT EXISTS (SELECT 1 
    FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s
    ON t.event_session_address = s.address
    WHERE s.name = 'RingBufferDeadlockTarget' AND t.target_name = 'ring_buffer'
  ) 
  begin
    ALTER EVENT SESSION RingBufferDeadlockTarget ON SERVER
    STATE = start
  end
  
  --run me every 8 hours
  	if datediff (hh,COALESCE(@ConfigValueText,'1/1/1980'),@now) > 8                      
	BEGIN    
		UPDATE PerformanceCollector.Config 
			SET ConfigValueText = convert(varchar(100),@now) 
		WHERE ConfigSetting = 'LastDeadlockRun'
	
  --Get the deadlocks into a temp table
  if OBJECT_ID('tempdb..#tmp') is not null 
    drop table #tmp
  create table #tmp (DeadlockGraph varchar(max))


  begin try
  insert into #tmp (DeadlockGraph)
  select 
  --CAST(     --This REPLACE call is to cover a bug in the trace XML from the event system.
			REPLACE(  --The bug is a mismatched begin/end tag pair.
				REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), 
				'<victim-list>', '<deadlock><victim-list>'),
			'<process-list>','</victim-list><process-list>')
		--as xml) 
		as DeadlockGraph
  FROM
  (select CAST(target_data as xml) as TargetData
   from sys.dm_xe_session_targets st
   join sys.dm_xe_sessions s on s.address = st.event_session_address
   where name = 'RingBufferDeadlockTarget' --system_health
  ) AS Data
  CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
  where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
  end try
  begin catch
    delete from #tmp
    ALTER EVENT SESSION RingBufferDeadlockTarget ON SERVER
    STATE = stop; 
    ALTER EVENT SESSION RingBufferDeadlockTarget ON SERVER
    STATE = start;  
  end catch

  select @recordcount = COUNT(*) from #tmp 
  insert into PerformanceCollector.DeadlockSnapshot (DeadlockGraph, DeadlockGraphCheckSum, ServerName)
  select tmp.DeadlockGraph, 0, @@SERVERNAME from #tmp tmp
  

  --Clear the event session
  ALTER EVENT SESSION RingBufferDeadlockTarget ON SERVER
  STATE = stop ;
  ALTER EVENT SESSION RingBufferDeadlockTarget ON SERVER
  STATE = start ;
  
	END;
end
GO