Enable Optimized Locking? Measure First, Then Decide

Optimized locking solves one particular conflict very well. So the question before turning it on is not "Is it good?" but "Do we have this conflict, and is there a cheaper way?" Using measurements from SQL Server 2025, this page shows when the switch pays off, what it costs and how to check that on your own load.

In 60 seconds

• Optimized locking needs ADR, and its main gain (lock after qualification) exists only with RCSI. Turning it on therefore means turning on three things. The mechanics are explained in Optimized locking explained.
• The gain: Writers that scan across other sessions' rows no longer block each other. In the lab, almost double the throughput in exactly this case, plus far fewer locks held and hardly any lock escalation.
• The costs come from ADR: for changes touching many rows, a quarter to two fifths less throughput, 2.3 to 3.5 times the CPU and just over three times as much log. For single-row changes, no difference was measurable.
• A suitable index was better: three times the throughput of optimized locking without an index, without any of the ADR costs.
• Hot rows: With many writers on the same row, throughput stayed the same, but the slowest writers became twice as slow.
• Decide by measuring: measurement window before, switches one at a time, measurement window after. The query for that is below.

What you buy and what you pay

We compared three configurations with four typical loads, 30 seconds each, three repetitions, on SQL Server 2025 CU10 with eight CPUs. We measured successful changes per second:

Four things can be read from this:

• The gain comes from one pattern only. Optimized locking earns its advantage where eight writers without an index run over all rows and wait for each other in the process. Without optimized locking, they waited together for about 205 seconds per 30-second run on LCK_M_U, that is, almost seven sessions at the same time on average. With optimized locking, nobody waited for a lock anymore; the new bottlenecks were page latches and the log.
• A single writer only pays the cost. Without competition, there is nothing to unblock. What remains is the ADR cost.
• ADR is expensive for set-based changes. There, ADR + RCSI came in 23 to 40 percent below the configuration with neither, needed 2.3 to 3.5 times the CPU per change and wrote just over three times as much log (412 instead of 129 KB per change with eight writers and a suitable key). The cause: ADR writes a version into the database for every changed row, and that version has to go to the log.
• Single-row changes don't notice any of this. For changes by primary key, all three configurations were within a few percent of each other, at about one millisecond of CPU per change, limited by writing to the log.

Then there are the gains that no throughput figure shows: In LAB-10, an UPDATE over 1,000 rows held 1,060 locks with ADR and RCSI and 3 with optimized locking. Fewer locks mean less lock memory and hardly any lock escalation. From telemetry of about 25,000 Azure SQL databases, Microsoft reports that about 81 percent of the locks are skipped for nonclustered indexes. Those are lock counts, not time values; Microsoft does not publish throughput or latency figures, and we found no independent benchmarks.

Does your workload benefit? Five questions

1. Do writers wait for writers that change other rows? In the waits, this shows up as LCK_M_U, often together with scans in the plans of the waiting UPDATE and DELETE statements. This is the case that optimized locking solves.
2. Do these changes scan because an index is missing? Then check the index first (next section).
3. Is there something in the code that prevents the gain? Lock hints such as UPDLOCK, HOLDLOCK or READCOMMITTEDLOCK, isolation levels other than READ COMMITTED, MERGE, OUTPUT to the client, columnstore indexes or plans with multiple accesses to the target table: lock after qualification (LAQ) doesn't apply there.
4. Do large transactions hold many locks, is there lock escalation? Then optimized locking helps even without waits, because it reduces lock memory and escalations. Exception: Right after a load or a rebuild with fill factor 100, even with optimized locking every first large run escalated in the lab, because the pages split (details).
5. Is RCSI already on, or acceptable for your application? Without RCSI, little of the gain remains. What RCSI changes in your code is covered in NOLOCK, RCSI or SNAPSHOT?.

If you find nothing at question 1 and nothing at question 4, optimized locking mainly buys you the ADR costs.

Index or optimized locking?

In the lab, it was the same table, the same data and the same statement:

UPDATE dbo.Scan SET v = v + 1 WHERE g = @g;   -- 8,000 rows in eight groups, each session changes its own group

Once with the clustered key on id only, so that every change has to read all rows, and once with the clustered key (g, id), so that every change reads only its own group.

The suitable access path removes the conflict at its root: No session reads the other sessions' rows anymore, so none of them waits. Optimized locking only lets the sessions read past each other without waiting; the work of reading other sessions' rows and the ADR costs remain.

That gives the order: access path first, then the switch. Optimized locking is the right choice when you may not change the code or the indexes, for example with vendor software, and the conflict of writers scanning other sessions' rows actually occurs.

The limit of this comparison: We changed the clustered key. An additional nonclustered index on g was not used by the optimizer at a selectivity of one eighth. A real additional index costs space, maintenance and sometimes key lookups; we didn't measure that.

Hot rows: same throughput, longer outliers

Four sessions increment the same counter, four more read it:

The writers' throughput stays the same, and so does their total wait time. With optimized locking, though, it is distributed more unevenly: The slowest five percent of the writers took twice as long, the slowest one percent almost three times as long. For applications with response-time commitments on such counters, sequence tables or status rows, that is a reason to measure beforehand. Microsoft doesn't describe this; in the sources we found only an experience report without measurements.

Our assumption about the cause, not verified: With optimized locking, a writer waits for the other writer's transaction, not for the row. After that transaction commits, it has to request the row again, and the ordered queue of a classic row lock doesn't exist there.

As a side note, the readers show what RCSI does: 47 times as many queries, with and without optimized locking. Whoever wants to relieve readers needs RCSI, not optimized locking.

Measure first: the measurement recipe

You judge a change best with the same measurement before and after, at the same time of load. The following query measures a time window: It remembers the counters, waits the configured time and then shows what happened in that window. Lock waits appear including the new LCK_M_S_XACT_* types, plus log and version store.

Query: Measurement window before and after

/* PSG Stack · SQL Server Locking · Query 4   Measurement window: What happened during a fixed time window? Lock waits (including the new XACT wait types),   lock and deadlock counters, log written and version store.   Run once before and once after a change, at a comparable time of load, and compare.    !!! WARNING — please read before running !!!   - Read-only. The query changes no data and no setting. It only creates small table     variables in your own session.   - It WAITS on purpose: the session stays open for @Seconds (WAITFOR DELAY) and returns     results afterwards. Cancelling is safe at any time.   - Waits and counters are server-wide; log and version store are measured only for the     database you run the query in. Other workload during the window is included.   - Permissions: VIEW SERVER STATE (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough),     plus a user in the database you measure.   - MAXDOP 1: every part runs serially and uses no additional cores.   - SQL Server 2017 and later. What a version does not know (version store DMV before 2019,     optimized locking before 2025) shows as n/a. Tested in the PSG lab on 2017, 2019, 2022 and 2025     (Nov 10, 2026), under load on 2025 CU10, also without sysadmin.    Results:     1  Switches of the current database (RCSI, ADR, optimized locking)     2  Waits in the window: locks, log and page latches only, largest first     3  Counters in the window: lock requests, lock waits, timeouts, deadlocks, escalations     4  Log and version store of the current database*/SET NOCOUNT ON; DECLARE @Seconds int = 60;   -- length of the measurement window in seconds (1 to 3600) DECLARE @w0 TABLE (wait_type nvarchar(60) PRIMARY KEY, waits bigint, ms bigint, signal_ms bigint);DECLARE @c0 TABLE (counter_name nvarchar(128) PRIMARY KEY, value bigint);DECLARE @w1 TABLE (wait_type nvarchar(60) PRIMARY KEY, waits bigint, ms bigint, signal_ms bigint);DECLARE @c1 TABLE (counter_name nvarchar(128) PRIMARY KEY, value bigint);DECLARE @log0 bigint, @log1 bigint, @pvs0 bigint, @pvs1 bigint, @t0 datetime2(3), @t1 datetime2(3);DECLARE @hasPvs bit = CASE WHEN OBJECT_ID(N'sys.dm_tran_persistent_version_store_stats') IS NULL THEN 0 ELSE 1 END;DECLARE @pvsSql nvarchar(400) = N'SELECT @p = persistent_version_store_size_kb FROM sys.dm_tran_persistent_version_store_stats WHERE database_id = DB_ID() OPTION (MAXDOP 1);';DECLARE @delay char(8); IF @Seconds NOT BETWEEN 1 AND 3600 BEGIN RAISERROR(N'@Seconds must be between 1 and 3600.', 16, 1); RETURN; END;SET @delay = CONVERT(char(8), DATEADD(second, @Seconds, CONVERT(datetime, 0)), 108); /* Before */SET @t0 = SYSUTCDATETIME();INSERT @w0SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_msFROM sys.dm_os_wait_statsWHERE wait_type LIKE N'LCK[_]M[_]%' OR wait_type IN (N'WRITELOG', N'PAGELATCH_EX', N'PAGELATCH_SH')OPTION (MAXDOP 1);INSERT @c0SELECT RTRIM(counter_name), cntr_valueFROM sys.dm_os_performance_countersWHERE (object_name LIKE N'%:Locks%' AND instance_name = N'_Total'       AND counter_name IN (N'Lock Requests/sec', N'Lock Waits/sec', N'Lock Timeouts (timeout > 0)/sec', N'Number of Deadlocks/sec'))   OR (object_name LIKE N'%:Access Methods%' AND counter_name = N'Table Lock Escalations/sec')OPTION (MAXDOP 1);SELECT @log0 = SUM(v.num_of_bytes_written)FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vJOIN sys.database_files AS f ON f.file_id = v.file_id AND f.type = 1OPTION (MAXDOP 1);IF @hasPvs = 1 EXEC sys.sp_executesql @pvsSql, N'@p bigint OUTPUT', @p = @pvs0 OUTPUT; WAITFOR DELAY @delay; /* After */SET @t1 = SYSUTCDATETIME();INSERT @w1SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_msFROM sys.dm_os_wait_statsWHERE wait_type LIKE N'LCK[_]M[_]%' OR wait_type IN (N'WRITELOG', N'PAGELATCH_EX', N'PAGELATCH_SH')OPTION (MAXDOP 1);INSERT @c1SELECT RTRIM(counter_name), cntr_valueFROM sys.dm_os_performance_countersWHERE (object_name LIKE N'%:Locks%' AND instance_name = N'_Total'       AND counter_name IN (N'Lock Requests/sec', N'Lock Waits/sec', N'Lock Timeouts (timeout > 0)/sec', N'Number of Deadlocks/sec'))   OR (object_name LIKE N'%:Access Methods%' AND counter_name = N'Table Lock Escalations/sec')OPTION (MAXDOP 1);SELECT @log1 = SUM(v.num_of_bytes_written)FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vJOIN sys.database_files AS f ON f.file_id = v.file_id AND f.type = 1OPTION (MAXDOP 1);IF @hasPvs = 1 EXEC sys.sp_executesql @pvsSql, N'@p bigint OUTPUT', @p = @pvs1 OUTPUT; /* 1 · Switches of the current database */DECLARE @switches nvarchar(max) = N'SELECT d.name AS database_name,       d.is_read_committed_snapshot_on AS rcsi,       d.snapshot_isolation_state_desc AS snapshot_isolation,       ' + CASE WHEN COL_LENGTH(N'sys.databases', N'is_accelerated_database_recovery_on') IS NULL                THEN N'N''n/a''' ELSE N'CONVERT(nvarchar(3), d.is_accelerated_database_recovery_on)' END + N' AS adr,       ' + CASE WHEN COL_LENGTH(N'sys.databases', N'is_optimized_locking_on') IS NULL                THEN N'N''n/a''' ELSE N'CONVERT(nvarchar(3), d.is_optimized_locking_on)' END + N' AS optimized_lockingFROM sys.databases AS dWHERE d.database_id = DB_ID()OPTION (MAXDOP 1);';EXEC sys.sp_executesql @switches; /* 2 · Waits in the window */SELECT w1.wait_type,       CASE WHEN w1.wait_type LIKE N'%XACT%' THEN N'optimized locking (XACT)'            WHEN w1.wait_type LIKE N'LCK%' THEN N'lock'            WHEN w1.wait_type = N'WRITELOG' THEN N'log write'            ELSE N'page latch' END AS kind,       w1.waits - ISNULL(w0.waits, 0) AS waits,       w1.ms - ISNULL(w0.ms, 0) AS wait_ms,       CONVERT(decimal(12, 2), (w1.ms - ISNULL(w0.ms, 0)) * 1.0 / NULLIF(w1.waits - ISNULL(w0.waits, 0), 0)) AS ms_per_wait,       CONVERT(decimal(12, 2), (w1.ms - ISNULL(w0.ms, 0)) / 1000.0 / NULLIF(DATEDIFF(millisecond, @t0, @t1) / 1000.0, 0)) AS avg_waitingFROM @w1 AS w1LEFT JOIN @w0 AS w0 ON w0.wait_type = w1.wait_typeWHERE w1.waits > ISNULL(w0.waits, 0)ORDER BY wait_ms DESCOPTION (MAXDOP 1); /* 3 · Counters in the window */SELECT c1.counter_name,       c1.value - ISNULL(c0.value, 0) AS in_window,       CONVERT(decimal(14, 2), (c1.value - ISNULL(c0.value, 0)) / NULLIF(DATEDIFF(millisecond, @t0, @t1) / 1000.0, 0)) AS per_secondFROM @c1 AS c1LEFT JOIN @c0 AS c0 ON c0.counter_name = c1.counter_nameORDER BY c1.counter_nameOPTION (MAXDOP 1); /* 4 · Log and version store of the current database */SELECT DB_NAME() AS database_name,       DATEDIFF(millisecond, @t0, @t1) / 1000.0 AS window_s,       CONVERT(decimal(14, 2), (@log1 - @log0) / 1048576.0) AS log_written_mb,       CASE WHEN @hasPvs = 0 THEN N'n/a' ELSE CONVERT(nvarchar(20), @pvs0) END AS pvs_before_kb,       CASE WHEN @hasPvs = 0 THEN N'n/a' ELSE CONVERT(nvarchar(20), @pvs1) END AS pvs_after_kbOPTION (MAXDOP 1);

Sample output

A 20-second window from the lab (SQL Server 2025 CU10, eight writers without a suitable index, ADR, RCSI and optimized locking on, synthetic data, run as a login with only VIEW SERVER PERFORMANCE STATE):

1 · Options
database_name  rcsi  snapshot_isolation  adr  optimized_locking
-------------  ----  ------------------  ---  -----------------
ol_full        1     OFF                 1    1

2 · Waits in the window
wait_type     kind        waits  wait_ms  ms_per_wait  avg_waiting
------------  ----------  -----  -------  -----------  -----------
PAGELATCH_SH  page latch  43157    46799         1.08         2.33
PAGELATCH_EX  page latch  20105    24980         1.24         1.24
WRITELOG      log write    2468     7411         3.00         0.37

3 · Counters in the window
counter_name                     in_window  per_second
-------------------------------  ---------  ----------
Lock Requests/sec                  6874207   342204.65
Lock Timeouts (timeout > 0)/sec          0        0.00
Lock Waits/sec                           0        0.00
Number of Deadlocks/sec                  0        0.00
Table Lock Escalations/sec               0        0.00

4 · Log and version store
database_name  window_s   log_written_mb  pvs_before_kb  pvs_after_kb
-------------  ---------  --------------  -------------  ------------
ol_full        20.088000          913.58         769416        348488

How to read it:

• Result 2: Not a single lock wait, but page latches and log. In our runs of the same load without optimized locking, almost seven sessions on average were waiting on LCK_M_U at this point. The bottleneck has moved; that is the gain. If new LCK_M_S_XACT_* rows appear after switching on, sessions are waiting for other transactions, for instance on hot rows.
• Result 3: Lock Waits and Table Lock Escalations should drop after the change. The number of lock requests doesn't necessarily drop, because short row locks still exist.
• Result 4: The log and the version store show the ADR costs. If the log per window rises markedly, check space, backup and replication. The version store may fluctuate as long as it shrinks again; if it grows from window to window, something is holding up cleanup.

In addition, every before-and-after measurement should include the response times of the most important operations from the application's point of view, including the slowest five percent, and the CPU of the instance. The extended event locking_stats reports escalations and how often lock after qualification was attempted, per database about every five minutes; so it also shows whether the gain applies at all.

The PoC in five steps

1. A test system with real load. A replay or a load test with the operations that wait today. On an empty system you see only the costs. SQL Server 2025 with at least CU9: Microsoft fixed crashes with optimized locking in combination with change tracking (CU3) and with forwarded records in heaps (CU9).
2. A measurement window with nothing switched on. Query 4 at the load times, plus response times and CPU.
3. Switch on one at a time. First ADR, measure. Then RCSI, measure. Then optimized locking, measure. Only this way do you know which step costs what and brings what. In our lab, ADR + RCSI cost up to 40 percent, and only optimized locking more than made up for it in the suitable case.
4. Check results, not only times. RCSI changes what triggers, validation logic and read-then-write code see; optimized locking changes the order of changes in one edge case. Both are covered in NOLOCK, RCSI or SNAPSHOT? and Optimized locking explained. Test these spots with two sessions.
5. Check monitoring and deadlock analysis. Does your monitoring recognize the wait types LCK_M_S_XACT_READ, LCK_M_S_XACT_MODIFY and LCK_M_S_XACT? Does your deadlock analysis read the <xactlock> element? If not, blocking looks as if it had disappeared after the switch.

How to recognize a regression

Switching in production

All three switches need a moment in which no other connection is active in the database:

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT = ON WITH ROLLBACK IMMEDIATE;ALTER DATABASE [MyDatabase] SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE;

What we saw in the lab on SQL Server 2017 to 2025:

• Without an option, the statement waits, for every other connection in the database, even an idle one without a transaction. If the other session had a transaction, ALTER DATABASE waited beyond its commit until the connection was closed.
• WITH NO_WAIT didn't help. According to the documentation, the statement is supposed to fail immediately with it. In the lab, on all versions and for all three switches, it waited as long as without the option, without any message. We haven't established the cause.
• WITH ROLLBACK IMMEDIATE disconnects the other connections and rolls back open transactions. In the lab, the switch was flipped after about three seconds.

So plan a maintenance window in which aborts are acceptable. Going back works in reverse order with OFF: first optimized locking, then RCSI as needed, then ADR. The other way round, SQL Server refuses (Msg 12134, see Optimized locking explained). Optimized locking is available in SQL Server 2025 Enterprise and Standard editions, not in Express; in Azure SQL Database it is always on.

What your tools don't know yet

Optimized locking changes what diagnostic tools get to see: new wait types, locks on XACT instead of rows, a new element in the deadlock report. Some community tools already know this, others don't yet, and the state changes with every version. So check your own monitoring before you switch: Does it show a session waiting on LCK_M_S_XACT_MODIFY as blocked? Does it resolve the blocker? Does it evaluate a deadlock with <xactlock>? The easiest way to test this is with the lock and switch overview from the overview page and two sessions on a test system.

Method and limits

The workload measurement ran on Nov 10, 2026 on SQL Server 2025 CU10 (17.0.5035.5), Developer Edition in a Linux container with eight CPUs, 12 GB of memory for SQL Server and MAXDOP 1, data and log on a shared SATA SSD. 63 runs of 30 seconds each, three repetitions per data point in random order, medians; all runs without errors, deadlocks or table escalations, and after each run the totals matched the successful changes. Eight runs overlapped with another experiment on the same host; repeated alone, they came out 3 percent lower to 14 percent higher, which changed nothing about the conclusions. We measured switching in a separate experiment on SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU10. Limits:

• Synthetic loads, a closed load loop without an arrival rate, one host. The log was the bottleneck in several cases and limits all configurations equally; on faster storage, the gaps can turn out differently.
• No long-term measurement: We didn't measure how the version store and cleanup behave over hours with a long read transaction.
• The cause of the longer outliers on hot rows is an assumption.
• We made the index comparison with a different clustered key; the cost of a real additional index is not measured.
• We didn't measure Azure SQL Database, Managed Instance or Windows.
• Percentages apply to these loads. They are not a forecast for your system.

Further reading

• Microsoft Learn: Optimized locking; Accelerated database recovery management and troubleshooting; ALTER DATABASE SET options; Deadlocks Guide.
• Dimitri Furman (Microsoft): Introducing optimized locking v2 (Nov 18, 2025).
• Louis Tochon (dbi services): SQL Server 2025 – is optimized locking really working? Ask the Extended Events (2026).
• At PSG: Optimized locking explained, ADR: fast rollback and what it costs, NOLOCK, RCSI or SNAPSHOT?, SQL Server upgrade: faster or slower?.

Planning the upgrade to SQL Server 2025?

Then the question of optimized locking comes up anyway. In a performance analysis, we measure on your load whether the conflict that optimized locking solves occurs, whether an index removes it more cheaply and what ADR costs you. The SQL Server Health Check gives the overall view of the instance.

More locking topics