Hey there, let's break down your SQL Server deadlock questions clearly and practically:

First, confirming your understanding of deadlock victims #

You’re totally right about how SQL Server handles deadlocks! By default, it selects the transaction with the lowest overall cost—think fewer data modifications, shorter execution time, or an explicitly set lower deadlock priority—as the victim. The goal is to minimize total system impact, and if your use case can tolerate this occasional failure, the next key piece is knowing how to monitor and list deadlocked processes.

How to list all deadlocked processes #

Below are the most reliable methods, tailored to different SQL Server versions:

1. Use Dynamic Management Views (DMVs) for real-time deadlock data #

For SQL Server 2012 and newer, sys.dm_tran_deadlocks gives direct access to current deadlock transactions. Pair it with other DMVs to get context about the associated sessions:

-- Get core deadlock transaction details
SELECT 
    deadlock_id,
    transaction_id,
    database_id,
    resource_type,
    resource_description,
    mode AS lock_mode
FROM sys.dm_tran_deadlocks;

-- Join with session data to see which processes are involved
SELECT 
    s.session_id AS process_id,
    s.login_name,
    s.host_name,
    dt.deadlock_id,
    dt.resource_type,
    dt.resource_description
FROM sys.dm_tran_deadlocks dt
JOIN sys.dm_tran_session_transactions st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id;

2. Use Extended Events (Recommended for SQL Server 2012+) #

Extended Events are lightweight, efficient, and the modern replacement for SQL Server Profiler. Here’s how to set up a session to capture deadlocks and view the results:

-- Create an extended event session to track deadlocks
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'DeadlockReports.xel')
WITH (STARTUP_STATE=ON);

-- Start the monitoring session
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE=START;

-- Query captured deadlock reports
SELECT 
    XEventData.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    XEventData.value('(event/@timestamp)[1]', 'datetime') AS EventTime,
    XEventData.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT CAST(event_data AS XML) AS XEventData
    FROM sys.fn_xe_file_target_read_file(N'DeadlockReports*.xel', NULL, NULL, NULL)
) AS XEventData;

You can copy the DeadlockGraph XML into SQL Server Management Studio (SSMS) and open it as a "Deadlock Graph" to see a visual map of all locked resources and competing processes.

3. SQL Server Profiler (Legacy, pre-2016) #

If you’re on an older SQL Server version, Profiler still works for capturing deadlocks:

• Open Profiler, create a new trace, and under the Locks event category, select the Deadlock Graph event.
• Start the trace—when a deadlock occurs, Profiler will generate a visual graph showing all involved processes and the resources they’re fighting over.

Bonus: Check for blocking processes (deadlock precursors) #

If you want to spot processes that are blocking others (a common precursor to deadlocks), run this query:

SELECT 
    blocking_session_id,
    session_id AS blocked_process_id,
    wait_type,
    wait_time,
    resource_description,
    login_name,
    host_name
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0;

内容的提问来源于stack exchange，提问作者Brian Gradin