Troubleshooting SQL Server Deadlocks using Foglight and T-SQL Queries
Introduction:-
This document explains how to find and fix SQL Server deadlocks using Foglight and T-SQL queries. Deadlocks can slow down the system or cause errors in applications.
By following these steps, you can quickly identify the cause of deadlocks and take action to fix them. This helps keep the database running smoothly and avoids problems for users.
Step 1: Confirm the Alert Check Foglight under:
Databases > SQL Server > Locks > Deadlocks
Validate spike in Deadlocks/sec metric.
Step 2: Extract Deadlock Graphs from Foglight
Use the Deadlock tab in Foglight to download visual deadlock reports.
Step 3: Capture Deadlocks from SQL Server (System Health)
SELECT XEvent.query(‘(event/data/value/deadlock)[1]’) AS DeadlockGraph FROM (SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = ‘system_health’) AS Data CROSS APPLY TargetData.nodes(‘//RingBufferTarget/event[@name=”xml_deadlock_report”]’) AS XEventData(XEvent);
Step 4: Identify Conflicting Queries and Objects
SELECT request_session_id AS SessionID, resource_type, OBJECT_NAME(p.object_id) AS LockedObject, t.text AS QueryText FROM sys.dm_tran_locks l JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id JOIN sys.dm_exec_sql_text(r.sql_handle) AS t LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id WHERE resource_database_id = DB_ID();
Step 5: Analyze Execution Plan (optional)
SELECT st.text, qp.query_plan FROM sys.dm_exec_requests r JOIN sys.dm_exec_query_stats qs ON r.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp WHERE r.session_id IN ([Session IDs from Deadlock]);
Step 6: Apply Fix Recommend consistent access order across transactions.
Add indexes to reduce locking.
Break long transactions.
Consider Snapshot Isolation or Retry Logic in application.
Conclusion:-
By using Foglight and SQL Server queries, we can effectively detect and troubleshoot deadlocks. Following this process helps reduce performance issues and ensures smoother database operations. Regular monitoring and quick action are key to maintaining a stable and responsive SQL Server environment.
