In-detail: Understand and effectively troubleshoot deadlocks in SQL Server

Are you facing deadlock issues? You do not know how to troubleshoot?

Let us first understand what is deadlock, how and why it happens in this session. Afterwards, we will learn how to effectively troubleshoot this issue.
Let us get started!!

Agenda
0:00 - Understand deadlocks
1. Meaning of blocking in SQL Server
2. How blocking leads to deadlocking.
3. In what way deadlocking is handled by SQL Server
4:45 - Causes of deadlocks
1. Lock escalation
2. Lack of worker threads
3. Lack of execution memory
4. Parallel query execution
9:49 - Detect blocking in SQL Server
11:25 - Detect deadlocks in SQL Server
13:56 - Troubleshoot and resolve deadlocks

Database used to reproduce blocking and deadlocks:
AdventureWorkdsT2019: https://github.com/Microsoft/sql-serv...

Command used to generate blocking:
In Session 67:
BEGIN TRAN
UPDATE SalesLT.Product SET SellEndDate = SellEndDate +1
WHERE Color = 'Red'

In Session 51:
SET TRANSACTION ISOLATION LEVCEL REPEATABLE READ
GO
BEGIN TRAN
SELECT * FROM SalesLT.Product
WHERE Color = 'Black'

Commands used to generate deadlocks:
In Session 63:
BEGIN TRAN
UPDATE SalesLT.Address SET City = 'Bothell'
WHERE City = 'Bothell'

SELECT * FROM SalesLT.Product
WHERE Color = 'Black'

In Session 51:
BEGIN TRAN
UPDATE SalesLT.Product SET SellEndDate = SellEndDate +1
WHERE Color = 'Red'

SELECT * FROM SalesLT.Address
WHERE City= 'Bothell'

Command used to check blocking and deadlocks (Change session ids accordingly):
SELECT request_session_id, resource_type, resource_description, request_mode, request_type, request_status
FROM sys.dm_tran_locks
WHERE request_session_id = 51 OR request_session_id = 67
ORDER by request_session_id

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id, resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id = 51 OR session_id = 67
ORDER by session_id

SELECT session_id, status, wait_type, wait_time, wait_resource, command
FROM sys.dm_exec_requests
WHERE session_id = 51 OR session_id = 67
ORDER by session_id

Command used to check memory grant:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

📚 ►Full course on Troubleshooting sessions:    • Troubleshooting  
📷 ►Follow me on Instagram - https://www.instagram.com/arslanov_bo...
🧑‍💼►Follow me on LinkedIn -   / bobirmirzo-arslanov-548960173  

Watch all my playlists here:
🚀►Full course on Encrypting connections to SQL Server-    • Full course on Encrypting connections...  
🚀 ►SQL Server replication session:    • SQL Server replication  
🚀 ►SQL Server Always On Availability Group:    • SQL Server Always On Availability Gro...  
🚀 ► SQL Server internals:    • SQL Internals  
🚀 ► Course in On-memory OLTP:    • Course on In-Memory OLTP