SQL Deadlock Optimizations

Deadlock

DMV Queries to execute in SQL DB to identify deadlocks

- step 1 - use MASTERSELECT * FROM sys.event_logWHERE event_type= 'deadlock'and start_time> '2023–02–08 00:00:26.5233333'and start_time< '2023–07–10 20:32:25.4600000'order by start_timedesc

- step 2
 - run in master DB - you can get a trend of what specific queries and objects are causing deadlocking with in your databases. - restrict the output by specifying the datetime range;WITH CTE AS (SELECT CAST(event_dataAS XML) AS [target_data_XML] FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null))SELECT [db_name], [query_text], [wait_resource], COUNT(*) as [number_of_deadlocks] FROM (SELECT LTRIM(RTRIM(Replace(Replace(c.value('.', 'nvarchar(250)'),CHAR(10),' '),CHAR(13),' '))) as query_text,D.value('@waitresource', 'nvarchar(250)') AS [wait_resource],target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name]from CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS T(C)CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS Q(D)where CTE.target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') >= '2023–05–26 15:49:41.4266667'and target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') <='2023–05–26 15:49:46.4633333') deadlockGROUP BY [query_text], [wait_resource], [db_name]ORDER BY [number_of_deadlocks] DESC

Get Garima Gupta’s stories in your inbox
Join Medium for free to get updates from this writer.
Remember me for faster sign in
- step 3
 - mention values for 64 and 65 line from above query - Query to get the XML , copy the XML in notepad and save as .XDL file and open in SSMS to get the dealockgraph details;WITH CTE AS (SELECT CAST(event_dataAS XML) AS [target_data_XML] FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null))SELECT [db_name], [wait_resource], [deadlock_xml] FROM (SELECT target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name],waitresource_node.value('@waitresource', 'nvarchar(250)') AS [wait_resource],deadlock_node.query('.') as [deadlock_xml]FROM CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock)') AS T(deadlock_node)CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS U(waitresource_node)where CTE.target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') >= '2023–05–26 15:49:41.4266667'and target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') <= '2023–05–26 15:49:46.4633333'

) deadlockWHERE [db_name] = 'U2Billing_cdwa'AND [wait_resource] = 'PAGE: 22:1:19099186'

Save Query text of deadlock query as .xdlfile

Fix

Made changes to Sp##### in ## DB . Split the merge statement for#### table into 3 steps : Delete, Update and Insert to resolve deadlock.