Index Contention

Overview

The indexing reports cover whether an index exists, whether it is used, whether it is duplicated and whether it is fragmented. None of them covered whether an index is a bottleneck.

Everything on this page arrives at the application as the same complaint: inserts got slow. It splits into three kinds of waiting that have nothing in common except that sentence, and each one sends you somewhere completely different.

The Index Contention report: waiting per index split into latch, lock and IO latch
The whole report. Each bar is one index, segmented by the kind of waiting. Escalation is a count rather than a duration and is deliberately not in the bar.

Where to find it

In the tree, under a database, Indexing → Index Contention.

The toolbar filters to All contention, Last-page, Blocking, Storage or Escalation.

Requirements

Requirement Why
VIEW DATABASE STATE sys.dm_db_index_operational_stats is database scoped.
An instance that has been up under real concurrency The counters begin when metadata enters the cache, so a quiet instance shows a page of zeroes.
SQL Server 2019 for the one line fix OPTIMIZE_FOR_SEQUENTIAL_KEY does not exist before it. The detection still works; only the fix changes.

The three kinds, and why they are split

Kind What is actually happening Where it leads
Page latch Sessions fighting over the same page, already in memory. Nothing to do with blocking. Adding an index or buying disks will not touch it.
Lock Another session is holding it. The blocking reports, transaction length, isolation level.
IO latch Waiting for the page to arrive from storage. The IO reports and Buffer Pool by Object.

Row and page lock waiting are added together, because both are one session waiting for another and both are fixed the same way.

Lock escalation is reported beside the three and never added to them. It is a count of events; the others are durations. A chart that stacked all four would draw a bar whose length meant nothing. It has its own column instead.

The headline finding: last page insert contention

This is the reason the page exists. It is extremely common in any queue or logging table, it is invisible in every other report in this application, and from SQL Server 2019 it has a one line fix.

It is detected from three facts together, not guessed from one:

  1. Page latch waiting dominates this index’s contention.
  2. The leading key column is sequential and ascending: an IDENTITY, a SEQUENCE default, or a datetime column defaulting to one of the GETDATE family.
  3. Inserts dominate the other DML on the index.

That combination is the PAGELATCH_EX hotspot: every session inserting is aiming at the same physical page, because a monotonically increasing key means there is only ever one page at the end of the index to insert into.

A page that flagged this on the wait type alone would call every busy index a hotspot. The key shape is what makes the finding worth acting on, and the Leading key column shows what was found, so the detection is auditable rather than a black box.

sys.indexes.optimize_for_sequential_key is read before the recommendation is made, so the page never suggests something that is already in place. Where it is already on, the row says so, and adds the thing the documentation tends to bury: the setting reduces this contention rather than removing it, so measurable latch waiting with it on is expected.

The findings

Finding What it means
Last-page insert The three facts above lined up. The one line fix, or a hash or reverse leading key before 2019.
Blocking Lock waiting dominates. Another session is holding it.
Storage IO latch waiting dominates. The pages are not in memory.
Escalation Enough escalations to a table lock to be the story on their own.
Latch Page latch waiting without the sequential key signature. Something else writes to the same pages.
Minor Measured, and not enough of anything to act on.

Reading the grid

Column What it is
Finding The band, in the chart’s colour.
Index Schema, table and index.
Leading key The first key column and how it gets its value: IDENTITY, SEQUENCE default, date default, or blank.
Latch wait Page latch waiting on this index.
Lock wait Row and page lock waiting, added.
IO latch Waiting for pages from storage.
Escalations Successful escalations to a table lock.
Observed since The earliest the observation window can have started.
Action One line, never claiming more than the evidence supports.

The caveat that has to be on the page

These counters are not cumulative since the instance started. They begin when an object’s metadata enters the cache and reset when it is evicted or the index is rebuilt, so two rows can have completely different observation windows.

This is in the footer on every visit rather than in a footnote, because it changes how the numbers compare. An index rebuilt last night and an index untouched for a month are not measured over the same period, and ranking them against each other without saying so is misleading.

Observed since is the earliest the window can have started, never the latest. The DMV does not record when its own counters were reset, so the honest reading is a lower bound. Presenting it as an exact start time would be inventing precision that does not exist.

Right-click actions in the grid

Action What it does
Show the script for this index The full index definition, with its current options.
Copy fix script SET OPTIMIZE_FOR_SEQUENTIAL_KEY = ON on a last page row, or a LOCK_ESCALATION change on an escalation row.
Go to Blocking Tree On a Blocking row.
Go to Buffer Pool by Object On a Storage row, where the pages that are not in memory are.
Copy the query behind this report The whole batch, to run and adapt yourself.

Double clicking a bar shows the index script.

Where the data comes from

Source What it gives
sys.dm_db_index_operational_stats The waiting: page latch, row and page lock, IO latch, escalation counts, and the leaf level DML mix.
sys.indexes Names, and optimize_for_sequential_key so the fix is never suggested twice.
sys.index_columns, sys.columns The leading key column, at key_ordinal = 1.
sys.identity_columns, sys.default_constraints The three sequential key shapes.
sys.tables lock_escalation, which is the setting behind the escalation rows.

The column expressions and the cumulative caveat live in Foundation\IndexOperationalStats and are shared with the Optimized Locking Readiness report, so the two pages cannot disagree about what they are counting.

Messages you may see

OPTIMIZE_FOR_SEQUENTIAL_KEY arrived in SQL Server 2019 and does not exist here. Last page insert contention is still detected on this page. What changes is the fix: before 2019 it is a hash or reverse leading key, or fewer concurrently inserting sessions.

N of the last-page insert rows already have OPTIMIZE_FOR_SEQUENTIAL_KEY on. Expected, not a contradiction. The setting reduces the contention rather than removing it.

No index in this database has recorded measurable contention. A quiet database, or an instance that has not been up long enough for the counters to accumulate.

No index has this kind of contention. A filter is active. The All contention button has the rest.

Report Why you would go there
Blocking Tree The same waiting seen live, with the session pair named.
Blocking by Hour Whether the blocking is constant or has a shape.
Index Usage Whether the index earns its writes at all.
Optimized Locking On SQL Server 2025 it changes the answer for the escalation rows outright.
Waits by Query Which queries the waiting belongs to.

Frequently asked questions

Why is lock escalation not in the bar? Because it is a count of events and the other three segments are durations. Stacking a count onto milliseconds produces a bar whose length means nothing at all. It has its own column.

My busiest index is flagged Latch rather than Last-page insert. Why? Because its leading key is not sequential, or inserts do not dominate its DML. Latch waiting without that signature is real contention with a different cause, and the fix is different too.

Why do two indexes with similar traffic show wildly different numbers? Different observation windows. One of them was rebuilt, or its metadata was evicted from the cache and the counters started again. The Observed since column is where that shows.

Should I turn on OPTIMIZE_FOR_SEQUENTIAL_KEY everywhere? No. It helps the specific case of many sessions inserting into the end of the same index, and does nothing for anything else. That is exactly the case this page detects, which is why the fix script is offered per row rather than as a blanket script.

Why does an index show escalations but almost no lock waiting? Escalation is often the fix working: one table lock is cheaper to take than a hundred thousand row locks. It becomes a problem when it stalls everything else on the table, which is why the action line says to review the batch size before changing LOCK_ESCALATION.