Quick Scan Report – Deadlocks

What this check looks for

Deadlock records collected into the DBHealthHistory database, where historic collection is configured. The check confirms the collection database is present and at a supported version before reading it.

Without DBHealthHistory and its collection, this check finds nothing, which does not mean there are no deadlocks. The system_health extended events session records them regardless, and the queries below read it directly.

Why it matters

A deadlock is not a slow query. It is a transaction that SQL Server killed.

When two sessions each hold a lock the other needs, neither can proceed. SQL Server’s deadlock monitor notices, picks one as the victim, kills it and rolls it back. The victim receives error 1205:

Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What happens next is entirely up to the application, and that is where the real variation is:

  • A well written application catches 1205 and retries, and the user never knows. Deadlocks are then a performance cost rather than a correctness one.
  • An application that does not catch it surfaces an error to the user, or worse, logs it and carries on with a half-finished unit of work that it believes succeeded.
  • A batch job that does not retry leaves part of its work undone, and whether that is noticed depends on whether anything reconciles.

So the same deadlock rate can be invisible on one system and a data integrity problem on another. That is why this sits at Medium: the finding tells you they are happening, and only you know what your applications do about it.

A few deadlocks a week on a busy OLTP system is normal. Dozens a day is a design problem. The rate matters more than the existence.

And deadlocks are almost always fixable, far more so than general blocking, because they have a specific and usually simple cause:

  • Inconsistent access order. Two procedures that update the same two tables in opposite orders will deadlock eventually. This is the most common cause by a wide margin.
  • A missing index, which makes a query scan and take far more locks than it needs.
  • Lock escalation, where a statement crosses the threshold and takes a table lock.
  • A long transaction holding locks while doing something slow, which widens the window.

How to confirm it yourself

The system_health session records every deadlock and it is on by default. This is the query to reach for:

SELECT CAST(event_data AS XML) AS [deadlock_xml],
       CAST(event_data AS XML).value('(event/@timestamp)[1]', 'datetime2') AS [when]
  FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
 WHERE [object_name] = 'xml_deadlock_report'
 ORDER BY [when] DESC;

Click the XML in the results to open the deadlock graph. It shows both sessions, the resources each held and wanted, and which was chosen as the victim.

The accumulated count since startup:

SELECT [object_name], [counter_name], [cntr_value]
  FROM sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE [counter_name] = 'Number of Deadlocks/sec'
   AND [instance_name] = '_Total';

That counter is cumulative despite its name, so it is the total since the instance started.

Read the graph for these three things, which is the whole diagnosis:

  1. The objects involved. Usually two tables, and the pair is the clue.
  2. The order each session took them in. If they differ, that is your cause and your fix.
  3. The statements. The graph carries the SQL for each side.

How to fix it

Deal with the cause, and separately make sure the application handles the victim.

1. Make access order consistent. If procedure A updates Orders then Customers, and procedure B updates Customers then Orders, they will deadlock. Changing one so both take the tables in the same order removes the deadlock entirely. This is the single most effective fix and it is usually a small change.

2. Add the missing index. A query that scans a table to find one row takes locks on everything it reads. An index that turns the scan into a seek reduces the lock footprint to almost nothing, and deadlocks stop. The Missing Indexes report is where to look.

3. Shorten the transactions. Take locks as late as possible, commit as early as possible, and never hold a transaction open across a user interaction or an external call.

4. Make the application retry. This is not a fix for the deadlock and it is essential anyway. Error 1205 is explicitly retryable, and the message says so. A retry with a short random delay handles the residual deadlocks that design changes will never eliminate entirely.

5. Consider READ COMMITTED SNAPSHOT where readers and writers are colliding. It removes shared locks for readers and eliminates a whole class of reader-writer deadlocks. It uses tempdb for row versions, which has its own considerations and its own checks.

6. On SQL Server 2022, look at optimized locking, which substantially reduces lock footprint and therefore deadlock opportunity. There is a report on whether it is available and enabled.

What not to do: setting DEADLOCK_PRIORITY only chooses who loses. It does not reduce deadlocks, it just decides which session gets killed, and it is occasionally the right tool for protecting a critical process while the real fix is made.

How long it takes

About half an hour to read the graph and identify the cause. The fix is a code or index change with its own testing.

Report Why you would go there
Deadlock History Every recorded deadlock, with its graph.
Deadlock Objects Which tables are involved most often.
Deadlocks by Hour Whether they cluster at a particular time.
Deadlocks by DB Which database they are concentrated in.
Blocking Tree The blocking that precedes them.
Missing Indexes The scans that widen the lock footprint.
Optimized Locking Whether the 2022 feature is in play.
Check
Updlock causing blocking A specific hint that produces contention.
Sessions running in serializable isolation level An isolation level that makes deadlocks far more likely.
A transaction has been open far too long Long transactions widening the window.
Missing Primary Keys Tables without a good access path.
Worker threads are running out Where severe blocking ends.

Frequently asked questions

How many deadlocks are acceptable? A handful a week on a busy OLTP system is normal and worth having retry logic for. Dozens a day is a design problem worth fixing.

Nothing is reported but I know we get deadlocks. This check reads collected history from DBHealthHistory. Without that collection it finds nothing. The system_health query above reads them regardless.

Can I stop deadlocks entirely? Not reliably, and you can make them rare. Consistent access order plus the right indexes removes most of them; retry logic handles the rest.

Does raising DEADLOCK_PRIORITY help? It changes who gets killed, not how often it happens. Useful for protecting one critical process, not a fix.