Detecting and Resolving Deadlocks in SQL Server 2019

A deadlock happens when two queries block each other, preventing execution.

🔍 Step 1: Enable Deadlock Tracking with Extended Events

Run this to create an Extended Events session for deadlocks:

CREATE EVENT SESSION Deadlock_Detection ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(SET filename='C:\ExtendedEvents\Deadlocks.xel');

ALTER EVENT SESSION Deadlock_Detection ON SERVER STATE=START;

🔹 This captures deadlock reports for analysis.

🛠 Step 2: Analyze and Resolve Deadlocks

Run this to view deadlocks:

SELECT
event_data.value('(event/data[@name="xml_report"]/value)', 'XML') AS DeadlockReport
FROM sys.fn_xe_file_target_read_file('C:\ExtendedEvents\Deadlocks*.xel', NULL, NULL, NULL);

🔹 How to fix deadlocks?

1️⃣ Identify the queries involved using the XML report.
2️⃣ Reduce transaction locking by committing transactions sooner.
3️⃣ Use NOLOCK hint where possible:

SELECT * FROM Orders WITH (NOLOCK);

4️⃣ Add appropriate indexes to avoid table scans.
5️⃣ Use TRY-CATCH blocks to handle deadlocks gracefully.

3️⃣ Identifying and Resolving Blocking in SQL Server 2019

🔍 Step 1: Check Blocking Queries

Run this to see which processes are blocking others:

EXEC sp_who2;

Look for rows where BlkBy is not 0.

🔹 Find the exact blocking query:

SELECT blocking_session_id AS BlockingSession,
wait_type, wait_time, blocking_session_id,
session_id AS BlockedSession,
status, command, wait_resource,
text AS BlockedQuery
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id <> 0;

🛠 Step 2: Fix Blocking Queries

✅ Find the blocking session and terminate it (if needed):

KILL <SessionID>;

✅ Optimize indexes to reduce contention.
✅ Break long transactions into smaller ones.
✅ Schedule heavy queries to off-peak hours