How to Check the Deadlocks

 1
 2Create Database [Deadlockdemo]
 3go
 4use [Deadlockdemo];
 5go
 6CREATE TABLE dbo.invoices_demo (
 7   id int NOT NULL,
 8   num nvarchar(20) NOT NULL,
 9   customer nvarchar(100) NOT NULL,
10   created_at DateTime NOT NULL,
11   updated_at DateTime NOT NULL,
12   CONSTRAINT PK_invoices PRIMARY KEY (id)
13);
14  
15CREATE TABLE dbo.invoice_items_demo (
16   invoice_id int NOT NULL,
17   item_index int NOT NULL,
18   product nvarchar(100) NOT NULL,
19   qty int NOT NULL,
20   price money NOT NULL,
21   CONSTRAINT PK_invoice_items PRIMARY KEY (invoice_id, item_index)
22);
23  
24INSERT INTO dbo.invoices_demo (id, num, customer, created_at, updated_at) VALUES
25(1, 'INV180125', 'NASA', '2018-01-25', '2018-01-25'),
26(2, 'INV180128', 'SpaceX', '2018-01-28', '2018-01-28');
27INSERT INTO invoice_items_demo (invoice_id, item_index, product, qty, price) VALUES
28(1, 1, 'NK-33 engine', 7, 145000),
29(1, 2, 'Rocketdyne RS-25', 2, 560000),
30(2, 1, 'Merlin 1D engine', 7, 125000),
31(2, 2, 'J58 engine', 3, 225000);
32GO
33
34-- Database Configuration
35DBCC TRACEON (1204, 1222)
36DBCC TRACESTATUS (1204, 1222)
37EXEC sp_altermessage 1205, 'WITH_LOG', 'true'
38GO
39
40-- Extended Event Implementation
41CREATE EVENT SESSION [blocked_process] ON SERVER
42ADD EVENT sqlserver.blocked_process_report(
43    ACTION(sqlserver.client_app_name,
44           sqlserver.client_hostname,
45           sqlserver.database_name)) ,
46ADD EVENT sqlserver.xml_deadlock_report (
47    ACTION(sqlserver.client_app_name,
48           sqlserver.client_hostname,
49           sqlserver.database_name,sqlserver.sql_text))
50ADD TARGET package0.asynchronous_file_target
51(SET filename = N'C:\temp\blocked_process.xel',    -- maybe, you need to change the path location
52     metadatafile = N'C:\temp\blocked_process.xem',
53     max_file_size=(65536),
54     max_rollover_files=5)
55WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
56GO 
57
58-- Enable the Blocked Process Threshold
59EXEC sp_configure 'show advanced options', 1 ;
60GO
61RECONFIGURE ;
62GO
63EXEC sp_configure 'blocked process threshold', '5';
64RECONFIGURE
65GO
66
67-- Enable the Extended Event
68ALTER EVENT SESSION [blocked_process] ON SERVER
69STATE = START;
70GO

 1
 2-- Create table on MSDB Database
 3USE MSDB
 4GO
 5SET ANSI_NULLS ON
 6GO
 7SET QUOTED_IDENTIFIER ON
 8GO
 9CREATE TABLE [dbo].[Blocking](
10    [ID] [bigint] IDENTITY(1,1) NOT NULL,
11    [ProcessWaiting] [varchar](1000) NULL,
12    [ProcessBlocking] [varchar](1000) NULL,
13    [WaitingQuery] [nvarchar](4000) NULL,
14    [blockingQuery] [nvarchar](4000) NULL,
15    [astatus] [varchar](1000) NULL,
16    [bstatus] [varchar](1000) NULL,
17    [waittime] [varchar](1000) NULL,
18    [killbill] [varchar](1000) NULL,
19    [Instance] [varchar](1000) NULL,
20    [DBName] [varchar](1000) NULL,
21    [Btime] [datetime] NULL
22) ON [PRIMARY]
23GO
24 
25ALTER TABLE [dbo].[Blocking] ADD  CONSTRAINT [DF_Blocking_Btime]  DEFAULT (getdate()) FOR [Btime]
26GO

 1SELECT a.spid ProcessWaiting
 2	,a.blocked ProcessBlocking
 3	,sptxt.TEXT WaitingQuery
 4	,blktxt.TEXT blockingQuery
 5	,a.STATUS
 6	,b.STATUS
 7	,a.waittime
 8	,convert(varchar,dateadd(ms,a.waittime,0),114) as [waittime hms]
 9	,@@SERVERNAME AS ServerName
10	,db_name(a.dbid) AS DBName
11	,'KILL ' + cast(a.blocked AS VARCHAR) KILLBill
12FROM sys.sysprocesses a
13CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) sptxt
14JOIN sys.sysprocesses b ON a.blocked = b.spid
15CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) blktxt
16WHERE a.blocked <> 0
17ORDER BY a.spid

 1
 2USE [msdb]
 3GO
 4BEGIN TRANSACTION
 5DECLARE @ReturnCode INT
 6SELECT @ReturnCode = 0
 7 
 8IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 9BEGIN
10EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
11IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
12 
13END
14DECLARE @jobId BINARY(16)
15EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Capture_Blocking', 
16        @enabled=1, 
17        @notify_level_eventlog=0, 
18        @notify_level_email=0, 
19        @notify_level_netsend=0, 
20        @notify_level_page=0, 
21        @delete_level=0, 
22        @description=N'No description available.', 
23        @category_name=N'[Uncategorized (Local)]', @job_id = @jobId OUTPUT
24IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
25 
26EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Blocking', 
27        @step_id=1, 
28        @cmdexec_success_code=0, 
29        @on_success_action=1, 
30        @on_success_step_id=0, 
31        @on_fail_action=3, 
32        @on_fail_step_id=0, 
33        @retry_attempts=0, 
34        @retry_interval=0, 
35        @os_run_priority=0, @subsystem=N'TSQL', 
36        @command=N'
37USE [msdb]
38GO
39INSERT INTO [dbo].[Blocking]
40           ([ProcessWaiting]
41           ,[ProcessBlocking]
42           ,[WaitingQuery]
43           ,[blockingQuery]
44           ,[astatus]
45           ,[bstatus]
46           ,[waittime]         
47           ,[Instance]
48           ,[DBName]
49           ,[killbill]
50           )
51          
52          select a.spid ProcessWaiting,a.blocked ProcessBlocking,sptxt.text WaitingQuery, blktxt.text blockingQuery 
53,a.status,b.status , a.waittime ,  @@SERVERNAME as ServerName , db_name(a.dbid) as DBName
54,''KILL '' + cast(a.blocked as varchar) KILLBill
55from sys.sysprocesses 
56a cross apply sys.dm_exec_sql_text(a.sql_handle) sptxt 
57join sys.sysprocesses b on a.blocked = b.spid 
58cross apply sys.dm_exec_sql_text(b.sql_handle) blktxt
59where a.blocked <> 0  
60order by a.spid
61', 
62        @database_name=N'msdb', 
63        @flags=0
64IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
65EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
66IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
67EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Blocking_Schdule1', 
68        @enabled=1, 
69        @freq_type=4, 
70        @freq_interval=1, 
71        @freq_subday_type=2, 
72        @freq_subday_interval=10, 
73        @freq_relative_interval=0, 
74        @freq_recurrence_factor=0, 
75        @active_start_date=20201108, 
76        @active_end_date=99991231, 
77        @active_start_time=0, 
78        @active_end_time=235959, 
79        @schedule_uid=N'31cdc500-1447-4926-97af-62073fc8fff0'
80IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
81EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
82IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
83COMMIT TRANSACTION
84GOTO EndSave
85QuitWithRollback:
86    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
87EndSave:
88GO

1
2Print '-- Deadlock simulation Session 1'
3USE Deadlockdemo;
4GO
5BEGIN TRANSACTION
6   UPDATE invoices_demo SET updated_at = GETDATE() WHERE id = 1;
7   WAITFOR DELAY '00:00:10';  -- 10s
8   UPDATE invoice_items_demo SET qty = qty + 1 WHERE invoice_id = 1 AND item_index = 2;
9ROLLBACK TRANSACTION

1
2Print '-- Deadlock simulation Session 2'
3USE Deadlockdemo;
4GO
5BEGIN TRANSACTION
6   UPDATE invoice_items_demo SET qty = qty + 1 WHERE invoice_id = 1 AND item_index = 2;
7   WAITFOR DELAY '00:00:10';
8   UPDATE invoices_demo SET updated_at = GETDATE() WHERE id = 1;
9ROLLBACK TRANSACTION

 1
 2--Create Temporary table on Tempdb Database
 3Create table #Logs
 4(Logdate Datetime, Processinfo Varchar(100), Errortext Varchar(4000))
 5GO
 6---Load the SQL Server Error Log on temp table #Log
 7insert into #Logs
 8EXEC master.dbo.xp_readerrorlog
 9GO
10--Select the Deadlock errors
11Select * from #Logs
12where Errortext like '%Deadlock%'
13order by Logdate DESC
14--Drop the #Log table
15GO
16Drop table #Logs

 1
 2WITH events_cte AS (
 3  SELECT
 4    xevents.event_data,
 5    DATEADD(mi,
 6    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
 7    xevents.event_data.value(
 8      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
 9    xevents.event_data.value(
10      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
11      AS [client app name],
12    xevents.event_data.value(
13      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
14      AS [client host name],
15    xevents.event_data.value(
16      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
17      AS [database name],
18    xevents.event_data.value(
19      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
20      AS [database_id],
21    xevents.event_data.value(
22      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
23      AS [object_id],
24    xevents.event_data.value(
25      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
26      AS [index_id],
27    xevents.event_data.value(
28      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
29      AS [duration (ms)],
30    xevents.event_data.value(
31      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
32      AS [lock_mode],
33    xevents.event_data.value(
34      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
35      AS [login_sid],
36    xevents.event_data.query(
37      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
38      AS blocked_process_report,
39    xevents.event_data.query(
40      '(event/data[@name="xml_report"]/value/deadlock)[1]')
41      AS deadlock_graph
42  FROM    sys.fn_xe_file_target_read_file
43    ('C:\temp\blocked_process*.xel',
44     'C:\temp\blocked_process*.xem',
45     null, null)
46    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
47)
48SELECT
49  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
50       THEN 'Deadlock'
51       ELSE 'Blocked Process'
52       END AS ReportType,
53  [event time],
54  CASE [client app name] WHEN '' THEN ' -- N/A -- '
55                         ELSE [client app name]
56                         END AS [client app _name],
57  CASE [client host name] WHEN '' THEN ' -- N/A -- '
58                          ELSE [client host name]
59                          END AS [client host name],
60  [database name],
61  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
62  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
63  index_id,
64  [duration (ms)],
65  lock_mode,
66  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
67  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
68       THEN deadlock_graph
69       ELSE blocked_process_report
70       END AS Report
71FROM events_cte
72ORDER BY [event time] DESC

1C:\>dir system_health* /s /b

1
2SELECT s.name, se.event_name
3FROM sys.dm_xe_sessions s
4       INNER JOIN sys.dm_xe_session_events se ON (s.address = se.event_session_address) and (event_name = 'xml_deadlock_report')
5WHERE name = 'system_health'
6SELECT CONVERT(xml, event_data).query('/event/data/value/child::*'),
7       CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
8FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null)
9WHERE object_name like 'xml_deadlock_report'

 1SELECT 
 2    'Deadlocks Occurrences Report', 
 3    CONVERT(BIGINT,((1.0 * p.cntr_value / 
 4NULLIF(datediff(DD,d.create_date,CURRENT_TIMESTAMP),0)))) as 
 5AveragePerDay,
 6    CAST(p.cntr_value AS NVARCHAR(100)) + ' deadlocks have been recorded 
 7since startup.' AS Details, 
 8    d.create_date as StartupDateTime
 9FROM sys.dm_os_performance_counters p
10INNER JOIN sys.databases d ON d.name = 'tempdb'
11WHERE RTRIM(p.counter_name) = 'Number of Deadlocks/sec'
12AND RTRIM(p.instance_name) = '_Total'
13;

1
2Select * from MSDB..Blocking
3order by Btime DESC