This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I shared T-SQL script to generate a DeadLock situation in SQL Server Database.
In this post, I am sharing different scripts to detect a Deadlock in SQL Server.
1. Using SP_LOCK, you can find the WAIT status for blocking sessions:
|
1 |
SP_LOCK |
2. Using sys.sysprocesses:
|
1 2 |
SELECT * FROM sys.sysprocesses WHERE blocked > 0 |
3. Using common DMV:
|
1 2 3 4 5 6 7 8 9 |
SELECT der.blocking_session_id AS BlockingSessionID ,dest.text AS BlockingStatement FROM sys.dm_exec_connections AS sdec INNER JOIN sys.dm_exec_requests AS der ON sdec.session_id = der.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks AS dowt ON der.session_id = dowt.session_id CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest |
4. Using sys.dm_tran_locks:
|
1 2 3 4 5 6 |
SELECT request_session_id AS SPID ,DB_NAME(resource_database_id) AS DatabaseName ,resource_type AS LockedResource ,request_mode AS LockType FROM sys.dm_tran_locks |
5. Enable required trace flags to log DeadLock related information in Tracefile:
|
1 2 |
DBCC TRACEON (1204, -1) DBCC TRACEON (1222, -1) |
6. Count total number of DeadLock:
|
1 2 3 4 |
SELECT cntr_value AS TotalNumberOfDeadLocks FROM sys.dm_os_performance_counters WHERE counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total' |
7. Using Extended Events and below script to create Extended Event for monitoring the DeadLock:
|
1 2 3 4 5 6 7 8 |
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded, ADD EVENT sqlserver.lock_deadlock, ADD EVENT sqlserver.lock_deadlock_chain, ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'C:\Temp\Deadlock_Monitor.xel') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO |
You can also use SSMS to create Extended Events for DeadLock, and you can monitor the live status of the server.
8. Using SQL Server Profiler:
SQL Server Profiler has three different events to capture a deadlock.
|
1 2 3 |
1.Deadlock graph 2.Lock: Deadlock 3.Lock: Deadlock Chain |
