SQL Server 2025 Performance: Optimized Locking, Optimized sp_executesql and OPPO, Tested

The Three Features at a Glance

Feature	What it fixes	Default	Prerequisite
Optimized Locking	Blocking between writers, lock memory, lock escalation	OFF	Accelerated Database Recovery (ADR); RCSI for the full effect
Optimized sp_executesql	Compile storms: many sessions compiling the same statement at once	OFF	None, database-scoped setting
OPPO (Optional Parameter Plan Optimization)	One bad cached plan for WHERE (col = @p OR @p IS NULL)	ON at compatibility level 170	Compatibility level 170

The defaults were read straight from sys.database_scoped_configurations and DATABASEPROPERTYEX on a freshly created database. The important consequence: OPPO arrives automatically the moment you raise a database to compatibility level 170. The other two do nothing until you turn them on.

1. Optimized Locking

The Problem: Locks Held Until Commit

In the classic locking model, every row a transaction modifies keeps its exclusive key lock until the transaction commits or rolls back. A transaction that updates 3,000 rows holds 3,000 key locks plus intent locks on every touched page. That costs lock memory, pushes the statement toward the lock escalation threshold, and every other session that merely scans past one of those rows has to wait.

Optimized locking attacks this with two independent mechanisms: TID locking and Lock After Qualification (LAQ).

Mechanism 1: Transaction ID (TID) Locking

With ADR enabled, every row version carries the ID of the transaction that last modified it. Optimized locking uses that: the row lock is taken while the row is being modified and released immediately afterwards. What the transaction holds until commit is a single lock on its own transaction ID. Anyone who needs a modified row waits on that TID instead of on the row.

The same UPDATE ... WHERE Id <= 3000 inside an open transaction, measured with sys.dm_tran_locks:

resource_type	request_mode	Optimized locking OFF	Optimized locking ON
KEY	X	3,000	0
PAGE	IX	84	0
OBJECT	IX	1	1
DATABASE	S	1	1
XACT	X	0	1
Total		3,086	3

From 3,086 locks down to 3. The lock count no longer grows with the number of modified rows, so lock memory stays flat and the escalation threshold is hardly ever reached by row count.

A session that has to wait for a row modified by someone else now shows new wait types. Both were captured from sys.dm_os_waiting_tasks during the tests:

• LCK_M_S_XACT_MODIFY: a writer waits for the transaction that modified the row. The resource description reads xactlock xdesIdLow=... mode=X UnderlyingResource keylock ..., so the original key is still visible.
• LCK_M_S_XACT_READ: a reader under locking READ COMMITTED (RCSI off) waits for the modifying transaction.

Mechanism 2: Lock After Qualification (LAQ)

TID locking alone does not stop a writer from blocking another writer that scans past its row. To check whether a row qualifies for an UPDATE or DELETE, the classic engine takes an update (U) lock on every row it reads, and it waits on rows that are locked by someone else, even if the row would not qualify at all.

LAQ changes the order: the predicate is evaluated against the latest committed version of the row, without a lock. Only rows that actually qualify get locked. LAQ requires READ_COMMITTED_SNAPSHOT (RCSI) on the database.

The test: session A updates row Id = 1 and keeps its transaction open. Session B updates a completely different row (Id = 15000), but through a non-sargable predicate, so it has to scan the table and read row 1 on the way:

-- Session A
BEGIN TRAN;
UPDATE dbo.T SET Val = 99 WHERE Id = 1;

-- Session B (5 second timeout)
UPDATE dbo.T SET Val = 77 WHERE Grp + Id = 15000;

Configuration	Session B
Optimized locking OFF, RCSI OFF	Blocked (timeout after 5 s)
Optimized locking ON, RCSI OFF (TID locking only)	Blocked (timeout after 5 s)
Optimized locking ON, RCSI ON (TID + LAQ)	Not blocked, 1 row updated in 12 ms

That is the core message of optimized locking: the blocking reduction between writers comes from LAQ, and LAQ needs RCSI. Optimized locking without RCSI saves lock memory and escalations, but the blocking chain in this test stayed exactly as it was.

Where LAQ Does Not Help (Measured)

Same setup, optimized locking and RCSI on, session A still holding row 1:

Session B	Result
Default READ COMMITTED (RCSI)	Not blocked
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	Blocked, LCK_M_S_XACT_MODIFY
WITH (READCOMMITTEDLOCK)	Blocked, LCK_M_S_XACT_MODIFY
WITH (UPDLOCK)	Blocked, LCK_M_S_XACT_MODIFY
Updates the same row (Id = 1)	Blocked, LCK_M_S_XACT_MODIFY (correct, real conflict)
RCSI off: plain SELECT of row 1	Blocked, LCK_M_S_XACT_READ

Higher isolation levels and explicit locking hints switch LAQ off for that statement. Code that is full of UPDLOCK or READCOMMITTEDLOCK hints (common in queue tables and "select then update" patterns) will keep its blocking behavior. That is by design: those hints ask for exactly the locks LAQ avoids.

Switching It On

-- 1. ADR is mandatory
ALTER DATABASE SalesDB SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;

-- 2. RCSI for Lock After Qualification
ALTER DATABASE SalesDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- 3. Optimized locking
ALTER DATABASE SalesDB SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE;

-- Check
SELECT DATABASEPROPERTYEX('SalesDB', 'IsOptimizedLockingOn') AS IsOptimizedLockingOn;

Skip step 1 and SQL Server refuses:

Optimized Locking cannot be enabled for this database because Accelerated
Database Recovery is not enabled. Enable Accelerated Database Recovery and try again.

The dependency also works the other way round. ADR cannot be switched off while optimized locking is on:

Optimized Locking is enabled for this database. To disable Accelerated Database
Recovery, disable Optimized Locking and try again.

The Real Risk Is Not Optimized Locking, It Is RCSI and ADR

Optimized locking itself is low-risk. The two prerequisites are where the homework is:

• RCSI changes read semantics. Readers no longer wait for writers, they read the last committed version. Application logic that silently relied on a read blocking until a concurrent write finished (read a balance, then update it, without UPDLOCK) can produce different results. This is the classic RCSI review, see SQL Server Isolation Levels Explained. Microsoft's documentation also describes edge cases where concurrent updates under LAQ can end differently than under the old blocking behavior. Read that section before you switch production on.
• ADR moves row versions into the user database. With ADR on, versions live in the persistent version store (PVS) inside the database, not in tempdb. Long-running transactions keep the PVS from being cleaned up, and the data file grows. Monitor sys.dm_tran_persistent_version_store_stats. We covered how a single forgotten transaction can pin a version store in The Real Fix for an Orphaned Transaction That Fills TempDB; the same logic applies to the PVS.

2. Optimized sp_executesql

The Problem: Compile Storms

After a restart, a failover, a plan cache flush or an application deployment, the plan cache is cold. If hundreds of sessions send the same parameterized statement at the same moment (typical for ORMs such as Entity Framework, and for any ADO.NET SqlCommand with parameters, which goes through sp_executesql), every session that finds no plan compiles its own. CPU spikes, compile memory runs short, and sessions queue on RESOURCE_SEMAPHORE_QUERY_COMPILE.

Stored procedures have never had this problem to the same degree: compilation of a procedure is serialized with a compile lock, so one session compiles and the others wait for the result. Optimized sp_executesql brings that same behavior to sp_executesql.

Measured: 40 Sessions, One New Statement

The test fires 40 sessions in parallel, all executing the same not-yet-cached statement (an eight-way join over catalog views, so the compile is not trivial) through sp_executesql. It counts the delta of optimizations in sys.dm_exec_query_optimizer_info and the matching plan cache entries. Three runs each:

OPTIMIZED_SP_EXECUTESQL	Run 1	Run 2	Run 3
OFF: optimizations / cache entries	40 / 16	40 / 17	40 / 16
ON: optimizations / cache entries	1 / 1	1 / 1	1 / 1

Without the setting, every one of the 40 sessions ran its own full optimization and the cache ended up with 16 to 17 Prepared entries for the identical text. With the setting, exactly one optimization ran, the other 39 sessions waited for it and reused the plan. On a real system with hundreds of sessions and expensive queries, that is the difference between a CPU spike right after a failover and a calm restart.

Switching It On

USE SalesDB;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;

SELECT name, value
FROM   sys.database_scoped_configurations
WHERE  name = 'OPTIMIZED_SP_EXECUTESQL';

It is a database-scoped configuration, so it applies to statements executed in the context of that database. It does not need a specific compatibility level.

3. OPPO: Optional Parameter Plan Optimization

The Problem: The Catch-All Search Procedure

Almost every application has one:

CREATE OR ALTER PROCEDURE dbo.GetOrders
    @CustomerId int = NULL
AS
SELECT OrderId, CustomerId, Amount
FROM   dbo.Orders
WHERE  (CustomerId = @CustomerId OR @CustomerId IS NULL);

A value should use the index on CustomerId; NULL means "all rows" and should scan. But there is only one cached plan, and it is built for whichever value comes first. That is parameter sniffing in its purest form, and the classic workarounds are OPTION (RECOMPILE) (compile on every call) or dynamic SQL.

Measured: Logical Reads on 200,000 Rows

Scenario	@CustomerId = 42 (6 rows)	@CustomerId = NULL (200,000 rows)
OPPO OFF, first call with 42	371	600,353
OPPO OFF, first call with NULL	9,168	9,168
OPPO OFF, OPTION (RECOMPILE)	20	n/a (compiles every call)
OPPO ON	20	8,730

Without OPPO you pick your poison: sniffed with a value, the "all rows" call does 600,353 logical reads, a key lookup for every single row. Sniffed with NULL, every single-customer lookup scans the whole table. With OPPO, each case gets the plan it should get: 20 reads for the customer (the same as RECOMPILE, without recompiling) and a clustered scan for NULL.

How It Works: A Dispatcher and Two Variants

OPPO reuses the multi-plan infrastructure that SQL Server 2022 introduced for Parameter Sensitive Plan optimization (PSP). The cached plan of the procedure becomes a dispatcher that tests the optional parameter at runtime and routes to one of two query variants. In the showplan XML it looks like this:

<Dispatcher>
  <OptionalParameterPredicate>
    <Predicate>
      <ScalarOperator ScalarString="[@CustomerId] IS NULL"> ...
    </Predicate>
  </OptionalParameterPredicate>
</Dispatcher>

Each variant is compiled and cached separately, with a hint appended to its statement text:

... WHERE (CustomerId = @CustomerId OR @CustomerId IS NULL)
option (PLAN PER VALUE(ObjectID = 1573580644, QueryVariantID = 1,
        optional_predicate(@CustomerId IS NULL)))   -- Index Seek + Key Lookup

... option (PLAN PER VALUE(ObjectID = 1573580644, QueryVariantID = 2,
        optional_predicate(@CustomerId IS NULL)))   -- Clustered Index Scan

Query Store Side Effect: One Query, Three query_ids

In Query Store, the procedure statement shows up as a parent query plus one query per variant. sys.query_store_query_variant links them:

SELECT query_variant_query_id, parent_query_id, dispatcher_plan_id
FROM   sys.query_store_query_variant;

query_variant_query_id parent_query_id dispatcher_plan_id
---------------------- --------------- ------------------
                     6               5                  5
                     7               5                  5

The runtime statistics (executions, reads, duration) are recorded on the variant queries, not on the parent. Any reporting script or "top queries" dashboard that groups by query_id will suddenly show one procedure statement as two unrelated queries, and the parent with no executions at all. Join through sys.query_store_query_variant to aggregate them back. More on Query Store in general in Query Store Explained.

Switching It Off, If You Have To

-- For the whole database
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

-- For a single statement
SELECT OrderId FROM dbo.Orders
WHERE  (CustomerId = @CustomerId OR @CustomerId IS NULL)
OPTION (USE HINT('DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION'));

A small oddity from the test instance: CU7 accepts the hint, but it is not listed in sys.dm_exec_valid_use_hints. Do not use that DMV as the source of truth for which hints exist.

Rollout Order for an Existing System

1. Baseline first. Enable Query Store (read-write) on the old compatibility level and let it collect a representative period. Without a baseline, "it got slower" cannot be proven or disproven.
2. Optimized sp_executesql. Lowest risk, no prerequisites, immediate effect on cold-cache behavior. A good first candidate for ORM-heavy databases.
3. Compatibility level 170. This brings OPPO (and the rest of the new IQP features) automatically. Treat it as its own change with its own test window, separate from the version upgrade. Plan regressions show up here, and Query Store plan forcing is your rollback.
4. Optimized locking last. The feature itself is harmless, but it pulls in ADR and, for the real benefit, RCSI. Test the application under RCSI, size the data files for the persistent version store, and update blocking monitoring for the XACT wait types before production.

For the upgrade itself, see Migration to SQL Server 2025: Risks, Issues, and Safe Approach and the broader feature tour in SQL Server 2025: What's New for Developers and DBAs. To compare blocking before and after the switch, Get-sqmBlockingReport and Get-sqmWaitStatistics from sqmSQLTool give you a report for each state.

The Bottom Line

All three features address real, everyday problems, and all three delivered in the lab: 3,086 locks became 3, a blocked writer went through in 12 ms, 40 parallel compiles became one, and a 600,353-read worst case dropped to 8,730. But they differ a lot in how much homework they need.

Optimized sp_executesql is a setting you can switch on almost anywhere. OPPO comes for free with compatibility level 170, with the usual caveat that every optimizer change needs a baseline. Optimized locking delivers the biggest concurrency gain, but only together with RCSI, and RCSI is an application-behavior decision, not a server setting. Make that decision deliberately, and the new locking model pays off.