Optimized Locking

Overview

Optimized locking arrived in SQL Server 2025. It is two mechanisms:

  • A single lock on the transaction id instead of a lock per modified row held to commit. This stops lock count scaling with rows modified, which is what removes lock escalation.
  • Lock after qualification, where a predicate is evaluated against the latest committed version before a lock is taken at all. This stops a scan blocking on rows it was going to discard.

Neither is on by default, and getting to them is a chain rather than a switch. Accelerated Database Recovery has to be on before optimized locking can be enabled at all, and read committed snapshot isolation decides whether the second half of the feature contributes anything.

Three settings, in order, with a default of off at every step. That is a recipe for shops running SQL Server 2025 for two years without ever switching it on, and this page exists to make that visible.

The Optimized Locking report: toolbar, readiness ladder and grid
One row per database. Three gate pips on the left say what is missing; the bar says what the gap has cost.

What makes this worth a page rather than a paragraph is that the product is already recording blocking per database in the history repository. So the page does not have to argue that the feature is good in general. It can put a measured number beside the switch, and sort by it.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → Optimized Locking

The page title reads Optimized Locking for <server name>.

The menu item is not hidden on older instances. Instance level reports are registered against a server name with no version behind them, so unlike the database level pages this one cannot hide itself. On anything older than SQL Server 2025 it opens and explains itself rather than failing.

Requirements

  • SQL Server 2025 or newer, for optimized locking to exist at all.
  • VIEW SERVER STATE on the instance, for the lock pressure DMV. Without it the lock waits, lock wait time and escalation columns are empty and the rest of the page is unaffected.
  • The DBHealthHistory repository, for the blocking measurements. Without it the bars are absent and the page says so.

The version test asks the instance two independent questions rather than reading a cached version number, and either one answering is enough: whether DATABASEPROPERTYEX recognises IsOptimizedLockingOn, and whether sys.databases has an is_optimized_locking_on column. Both test the actual capability rather than a version string. Asking twice is deliberate, because a single misremembered name would have turned the page away everywhere while looking exactly like a correctly gated report.

Nothing on this page runs anything. Enabling Accelerated Database Recovery needs exclusive access to the database, and turning on read committed snapshot isolation changes what readers see and kills every open connection on the way in. Both are somebody’s change window and an application decision, not a button on a report.

The five bands

The chain has four terminal states, and they are what the report sorts and filters by. A fifth covers the databases that cannot be asked.

Band What it means
Blocked Accelerated Database Recovery is off. There is nothing to enable yet.
One setting away ADR is on, optimized locking is off. One ALTER DATABASE.
Partial Optimized locking is on, RCSI is off, so lock after qualification is not contributing.
Full All three on. Nothing here to do.
No answer The database did not answer. Offline, restoring and read only databases cannot report their settings.

No answer is a real row, not silence. Offline and restoring databases return NULL from DATABASEPROPERTYEX, and NULL is not off. Those rows draw with hollow pips, no bar, and the reason in place of the measure, because a database quietly missing from a readiness report is the one somebody will be surprised by later.

Reading the ladder

The readiness ladder: three gate pips per database, then the cost bar
A gap in a column of pips reads as a gap. The bar beside it is what the blocking already recorded has cost that database.

One row per database: three gate pips on the left, then a bar measuring what blocking has actually cost that database.

The pips are the argument. Three settings, none of them on by default, and no way to see at a glance which databases are missing which. A grid answers that a column at a time. Pips in a column answer it in one look, because a gap reads as a gap.

The bar is the other half. Rows sort by the bar inside band order, which puts the database with the most to gain and the least done about it at the top without anybody having to sort anything.

Three things the chart deliberately does not do

Nothing here is a prediction. The bar measures blocking that happened. It is not what optimized locking would save. The feature removes a large class of lock waiting, not all blocking, and a page implying otherwise would eventually be quoted back at you by somebody whose blocking did not go away. There is no projected bar, no saving figure and no percentage anywhere on this report.

The bar never stacks. Two measures are available and they come from unrelated windows. Blocking seconds are a wall clock month out of the history database; lock wait milliseconds and escalations come from sys.dm_db_index_operational_stats, which counts from instance start and loses objects as their metadata is evicted. Adding them would produce a number that means nothing and looks authoritative. The toolbar switches between them instead, and the axis relabels.

A missing history database costs the cost column, not the page. The bands, the scripts and the grid all come off the live instance. Reporting zero blocking for an instance nobody has been recording would be the one failure mode that made this page actively misleading, so the chart says no history configured and draws no bar at all.

The toolbar

Button What it does
All Every database. The default.
Blocked Only the ones where ADR is off.
One setting away Only the ones a single statement would fix.
Partial Only the ones running optimized locking without RCSI.
Full Only the ones already finished.
No answer Only the ones that could not be asked.
Blocking / Lock waits / Escalations What the bar measures.
Refresh Re-read now.

A band button with nothing behind it is disabled. All stays pressable.

Blocking is the default and comes from the last 30 days of history, which is what the blocking tracker keeps by default. Lock waits and Escalations come from the DMV and cover however long the instance has been up.

Reading the grid

Column What it is
Database The database name.
Readiness The band, in words, with its colour swatch.
ADR Whether Accelerated Database Recovery is on.
Optimized locking Whether optimized locking is on.
RCSI Whether read committed snapshot isolation is on.
Blocking time Blocking recorded for this database over the history window.
Blocking events How many blocking events were recorded.
Worst chain The longest blocking chain seen.
Last blocked When blocking was last recorded.
Lock waits Lock waits from the DMV, since instance start.
Lock wait time Total lock wait time from the DMV.
Escalations Lock escalations from the DMV.
Recovery model The recovery model.
State Online, offline, restoring and so on.
The grid, with the three settings as their own columns
ADR, Optimized locking and RCSI each get a column, so the chain is readable without going back to the chart.

System databases and database snapshots are not shown.

Right-click actions

Item What it gives you
Copy enable script The three statements for this database, in order, all commented out, each with what it costs.
Copy settings check script What the instance itself reports about the three settings.
Copy lock pressure script The lock waits and escalations behind the DMV columns.
Copy database name Just the name.

The enable script

Every statement is commented out, and each is annotated with what it actually does. The three steps are not equivalent and the script says so:

1. Accelerated Database Recovery. Optimized locking cannot be enabled until this is on. It needs exclusive access to the database, and it adds a persistent version store that occupies space in the database from then on.

2. Optimized locking itself. Reversible in a statement, and the least disruptive of the three.

3. Read committed snapshot isolation. This one is not a tuning knob. It changes what readers see. Code that relied on read committed blocking behaviour to serialise itself will stop doing so. Row versions move into tempdb, which has to have the room. WITH ROLLBACK IMMEDIATE kills every open connection to the database and rolls their transactions back. Treat this as an application change with a test pass behind it, not as the last line of a script somebody copied off a monitoring report.

For a read only database the script warns that the statements will not run until it is made writable. For a database that could not be read, nothing is offered, because nothing about its settings is known. For a database already at Full, the script says there is nothing to do, and adds that if it is still blocking heavily then optimized locking is not the answer to it.

How to read the report

  1. Check whether the bars are there at all. If the chart says no history is configured, the bands are still correct but there is no measured cost to prioritise by.
  2. Read the Blocked band first. Those databases cannot enable anything until ADR goes on, and ADR is the change with the real cost attached.
  3. Then One setting away, sorted by the bar. That is the shortlist: one statement each, ordered by how much blocking they have actually suffered.
  4. Look at Partial. Optimized locking is on and RCSI is not, so half the feature is idle. That is an application decision rather than a maintenance one.
  5. Read No answer. A database that could not report its settings is not a database that is fine.
  6. Switch the measure. If blocking history and lock waits disagree about which database is worst, they are measuring different windows, and both are true.
  7. Copy the enable script for the database you have chosen and read it somewhere else.

Common patterns

Every database Blocked. Nobody has turned on Accelerated Database Recovery anywhere. That is one decision covering the whole instance rather than a per-database backlog.

One database Full and the rest Blocked. Someone did this once, probably on a test database, and it never propagated. The Full row is proof the change works on this instance.

A large bar in the Blocked band. The database with the most to gain and the furthest to go. Start the change window conversation here.

Everything Full and blocking still high. Optimized locking is not the answer to this instance’s blocking. Go to Blocking Tree and Blocking by Hour by Day and find out what is actually holding locks.

Where the data comes from

  • sys.databases for the three settings, preferring is_optimized_locking_on where the build has it, because one column read beats a scalar call per database and it joins.
  • DATABASEPROPERTYEX where that column is absent.
  • sys.dm_db_index_operational_stats for lock waits, lock wait time and escalations.
  • DBHealthHistory.dbo.blockingOverTime for the blocking measurements, over the last 30 days.
  • sys.dm_os_sys_info for the instance start time, which is what the DMV window is measured from.

Nothing is stored by this page. The history it reads is written by the blocking tracker, not by this report.

Settings

Setting Default Values
OptimizedLockingWeight 0 0 = Blocking, 1 = Lock waits, 2 = Escalations

Per-user rather than per-instance. The band filter and the grid sort are not remembered.

Messages you may see

On anything older than SQL Server 2025:

The Optimized Locking report requires SQL Server 2025 or newer.

When the instance has only system databases:

There are no user databases on this instance. System databases and database snapshots are not shown.

When a refresh empties whatever the filter was sitting on:

No databases match the current filter. The All button has the rest.

Report Why you would go there
Blocking Tree Who is blocking whom right now.
Blocking by Hour by Day When the blocking this page measures actually happens.
Deadlock History Where blocking turns into deadlocks.
Waits Whether locking is really where this instance’s time goes.

Frequently asked questions

Does this tell me how much optimized locking would save? No, deliberately. The bar is blocking that already happened. Optimized locking removes a large class of lock waiting, not all blocking, and no report can honestly predict your saving.

Why can I not add the blocking seconds and the lock wait milliseconds? Because they come from different sources over unrelated windows: a wall clock month of history against however long the instance has been up. The toolbar switches between them rather than stacking them for exactly this reason.

Why is a database showing No answer? It is offline, restoring or read only, so DATABASEPROPERTYEX returned NULL. NULL is not off, and the report will not guess.

Do I have to turn on RCSI? Not to use optimized locking, but without it lock after qualification, which is half the feature, does not contribute. It is also the most disruptive of the three changes by a long way.

Why is there no button to enable it? Because ADR needs exclusive access and RCSI kills every open connection to the database. Neither belongs behind a click on a monitoring page.

The bars are missing entirely. Why? No history database is configured for this instance. The bands and the scripts still work; only the measured cost is unavailable, and the chart says so rather than drawing zeros.