iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Optimized locking is an opt-in, per-database feature in SQL Server 2025 that changes how the engine protects rows a transaction modifies. It can hold fewer locks for the length of a transaction, lower lock memory use, reduce lock escalation, and cut some blocking. It does not remove every lock, it does not guarantee that an application will never block, and under some concurrency patterns it returns a different result than the older locking model would. This guide explains the two mechanisms behind the feature, how to turn it on and confirm it is working, and where it does not apply.
What optimized locking changes
Optimized locking has two components. Each one addresses a different part of the cost of locking during data modification, and each has its own rules.
Transaction ID (TID) locking
Without optimized locking, an update that changes many rows typically keeps a row-level exclusive lock on every changed row until the transaction commits or rolls back. With TID locking, the engine assigns each transaction a unique transaction identifier and labels each modified row with the last TID that changed it. The transaction then holds a single exclusive lock on its TID, which protects all of the rows it changed. Individual row locks can be released as each row is updated.
Microsoft Learn’s optimized locking article uses an update affecting 1,000 rows to illustrate the difference. Without the feature, that statement can hold 1,000 exclusive row locks until the transaction ends. With it, row locks are released as rows are updated, and one exclusive TID lock remains until the transaction ends. This is a worked explanation of the mechanism, not a benchmark, and it is not a guarantee of any particular lock count in your workload.
#1 Best Overall
Lock after qualification (LAQ)
LAQ changes how a data modification statement evaluates its predicate. Under Read Committed with Read Committed Snapshot Isolation (RCSI) enabled, the engine can read the latest committed version of a row and test the predicate against it before taking an update lock. If the row matches, the engine acquires the exclusive lock needed for the modification, performs the change, and releases the row lock after the update. If the row does not match, the scan moves on without locking it.
The practical effect is that an update or delete does not queue behind rows it was never going to change. That is where most of the blocking reduction comes from.
The stated purpose
Microsoft’s own summary of the feature is: “Optimized locking offers an improved transaction locking mechanism to reduce lock blocking and lock memory consumption for concurrent transactions.” The sentence appears in the Microsoft Learn article “Optimized locking – SQL Server” (last updated November 24, 2025) and in the SQL Server 2025 “What’s new” overview. Neither names an individual author.
Availability and prerequisites
Availability depends on the product and platform, and the defaults differ.
- SQL Server 2025 (17.x): optimized locking is set per user database and is off by default.
- SQL Server 2022 and earlier: the feature is not supported, according to the Microsoft feature support table.
- Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric: these have their own availability and default behavior, listed separately in the same Microsoft documentation. Check those platform pages rather than assuming the on-premises SQL Server 2025 setting applies.
Two prerequisites matter for any on-premises deployment:
- Accelerated database recovery (ADR) must be enabled before optimized locking can be enabled. The reverse also applies: disable optimized locking before you disable ADR.
- RCSI is not required for TID locking, but LAQ only operates when RCSI is on. Microsoft recommends RCSI with READ COMMITTED to get the most benefit.
Enabling and verifying the setting
- Confirm that ADR is enabled for the target database.
- Run
ALTER DATABASE [YourDatabase] SET OPTIMIZED_LOCKING = ON;in the context of the target database’s server instance. - Check the state in
sys.databasesby reviewing theis_accelerated_database_recovery_on,is_read_committed_snapshot_on, andis_optimized_locking_oncolumns. - Alternatively, run
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');. The function returns 0 when the feature is disabled, 1 when it is enabled, and NULL when it is unavailable.
A database that shows optimized locking as on but RCSI as off will get TID locking but no LAQ. Treat that combination as a partial deployment, not a misconfiguration to ignore.
Where the feature does not help
Optimized locking targets row and page locks acquired by data modification (DML). Several categories of locking and several statement patterns fall outside it.
Recommended Free Tools
Other lock types are unchanged
Microsoft states that the feature reduces or eliminates row and page locks acquired by DML. It has no effect on other database and object locks, such as schema locks. A workload that blocks on schema modification, or that holds long transactions, will not be fixed by this feature.
Statements where LAQ is not used
Microsoft documents the following cases in which LAQ does not apply:
- LAQ heuristics decide to disable it for a given statement.
- Locking hints are used, including
UPDLOCK,READCOMMITTEDLOCK,XLOCK, orHOLDLOCK. - The isolation level is anything other than READ COMMITTED.
- RCSI is disabled for the database.
- The modified table has a columnstore index.
- The DML statement assigns to a variable.
- The DML statement has an
OUTPUTclause that returns a result set or inserts into a table variable. - More than one index seek or scan reads the modified rows.
- The statement is a
MERGE.
Hints deserve particular attention. Code that has accumulated UPDLOCK or XLOCK over the years can silently opt out of LAQ, so review those hints when you test the feature.
Skip Index Locks is a separate optimization
Skip Index Locks is documented separately. It covers certain INSERT-into-heap and UPDATE cases and has its own exclusions, including DELETE statements, some updates to heap forwarding pointers, modified LOB columns, and rows on pages split within the same transaction. Do not assume that a Skip Index Locks exclusion means LAQ is also excluded, or the reverse. The two rule sets are documented separately.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →tempdb, temporary tables, and secondary replicas
Optimized locking is not used for modifications in tempdb or temporary tables. It is also not used on read-only secondary replicas, where DML cannot run in the first place.
The ordering change you need to test
Less blocking is not free. Because LAQ tests predicates against the latest committed row version, a concurrent statement can skip a row that it would previously have waited for and then updated.
Microsoft’s example uses two transactions. T1 updates a row from b = 1 to b = 2. T2 runs an update with the predicate b = 2. Without LAQ, T2 waits for T1 to finish and then finds the updated row. With LAQ, T2 evaluates the latest committed version available to its predicate, sees b = 1, skips the row, and completes without waiting. The final value of the row differs between the two behaviors.
The change is a trade-off between waiting and which row qualifies. It is not a defect, and most workloads will not depend on that ordering. Applications that assumed a strict execution order under RCSI should be reviewed before enabling the feature.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Choosing a stricter isolation level
Microsoft advises that workloads relying on strict transaction ordering under RCSI consider REPEATABLE READ or SERIALIZABLE. Those levels hold row and page locks for longer, so they can increase blocking and lock memory. They are correctness and concurrency choices that need workload review. They are not a free fix. Note also that LAQ does not run under those isolation levels, so stricter isolation trades the feature’s benefit for the ordering guarantee.
Best Value
Checking behavior in practice
Start by confirming the three database settings: optimized locking, ADR, and RCSI. Then examine the statements that matter. Look at their isolation level, any hints, the presence of columnstore indexes, OUTPUT clauses, variable assignments, MERGE, and the number of index seeks or scans that read the modified rows.
For live lock inspection, Microsoft identifies sys.dm_tran_locks as the view to examine current locks. The feature also adds locking-related Extended Events:
lock_after_qual_stmt_abortfires when a statement is reprocessed internally after a conflict.locking_statsandlocking_stats2emit periodic aggregate locking and LAQ information.
Measure before and after with your own workload. Microsoft’s documentation gives a mechanism and an illustration but no general performance percentage, and it does not promise one. The benefit depends on how much your statements contend on the same rows and on whether LAQ actually applies to them.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How the two mechanisms compare
| Aspect | TID locking | Lock after qualification (LAQ) |
|---|---|---|
| What it changes | How long row locks for modified rows are held | Whether a row is locked at all when a predicate does not match |
| Requires RCSI | Not stated as a requirement | Yes; operates only when RCSI is enabled |
| Requires ADR | Yes, because optimized locking depends on ADR | Yes, through optimized locking |
| Main effect | Fewer retained row locks, lower lock memory | Fewer waits on non-qualifying rows |
| Can change which rows qualify | No | Yes, because it evaluates the latest committed version |
| Main exclusions | Tempdb, temporary tables, read-only secondary replicas | Hints, non-READ COMMITTED isolation, RCSI off, columnstore, variable assignment, OUTPUT with result set or table variable, multiple seeks or scans, MERGE |
The table shows the two mechanisms as separate effects. A statement can benefit from one and not the other, so verify each in your workload.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
