SQL Server 2025 OPTIMIZED_LOCKING: What DBAs Need to Know

Every DBA has the same story. A perfectly fine workload starts hitting lock waits. You dig through sys.dm_tran_locks, find row locks escalating to page locks, page locks escalating to table locks, and suddenly a 200-millisecond transaction is blocking 40 sessions because the lock manager decided to go nuclear. You tune the query, add an index, maybe throw ROWLOCK hints at it, and move on until the next fire.

SQL Server 2025 takes a different approach. Instead of making you fight the lock manager, it changes how the lock manager thinks. The feature is called OPTIMIZED_LOCKING, and it is the most significant change to SQL Server's concurrency model since Read Committed Snapshot Isolation shipped in 2005.

The Problem OPTIMIZED_LOCKING Solves

Traditional SQL Server locking follows a conservative model: acquire a lock before touching a row, hold that lock until the transaction commits or rolls back. For a simple UPDATE that modifies 10 rows, the engine acquires 10 exclusive row locks (or fewer if lock escalation kicks in) and holds all of them for the entire duration of the transaction — even if the actual row modification took microseconds.

This creates two problems that compound under load:

• Lock memory pressure. Every lock consumes memory in the lock manager's hash table. A million-row batch update can consume hundreds of megabytes just in lock structures, stealing memory from the buffer pool where it actually helps query performance.
• Lock duration. Locks held for the full transaction lifetime create serialization points. Long-running transactions — even those doing trivial work — block other sessions from touching the same rows, pages, or tables.

OPTIMIZED_LOCKING attacks both problems simultaneously through two mechanisms: Lock After Qualification (LAQ) and Transaction ID (TID) locking.

Lock After Qualification (LAQ)

In traditional locking, SQL Server acquires a lock before evaluating whether a row qualifies for an UPDATE or DELETE. Consider this statement:

UPDATE Orders
SET Status = 'Shipped'
WHERE CustomerID = 42 AND Status = 'Processing';

Without LAQ, the engine scans the index, finds rows for CustomerID 42, acquires an exclusive lock on each one, then checks whether Status = 'Processing'. If the row does not qualify, the lock is released — but the lock was still acquired, contended for, and released. Under concurrency, those unnecessary lock acquisitions create wait chains.

With LAQ enabled, the engine evaluates the full predicate first. Only rows that actually qualify for modification get locked. Rows that do not match are never locked at all. The reduction in lock acquisitions is proportional to the selectivity of your predicates — the more rows you scan but do not modify, the bigger the win.

Transaction ID (TID) Locking

TID locking is the more radical change. Instead of holding row-level exclusive locks for the duration of the transaction, SQL Server stamps each modified row with the transaction's ID and releases the row lock immediately. Other transactions that need to read or modify the same row check the TID stamp:

• If the stamped transaction has committed, the row is available — no waiting.
• If the stamped transaction is still active, the requesting session takes a lightweight lock on the transaction ID itself rather than on the row.

The practical effect: locks are held for the duration of the individual row operation, not the entire transaction. A transaction that modifies 10,000 rows no longer holds 10,000 concurrent row locks — it modifies each row, stamps it, and moves on. The lock manager's memory footprint drops dramatically, and lock escalation becomes far less likely to trigger.

How to Enable OPTIMIZED_LOCKING

OPTIMIZED_LOCKING is a database-scoped configuration option. It is off by default — you have to opt in:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_LOCKING = ON;

Check the current state:

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

You can turn it off without downtime if you encounter regressions:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_LOCKING = OFF;

Prerequisites — What Must Be True First

OPTIMIZED_LOCKING has two hard prerequisites that are easy to overlook:

1. Accelerated Database Recovery (ADR) must be enabled. TID locking depends on ADR's persistent version store (PVS) to maintain row versions. Without ADR, the engine has no mechanism to resolve TID stamps against committed/uncommitted transaction state. Enable it with:

   ALTER DATABASE YourDatabase SET ACCELERATED_DATABASE_RECOVERY = ON;

2. READ_COMMITTED_SNAPSHOT must be ON. RCSI provides the snapshot infrastructure that LAQ uses to evaluate predicates without acquiring locks. If your database is still using plain READ COMMITTED (locking-based), enable RCSI first:

   ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

   Note: This requires exclusive database access during the ALTER. Schedule it during a maintenance window.

If either prerequisite is missing, the ALTER DATABASE SCOPED CONFIGURATION will succeed but OPTIMIZED_LOCKING will silently fall back to traditional locking behavior. Always verify both are active before testing.

Monitoring OPTIMIZED_LOCKING with DMVs

You need to confirm the feature is actually working, not just enabled. These DMV queries give you visibility:

Check lock counts before and after

-- Run under representative workload, compare before/after enabling
SELECT resource_type, request_mode,
       COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
GROUP BY resource_type, request_mode
ORDER BY lock_count DESC;

After enabling OPTIMIZED_LOCKING, you should see a significant drop in KEY and RID lock counts for write-heavy workloads.

Monitor TID waits

SELECT wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%TID%'
ORDER BY wait_time_ms DESC;

TID-related wait types appear when transactions contend on the same modified rows. If TID wait times are high relative to your previous LCK_M_X waits, the feature is working but you still have a concurrency hotspot — the lock type changed but the contention pattern did not.

Verify ADR persistent version store health

SELECT pvs_off_row_page_count,
       current_aborted_transaction_count,
       aborted_version_cleaner_start_time,
       aborted_version_cleaner_end_time
FROM sys.dm_tran_persistent_version_store_stats
WHERE database_id = DB_ID();

If pvs_off_row_page_count climbs without stabilizing, your PVS cleanup is falling behind. This usually indicates long-running transactions holding version store references — the same problem that causes tempdb version store bloat in RCSI, now moved to in-database PVS pages.

Lock Hint Interactions

This is where things get subtle. Existing lock hints in your queries interact with OPTIMIZED_LOCKING in ways that can negate the benefit:

• ROWLOCK, PAGLOCK, TABLOCK — These hints force the specified lock granularity. When OPTIMIZED_LOCKING is on, these hints override TID locking and force the traditional lock-and-hold behavior for those statements. If your codebase is peppered with WITH (ROWLOCK), you are opting out of the optimization for every hinted query.
• HOLDLOCK / SERIALIZABLE — Range locks required by serializable isolation are not affected by OPTIMIZED_LOCKING. If you use serializable isolation or HOLDLOCK hints, those queries still acquire and hold range locks.
• UPDLOCK — Update locks taken with explicit hints are held for the full transaction duration regardless of OPTIMIZED_LOCKING. This is by design — UPDLOCK exists precisely to hold a lock for the full transaction.
• XLOCK — Exclusive lock hints similarly override TID locking. The lock is acquired and held traditionally.

Action item: Before enabling OPTIMIZED_LOCKING, audit your application code and stored procedures for lock hints. Every hint you find is a potential bypass of the optimization. Remove lock hints that were added as workarounds for escalation or blocking problems — OPTIMIZED_LOCKING may make them unnecessary and counterproductive.

Performance Implications — The Good and the Tricky

The good

• Reduced lock escalation. Since row locks are released immediately after modification, the 5,000-lock threshold that triggers escalation to table locks is rarely reached. Batch operations that previously required OPTION (MAXDOP 1) or artificial batch splitting may run without escalation.
• Lower lock memory. The lock manager's memory footprint drops proportional to the average locks held per transaction. On OLTP systems with thousands of concurrent short transactions, this frees buffer pool memory.
• Fewer blocking chains. The lock-and-release pattern means Transaction A does not block Transaction B unless both are modifying the same row at the same instant. Sequential row modifications within a transaction no longer create a growing exclusion zone.

The tricky

• PVS storage growth. TID locking depends on ADR's persistent version store. Write-heavy workloads generate more versioned rows, and PVS cleanup runs asynchronously. If your workload has long-running read transactions that hold version store references, PVS can grow unbounded. Monitor pvs_off_row_page_count and set up alerts.
• Changed deadlock patterns. OPTIMIZED_LOCKING does not eliminate deadlocks — it changes when and where they occur. Deadlocks that previously happened at the row lock level may now appear as TID deadlocks. Your deadlock analysis scripts need to account for new wait resource types.
• Lock hint audit burden. If your application relies heavily on explicit lock hints, enabling OPTIMIZED_LOCKING without removing those hints gives you the worst of both worlds — the overhead of PVS versioning plus traditional locking on hinted queries.

When NOT to Enable OPTIMIZED_LOCKING

OPTIMIZED_LOCKING is not a universal win. Skip it in these scenarios:

• Read-heavy workloads with minimal write contention. If your blocking problems are not lock-related (they are PAGEIOLATCH or RESOURCE_SEMAPHORE waits instead), OPTIMIZED_LOCKING adds PVS overhead without addressing the actual bottleneck.
• Databases that cannot enable ADR. Some workloads — particularly those with heavy tempdb usage or specific transaction log patterns — perform worse with ADR enabled. If ADR degrades your workload, OPTIMIZED_LOCKING is off the table.
• Applications that depend on lock-based isolation semantics. Code that uses lock ordering as a concurrency control mechanism (common in financial systems) may break subtly when locks are no longer held for the full transaction. Test thoroughly before enabling in these environments.

A Practical Rollout Plan

1. Baseline your lock metrics. Run the DMV queries above under representative load. Record lock counts, lock memory, and blocking session counts.
2. Enable ADR and RCSI if not already active. Monitor for 48 hours to isolate any impact from these changes alone.
3. Audit and remove unnecessary lock hints. Every ROWLOCK, PAGLOCK, XLOCK hint in your codebase should be justified. Remove any that were added as blocking workarounds.
4. Enable OPTIMIZED_LOCKING on a non-production replica first. Run your load test suite and compare lock counts, TID waits, and PVS growth against the baseline.
5. Enable in production during low-traffic window. The setting takes effect immediately — no restart required.
6. Monitor for 7 days. Watch for PVS growth, new deadlock patterns, and any regression in transaction throughput. Keep the ALTER ... SET OPTIMIZED_LOCKING = OFF command ready.

The Bottom Line

OPTIMIZED_LOCKING is the most consequential change to SQL Server's concurrency engine in 20 years. LAQ eliminates unnecessary lock acquisitions. TID locking eliminates unnecessary lock duration. Together, they address the two root causes of most SQL Server blocking problems.

But it is not a switch-flip fix. You need ADR running cleanly, RCSI enabled, lock hints audited, and PVS monitoring in place before you turn it on. The DBAs who treat it as a careful infrastructure change will see real throughput improvements. The ones who enable it on a Friday afternoon will learn about PVS growth the hard way.

If your SQL Server environment is running 2025 and you are still fighting lock escalation and blocking chains, start with a free assessment. We will baseline your lock profile and tell you whether OPTIMIZED_LOCKING is the right move — or whether the real problem is somewhere else entirely.