Deadlock History for Database
Overview
The Deadlock History report answers what keeps colliding with what.
That is the question whose answer is a fix. Knowing a database had forty deadlocks last month tells you there is a problem; knowing that thirty-eight of them were dbo.OrderLine under the same two statements tells you what to change.
The page exists in two scopes – one for the whole instance, one for a single database. They are the same report, and everything except the title, the database filter and one grid column is shared between them.
Its pairing with Deadlocks by Database is deliberate. That report answers which database, and when. This one answers what keeps colliding with what. Giving both pages the same chart would have given the product two pages saying the same sentence.
What this fixes
Both pages used to be a bare grid – eight columns on the instance one, thirteen on the database one, every column centred including two 300-pixel columns of SQL, so the database page was born scrolling sideways. No chart, no toolbar, no summary, and no owner drawing, so neither page responded to dark mode or colour-blind mode. The window was hard-coded to thirty days at the call site with no way to change it from the page.
Three defects in the old queries mattered more than any of that, because they made the numbers wrong rather than merely ugly:
- The row fan-out. Both queries applied over every non-victim process in the graph, so a three-process deadlock produced two rows. The database page applied it twice – once for the blocker’s input buffer and once for the process attributes – so a three-process deadlock produced four rows and a four-process deadlock produced nine. With a
TOP 50in front of that, “fifty rows” could be a dozen actual deadlocks, with the same deadlock repeating. - The silent
TOP 50. Neither page said it had truncated, so an instance with twelve hundred deadlocks in the window looked exactly like one with fifty. ISNULLover an integer. The database page wroteISNULL(spid, ''), which makes SQL Server convert the empty string to0to match the integer type – so a missing value rendered as a confident zero.
The fix for the fan-out is one row per deadlock whatever the graph contains, with every value read off that single node. The cap is now much higher, and what was left out is stated in the notice band instead of hidden.
Where to find it
Instance scope: right-click the server and open Instance Reports → Deadlock History.
Database scope: select a database in the tree and open its Deadlock History section.
The instance page carries a Database column; the database page does not, because every row on it would say the same thing.
The three views
| View | What it draws | The question it answers |
|---|---|---|
| Hotspots | Objects ranked by contention, each with its own spark-strip. The default. | What is being fought over? |
| Stream | Every deadlock on one axis, with a day histogram and an hour ribbon. | When does this happen? |
| Pairs | Blocker statements joined to victim statements by weighted ribbons. | Which two statements collide? |
Pairs is the view that usually produces the fix. A thick ribbon between two statements is the access-order problem, named.
Past seven pairings the ribbons stop being tellable apart, so the tail is rolled up rather than drawn as a thicket.
Where an equivalent earlier window is available, each object also carries its trend against the previous window – “+486% on the previous 30 days”. When no comparable earlier window was kept, the page says so rather than showing a count that looks like it has a trend behind it.
Clicking a row finds it in the grid; double-clicking a tick opens that exact deadlock graph.
The status badges
Each object carries a verdict badge:
| Badge | Meaning |
|---|---|
| Deadlocking now | Something deadlocked on this object within the last day. Still happening. |
| Chronic | Deadlocking on most days of the window rather than in one burst. |
| (no badge) | Deadlocks in the window, neither live nor chronic. |
| Dormant | Nothing for two weeks or more – history rather than a problem. |
| No object in the graph | The deadlock graph named no object. Drawn hatched, never blank. |
Objects also carry a lock-mode chip – X lock · 100% means every deadlock on that object was an exclusive lock.
“Deadlocking now” and Chronic are different findings. The first means it is happening today. Chronic means it is built into how the application works – deadlocking on 60% or more of the window’s days, on a sample of at least three affected days.
Chronic and Dormant are statements about a window long enough to have a rhythm. On a window shorter than a week they would be noise, so below that every row is simply active or normal.
A deadlock with more than two processes wears an amber mark. Those are rarer and usually harder to reason about.
Reading the grid
| Column | What it is |
|---|---|
| When | When it happened. |
| Database | (instance scope only) – dbo.OrderLine in two databases is two different problems. |
| Object | The object fought over, shortened to fit a grid line. |
| Index | The index involved. |
| Lock | The lock mode. |
| Procs | How many processes were in the graph. More than two is a tangle. |
| Blocker ▸ Victim | The two statements, as fingerprints. |
The eight columns the old database page carried – hostname, spid, tran count, lock mode, client app, login name, isolation level – are gone rather than rearranged. They are all shown by the deadlock advisor a double-click opens, and the advisor shows them for the victim as well as the blocker. The grid was a worse copy of the dialog behind it.
The toolbar
| Group | Buttons |
|---|---|
| Window | 7 days · 30 days · 60 days |
| View | Hotspots · Stream · Pairs |
Refresh |
The window is now a control on the page rather than a hard-coded number at the call site.
There is no auto-refresh. The service collects deadlocks on a fifteen-minute timer and the window is measured in weeks, so a heartbeat would re-read the history table – and re-shred its XML – to redraw the same picture.
Right-click actions
| Item | What it does |
|---|---|
| Open this Deadlock Graph | The deadlock advisor, showing both sides in full. |
| Copy Blocker Statement to Clipboard | The statement that held the lock. |
| Copy Victim Statement to Clipboard | The statement that was rolled back. |
| Copy Object Name to Clipboard | Just the object. |
| Copy Deadlock Detail Query to Clipboard | An investigation query. |
Double-clicking a row opens the graph.
Requirements
This is a historic report. It reads the DeadlockHistory table in the DBHealthHistory repository, populated by the Database Health Monitor collection service on a fifteen-minute timer.
Without collection configured for the instance, there is nothing to show. Because it reads history rather than the live instance, it works when the monitored server is unavailable.
Every deadlock fetched has its graph shredded for the object, the lock mode and the two statements. A deadlock graph is a large document, so that work is what decides how long the page takes. The window total is counted separately and cheaply, which is what lets the notice band state how many were left out without paying to fetch them.
How to read the report
- Start in Hotspots. The top row is the object costing you the most.
- Read the badge before the count. Active and Chronic are the two that need action; Dormant is closed.
- Switch to Pairs for the top object. The thickest ribbon is the statement pair to change.
- Use Stream to see whether it is a burst or a rhythm. A spike at one hour every day is a job; a smear is normal load.
- Open the graph on one representative deadlock. The advisor shows both sides, including the columns the old grid guessed at.
- Check the notice band. If the window was truncated, you are looking at a sample.
Common patterns
One object, two statements, thick ribbon. The classic deadlock: two code paths touching the same rows in opposite order. Fix the ordering or narrow the transaction.
Chronic on a small lookup table. Usually an update-in-place pattern on a hot row – a counter, a sequence table, a status flag.
Active with a very recent first appearance. Something changed lately. A deployment, a new index, or a plan change.
Many unattributed deadlocks. The graphs named no object. Often lock escalation or an in-memory structure rather than a table.
Deadlocks with more than two processes. Rarer and harder. Read the graph rather than the grid – the shape matters more than the counts.
Dormant rows filling the page. Old problems already fixed. Narrow the window to see what is current.
Related reports
| Report | Why you would go there |
|---|---|
| Deadlocks by Database | Which database and when – the rollup this page pairs with. |
| Blocking Tree | The contention that has not turned into a deadlock yet. |
| Tables With Triggers | A trigger extends the time a write holds locks. |
| Missing Indexes | Deadlocks often resolve when a scan becomes a seek. |
Frequently asked questions
What is the difference between this and Deadlocks by Database? That page answers which database, and when. This one answers what keeps colliding with what – the question whose answer is a fix.
Why did my deadlock count drop compared to the old report? Because the old count was inflated by a row fan-out – on the database page, a three-process deadlock produced four rows. The number here is one row per deadlock.
Why does the page say some deadlocks were left out? The window held more than the page fetches. That is stated rather than hidden, which is what the old silent TOP 50 did not do.
What does Chronic mean exactly? Deadlocks on at least 60% of the window’s days, over at least three affected days. It is only applied to windows of a week or more, because a shorter window has no rhythm to measure.
Why are there no spid or login columns? Because the advisor behind a double-click shows them, for both sides of the deadlock. The old grid showed them for one side, and sometimes showed a missing value as zero.
What is an unattributed deadlock? One whose graph named no object. It is drawn hatched rather than left blank, so it is visibly unattributed rather than invisible.
Why is there no auto-refresh? Collection runs every fifteen minutes and the window is weeks long. Re-shredding the XML on a timer would cost a great deal to redraw the same picture.