How To See Deadlock Rank: A Technical Guide To Identifying And Managing SQL Server Deadlock Priorities

To identify the deadlock rank or priority of a session in SQL Server, you must query the sys.dm_exec_sessions dynamic management view to see the deadlock_priority column or analyze the XML deadlock graph captured by Extended Events. The database engine uses this numeric value, ranging from -10 to 10, as the primary metric to determine which transaction to terminate as the victim when a circular blocking dependency occurs. Monitoring these ranks is essential for ensuring that high-value transactions are protected while lower-priority background tasks are sacrificed during resource contention.

Pre-Analysis Configuration and Diagnostic Requirements

Before attempting to view or modify deadlock ranks, you must ensure your environment is configured to capture and expose session metadata. Deadlock rank, technically referred to as Deadlock Priority in the SQL Server ecosystem, is not always logged in the standard error log unless specific trace flags or Extended Event sessions are active. Without the proper administrative permissions and diagnostic tools, the data remains ephemeral and is lost the moment the deadlock is resolved by the database engine’s Lock Monitor thread.

The following prerequisites are mandatory for a successful diagnostic session:

• Administrative Permissions: You must possess the VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE permission to query the dynamic management views that house session-level priority data. For historical analysis via Extended Events, the ALTER ANY EVENT SESSION permission is required.
• Diagnostic Tooling: SQL Server Management Studio (SSMS) version 18.x or higher is recommended for viewing the graphical and XML representations of deadlock reports.
• Standard Nomenclatures: Familiarize yourself with the three named priority levels: LOW (mapping to -5), NORMAL (mapping to 0), and HIGH (mapping to 5). Any integer between -10 and 10 is a valid rank.
• Operational Benchmarks: In a standard production environment, most sessions default to a rank of 0. Diagnostic efforts should focus on identifying sessions that have been explicitly elevated or lowered, as these are the primary drivers of victim selection.
• Capture Duration: Monitoring should be active for at least one full business cycle (24 hours) to capture intermittent deadlocks that occur during specific maintenance windows or high-traffic periods.

Advanced Workflow for Identifying and Interpreting Deadlock Priority Levels

The process of seeing the deadlock rank involves two distinct methodologies: real-time monitoring of active sessions and retrospective analysis of completed (and failed) transactions.

Step 1: Inspecting Live Session Deadlock Ranks

To see the rank of currently active sessions, you must interact with the system's dynamic management views. This is the most direct way to verify if a specific application or user session has been configured with a non-default priority.

1. Access the SQL Server instance using a query window in your management tool.
2. Query the sys.dm_exec_sessions view. You specifically need to examine the session_id, login_name, and deadlock_priority columns.
3. Filter the results to exclude system sessions (generally those with a session_id of 50 or less) to focus on user-defined workloads.
4. Observe the deadlock_priority column. A value of 0 indicates the default NORMAL priority. A value of -5 indicates LOW, and 5 indicates HIGH. If you see numeric values like -8 or 3, these were set using the numeric assignment syntax in the SET DEADLOCK_PRIORITY statement.

Pro-Tip: If a session shows a rank that contradicts your application logic, check for login triggers or connection string parameters that may be executing a SET statement immediately upon connection.

Step 2: Capturing Historical Deadlock Ranks via Extended Events

Since deadlocks often happen in milliseconds, you likely need to see the rank of a session that has already been killed as a victim. The system_health Extended Events session, which is active by default in SQL Server, captures these events automatically.

1. In the Object Explorer, navigate to Management, then Extended Events, and finally Sessions.
2. Expand the system_health session and right-click the package0.event_file target.
3. Select View Target Data to open the event viewer.
4. Filter the results for the event named xml_deadlock.
5. Click on an individual deadlock event and examine the Details pane. You must look for the XML report section.
6. Inside the XML, find the process nodes. Each process node contains an attribute named priority. This value represents the deadlock rank of that specific process at the time the conflict occurred.

Step 3: Analyzing the XML Deadlock Graph for Victim Selection

Viewing the rank is only half the battle; you must understand how that rank influenced the engine's decision to terminate a specific process.

1. Locate the victim-list section at the top of the XML deadlock report. This identifies which process was rolled back.
2. Compare the priority attribute of the victim process against the priority attributes of the other processes involved in the cycle.
3. Note that the SQL Server Lock Monitor always chooses the process with the lowest deadlock rank as the victim.
4. If all processes have the identical rank, look at the logused attribute within each process node. When ranks are tied, the engine selects the process that has generated the least amount of transaction log bytes, as this process is the "cheapest" to roll back.

Warning: Never assume that a HIGH priority session cannot be a deadlock victim. If a HIGH priority session (rank 5) is deadlocked with another session that has been manually set to a rank of 6 or higher, the HIGH priority session will be sacrificed.

Step 4: Decoding the Impact of Parallelism on Rank

In complex environments, a single session may spawn multiple threads (parallelism). When seeing the deadlock rank in these scenarios, the rank is applied to every thread of that session.

1. Identify if the deadlock involves multiple threads from the same session_id.
2. Verify the kpid (kernel process ID) for each thread in the deadlock graph.
3. Confirm that the priority remains consistent across all threads of the same session. If a deadlock occurs between two threads of the same session (an intra-query deadlock), the rank becomes irrelevant because the session is essentially deadlocking itself.

Deadlock Priority Levels and Engine Impact Comparison

The following table outlines the standard mapping between named priority constants and their numeric equivalents used by the SQL Server engine during the arbitration process.

Common Diagnostic Failures and Resolution Strategies

Identifying deadlock ranks can be frustrated by several technical hurdles. Below are the most common failure scenarios encountered by senior database administrators and their respective fixes.

• Scenario: The Deadlock Rank Appears as NULL or Missing

  
  
  
  • Root Cause: This usually occurs when querying deprecated system views like sys.sysprocesses which do not fully support the granular numeric priority scale, or when the Extended Event session was not configured to capture the deadlock_report action.
  • Actionable Fix: Transition all diagnostic queries to use sys.dm_exec_sessions and ensure your Extended Event session includes the xml_deadlock event with the appropriate "Collect Report" field enabled.

• Scenario: Identical Ranks Resulting in Unexpected Victims

  
  
  
  • Root Cause: When two sessions have the same deadlock rank (e.g., both are set to 0), the engine defaults to the transaction's "cost." If a critical process has just started and has a small log footprint, it may be killed instead of a long-running, low-priority process.
  • Actionable Fix: Explicitly set the deadlock rank of the critical process to a higher value (e.g., SET DEADLOCK_PRIORITY 5) to ensure that the log cost is only considered if the other session is also of HIGH priority.

• Scenario: Deadlock Priority Setting Does Not Persist

  
  
  
  • Root Cause: The SET DEADLOCK_PRIORITY command is scoped to the current session or scope. If the application uses a connection pool and does not reset the priority, or if the setting is changed within a stored procedure, it may not reflect accurately in the broader session metadata.
  • Actionable Fix: Implement a wrapper in your data access layer to explicitly set the priority immediately after the connection is retrieved from the pool, or use a DATABASE LEVEL TRIGGER to assign ranks based on login roles.

Frequently Asked Questions

How can I see the deadlock priority of a specific SQL Agent Job?

To see the rank of a SQL Agent Job, you must find the session ID associated with the job execution by querying sys.dm_exec_sessions and filtering by the program_name column, which typically includes the Job ID. Once the session ID is identified, look at the deadlock_priority column in the same view.

Does setting a higher deadlock rank prevent deadlocks from occurring?

No, setting a higher rank does not prevent deadlocks; it only dictates which process will survive once a deadlock is detected. To prevent deadlocks, you must address the underlying architectural issues, such as inconsistent object access patterns, excessive locking due to missing indexes, or overly long transaction hold times.

Can I see the deadlock rank in the SQL Server Error Log?

The standard SQL Server Error Log records that a deadlock occurred but does not typically display the numeric rank of the participants unless Trace Flag 1222 is enabled. If this flag is active, the error log will contain a text-based representation of the deadlock where the priority level of each participant is explicitly listed.

What is the default deadlock rank if I don't specify one?

The default deadlock rank for every connection in SQL Server is 0, which corresponds to the NORMAL priority level. Unless an application explicitly executes a SET DEADLOCK_PRIORITY statement, all sessions will compete on equal footing, with the engine using transaction log size as the tie-breaker for victim selection.

Is there a way to see deadlock ranks across a whole cluster or Always On Availability Group?

You must check the Extended Event logs on the specific replica where the deadlock occurred. Since deadlocks are local to the engine instance managing the locks, the rank information will be stored in the system_health or custom XEvent session of the primary replica (for write-write conflicts) or the secondary replica (for conflicts involving read-only intent workloads).

Optimize Your Database Concurrency Strategy

Mastering the visibility of deadlock ranks is the first step toward building a resilient database architecture that prioritizes critical business logic over background processing. If you are experiencing frequent contention issues, consider performing a comprehensive audit of your transaction isolation levels and indexing strategies to reduce the frequency of lock escalations.