Deadlock
Rédigé par Sozezzo - - Aucun commentaireWe have same SQL Scripts to try to find out about what it happens when we have or had deadlocks.
- Get current running commands.
- Create event to find blocked processes and deadlocks.
Sources :
Deadlocking
https://technet.microsoft.com/en-us/library/ms177433%28v=sql.105%29.aspx
How to isolate the current running commands in SQL Server
https://www.mssqltips.com/sqlservertip/1811/how-to-isolate-the-current-running-commands-in-sql-server/
How To Monitor Deadlocks in SQL Server
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx
A very quick guide to deadlock diagnosis in SQL Server
https://dzone.com/articles/very-quick-guide-deadlock
Finding Blocked Processes and Deadlocks using SQL Server Extended Events
http://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comments
* Get current running commands
PRINT '-- Get current running commands';
-- current running commands
SELECT
@@servername AS ServerName
, GetDate() AS LocalDate
, sder.session_id AS [SPID]
, sdes.login_name AS [Login]
, sd.name AS [DBName]
, sder.start_time AS [Start Time]
, sder.status AS [Status]
, sder.percent_complete AS [Pct Cmplt]
, sder.estimated_completion_time AS [Est Cmplt Time]
, sder.wait_type AS [Wait Type]
, sder.wait_time AS [Wait Time]
, sder.last_wait_type AS [Last Wait]
, sder.cpu_time AS [CPU Time]
, sder.total_elapsed_time AS [Total Elpsd Time]
, sder.reads AS [Reads]
, sder.writes AS [Writes]
, sder.logical_reads AS [Logical Reads]
, Stuff((
SELECT
', ' + v.NAME
FROM sys.dm_exec_plan_attributes(sder.plan_handle) AS pa
INNER JOIN master..spt_values v ON v.type = 'sop' -- Set OPtions I guess
AND v.number & Cast(pa.value AS INT) = v.number
WHERE
attribute =
'set_options'
FOR XML PATH('')
), 1, 13, '') AS [Set Options]
, sder.command AS [Command]
, sdet.text AS [SQL Text]
, CASE WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset] WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(sdet.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(sdet.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2) END
ELSE
--1st part of full command is running
CASE SDER.[statement_end_offset] WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(sdet.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(sdet.TEXT, (SDER.[statement_end_offset]/2) +1) END END AS [executing statement]
FROM sys.dm_exec_Requests sder
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
JOIN sys.dm_exec_sessions sdes ON sder.session_id = sdes.session_id
JOIN sys.databases sd ON sder.database_id = sd.database_id
WHERE
sder.session_id <> @@SPID
* Create event to find blocked processes and deadlocks.
!! Make sure this path exists before you start the trace !!
-- !! Make sure this path exists before you start the trace !! CREATE EVENT SESSION [blocked_process] ON SERVER ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)) , ADD EVENT sqlserver.xml_deadlock_report ( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)) ADD TARGET package0.asynchronous_file_target (SET filename = N'c:\XEventSessions\blocked_process.xel', metadatafile = N'c:\XEventSessions\blocked_process.xem', max_file_size=(65536), max_rollover_files=5) WITH (MAX_DISPATCH_LATENCY = 5SECONDS) GO
Change configuration - enable 'blocked process threshold'
/* Enabled to show advanced options */ EXEC sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO /* Enabled the blocked process report */ EXEC sp_configure 'blocked process threshold', '5'; RECONFIGURE GO /* Start the Extended Events session */ ALTER EVENT SESSION [blocked_process] ON SERVER STATE = START;
Read data from events
DECLARE @xel nvarchar(1024); -- log filename
DECLARE @xem nvarchar(1024); -- Meatadata file
DECLARE @x nvarchar(1024); -- temp variable
SELECT @x = CAST(sys.server_event_session_fields.value as nvarchar(1024))
FROM sys.server_event_session_fields INNER JOIN sys.server_event_sessions
ON sys.server_event_session_fields.event_session_id = sys.server_event_sessions.event_session_id
WHERE sys.server_event_session_fields.name = N'filename' AND sys.server_event_sessions.name = N'blocked_process'
SET @x = SUBSTRING(@x , 1, LEN(@x)-4)
SET @xem = @x+'*.xem';
SET @xel = @x+'*.xel';
WITH events_cte AS (
SELECT
xevents.event_data,
DATEADD(mi,
DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client app name],
xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client host name],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)') AS [database name],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int') AS [database_id],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int') AS [index_id],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS [duration (ms)],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar') AS [lock_mode],
xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int') AS [login_sid],
xevents.event_data.query('(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]') AS blocked_process_report,
xevents.event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file (@xel, @xem, null, null)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
THEN 'Deadlock'
ELSE 'Blocked Process'
END AS ReportType,
[event time],
CASE [client app name] WHEN '' THEN ' -- N/A -- '
ELSE [client app name]
END AS [client app _name],
CASE [client host name] WHEN '' THEN ' -- N/A -- '
ELSE [client host name]
END AS [client host name],
[database name],
COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
index_id,
[duration (ms)],
lock_mode,
COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
THEN deadlock_graph
ELSE blocked_process_report
END AS Report
FROM events_cte
ORDER BY [event time] DESC ;