Deadlock Objects for a Database
Overview
The Deadlock Objects report answers which objects collide with which, on what index, under what lock mode, and which one loses. This is the database scope version: one database, drawn from the retained deadlock history.
A deadlock is a cycle over resources. Every resource in a deadlock graph carries an owner list and a waiter list of process ids, so a process that holds one object and waits on another is a directed link between the two. That link is what this report draws.
The shape worth opening the report for is a pair of objects with links running in both directions:
Two statements taking the same two tables in opposite orders is a reversed lock order. It is the textbook deadlock, it is nearly always a half-day fix, and in a grid it is spread across fifty rows where nobody will ever see it.
One thing this page cannot show you
A pairing that crosses two databases will not appear here. A process holding a row in this database and waiting on a row in another one is a real deadlock, and this page can only see the half of it that lives here. That shape is what a linked server or a distributed transaction produces, and the instance scope version of this report is the only page that draws it as one ribbon.
If the ring here looks incomplete, or an object seems to collide with nothing, open Deadlock Objects at instance scope before concluding anything.
Where to find it
| Route | How |
|---|---|
| Server tree | Select a database → Historic → Deadlock Objects |
The page title reads Deadlock Objects for <database name>.
There is no Database column on this page, because every row on it would say the same thing.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Deadlocks are collected from the system_health extended events session, which earlier versions cannot be read from this way. |
| Historic monitoring configured | The report reads DBHealthHistory.dbo.DeadlockHistory. It never queries the monitored instance for deadlocks. |
The three views
Collisions (the default)
One arc per object, as wide as the traffic through it. One ribbon per held-object to waited-object pairing, as thick as the number of deadlocks that had that shape.
Each ribbon takes the colour of the object that held the lock, tapers towards the object that was waiting, and carries a mark at the waiting end, so direction reads from the shape rather than from a key. Amber marks the two shapes that name a fix rather than an investigation: a reversed lock order, and a lock conversion.
The finding is printed in the middle of the ring. An empty middle means the report found neither shape.
Past twelve objects the ring rolls the tail into one hatched arc, so the ribbons still account for the whole window. The line underneath says how many objects went into it, and every one of them is still in the grid.
A single database usually has fewer than twelve deadlocking objects, so this page reaches the roll up far less often than the instance one.
Contact points
One bar per object, split by the index the lock was taken on.
This is the view that turns a finding into an edit. OrderLine deadlocks is a shrug; 63% of them on IX_OrderLine_OrderId is an afternoon’s work. A segment whose graph named no index is hatched rather than coloured, because a heap and an object level lock are the absence of an index rather than an index called nothing.
Because every object here is in one database, this view is the most directly actionable of the three: the indexes it names are all ones you can go and look at without working out where they live first.
Ranking
The plain answer, and the fallback when there are too many objects for a ring or too few for it to be worth drawing.
Each bar is split by which side of the cycle was waiting on that object when the server picked a victim, and the strip underneath is when the deadlocks happened on the shared time axis. An object that is nearly always on the victim side is where the retry logic keeps landing.
The two shapes worth finding
Reversed lock order
The same two objects appear as a pairing in both directions: one code path takes A then B, the other takes B then A. Sooner or later they interleave.
The fix is an ordering rule rather than an index or a hint, both paths take the objects in the same order, and it is usually confined to the two statements the ribbons point at.
Double-click either ribbon to open a deadlock graph with that exact shape.
Lock conversion
A process already held a resource and asked for a stronger lock on the same one. It is usually a read inside a transaction followed by a write to the same row.
The fix is to take the stronger lock the first time rather than to reorder anything, so it is kept apart from the reversed pairings rather than counted with them.
The statuses
| Status | Meaning |
|---|---|
| Deadlocking now | Something deadlocked on this object within the last 24 hours. |
| Chronic | Deadlocking on 60% or more of the window’s days, over at least three days. |
| Quiet | Nothing for 14 days or more. |
| No object in the graph | The graph named no object. Drawn hatched, never blank. |
Reading the grid
| Column | What it carries |
|---|---|
| Object | The object, with the status stripe and the colour it was drawn in on the ring. |
| Deadlocks | Deadlocks in the window that touched this object, counted once each, over a bar scaled to the busiest row. |
| Share | That count as a share of the deadlocks read. |
| Collides With | The object it pairs with most, and how often. A ⇄ marks a pairing that also runs the other way. |
| Top Index | The index most of its locks were taken on. Blank means a heap or an object level lock. |
| Lock | The most requested lock mode. Exclusive modes are drawn as a caution chip. |
| Victim Side | How often the process that lost was the one waiting on this object. |
| Days | Days of the window this object deadlocked on. |
| First In Window / Last Deadlock | The span, and how long ago the last one was. |
| vs Previous Window | The same length of time immediately before the window. Blank when retention did not keep it. |
Double-click a row to open the most recent deadlock graph that touched that object.
The toolbar
| Button | What it does |
|---|---|
| 7 / 30 / 60 days | The window. Saved between sessions. |
| Collisions / Contact Points / Ranking | The view. Switching is a repaint, not a new query. |
| Refresh | Re-runs the query. |
A view button is disabled when the data in hand cannot support it. Collisions needs a pairing, and whether a graph recorded its owner and waiter lists is a property of the deadlock rather than of the report.
There is no auto-refresh. Collection runs every fifteen minutes and the window is measured in weeks, so a sixty-second heartbeat would re-shred the history table to redraw the same picture.
Right-click actions
| Action | Where |
|---|---|
| Open the Latest Deadlock Graph | Grid and chart |
| Open the Deadlock History | Grid and chart |
| Copy Object Name to Clipboard | Grid and chart |
| Copy Collision Query to Clipboard | Grid and chart |
| Copy Chart to Clipboard | Chart |
Copy Collision Query hands back the same resource shred this report runs, narrowed to one object, so what it prints in SSMS and what the chart drew cannot disagree.
Nothing on this report runs anything against the monitored instance. A deadlock is not something anyone should be able to click a button and fix.
How to read the report
- Look at the middle of the ring. If it names a reversed pairing, that is where the afternoon goes.
- Follow the thickest ribbon. Its held end is the object a process was sitting on; its waiting end is what the other process wanted.
- Switch to Contact points for the two objects involved. If the locks concentrate on one index, that index is the contact point, and a covering index or a narrower predicate often removes the overlap entirely.
- Switch to Ranking to see which of the two keeps losing. That is the code path whose retry logic is doing the most work.
- Double-click a ribbon to read an actual graph with that shape before changing anything.
- If the picture looks incomplete, check the instance scope page. Half of a cross-database deadlock looks exactly like an object that collides with nothing.
Common patterns
| What you see | What it usually is |
|---|---|
| Two thick ribbons, both directions, amber | A reversed lock order between two code paths. |
| One object looping back to itself | Two statements taking different rows of the same table in different orders, often an IN list or a cursor in input order. |
| An amber conversion loop | A SELECT and then an UPDATE of the same row inside one transaction. |
| One arc far wider than the rest | A single hot object. Check Contact points before assuming it needs an index. |
| A busy object with no ribbon at all | Either its graphs recorded no owner and waiter lists, or the other half of the cycle is in another database. Check the instance page. |
| Ribbons fanning from one object to many | Usually a lookup or queue table touched by everything, taken at different points in different transactions. |
| A hatched arc with no ribbons | Deadlocks whose graphs named no object, so they can join to nothing. |
Where the data comes from
The DBHealthHistory repository, populated by the Database Health Monitor collection process from the system_health extended events session on a fifteen-minute timer.
Every graph in the window has its whole resource list read, one row per lock, with the owner and waiter that make the pairing, which is more XML work than the neighbouring reports do per deadlock. That is capped, and the notice band says when the cap was reached and what was left out.
The third and later owner of a single resource is not read. It needs three or more processes holding compatible locks on the same key, and the pairing it would add is a refinement of one already drawn.
The only difference in the query between this page and the instance one is a filter on the database name. Everything else, including the resource shred and the cap, is shared.
Settings
| Setting | Default | Values |
|---|---|---|
DatabaseDeadlockObjectsWindowDays |
30 |
7, 30, 60 |
DatabaseDeadlockObjectsView |
collisions |
collisions, contacts, ranking |
The window and view are saved separately from the instance scope page, so the two do not fight over one setting. They are also shared across databases: switching from one database to another keeps the window you chose.
Messages you may see
Version too old:
Deadlock history needs SQL Server 2016 or newer.
Nothing recorded:
No deadlocks recorded on this database in the last 30 days.
Timed out:
The deadlock lookup did not finish in time. Every deadlock graph in the window has its whole resource list read to work out which objects collided. Try 7 days, or the Refresh button.
Capped fetch:
Drawn from the most recent 1,200 of 4,310 deadlocks.
Related reports
| Report | Why you would go there |
|---|---|
| Deadlock Objects | The same report at instance scope, which is the only place a cross-database pairing appears. |
| Deadlock History | One row per deadlock, with the blocker and victim statements. |
| Deadlocks by Hour | The time-of-day pattern for this database. |
| Missing Indexes | A missing index widens the row range a statement locks, which is a common deadlock cause. |
| Most Used Indexes | Whether the contact point index is earning its place. |
Frequently asked questions
How is this different from the instance version? Scope, and one column. This page filters to one database and drops the Database column because every row would say the same thing. The instance page reads every database and keeps the column. Everything else is identical.
An object here collides with nothing. Why? Either its graphs did not record which process held what, so there is nothing to join, or the other end of the cycle is in a different database and this page cannot see it. The instance scope page settles which.
Why does this report exist when Deadlock History already ranks objects? Because ranking is not the question here. Deadlock History says which objects appear most; this says which objects appear together, and in which direction. A ranking cannot show a reversed lock order, because the two halves of it are two separate rows.
What is the hatched arc? Either deadlocks whose graph named no object at all, or the rolled up tail past the twelfth object. Both are labelled.
Why is Collisions greyed out? There is no pairing to draw in this window. Try a longer window, or use Ranking.
Does the window follow me when I switch databases? Yes. The window and view are per user rather than per database, so a 7 day window stays 7 days as you move around the tree.
What do I do about a chronic object with no reversed pairing? Look at Contact points first. Locks concentrating on one index usually mean the statement is locking a wider range of rows than it needs, which is an indexing or a predicate problem rather than an ordering one.