Optimized locking is a SQL Server 2025 (17.x) database-engine feature that can reduce how many row and page locks concurrent write transactions hold, and for how long. It uses transaction ID (TID) locking and, when read committed snapshot isolation (RCSI) is enabled, lock after qualification (LAQ). It is off by default in SQL Server 2025; accelerated database recovery (ADR) must be enabled before you turn it on.
What is optimized locking in SQL Server 2025?
Optimized locking changes how SQL Server manages locks for data modifications. Conventional write transactions can hold many row or key locks until the transaction ends. With optimized locking, SQL Server can release low-level locks as changes are made and use a transaction ID lock to protect the modified rows. This can lower lock-memory demand and reduce lock escalation, blocking, and some deadlock scenarios. It does not remove all locks or prevent every kind of blocking.
Microsoft describes optimized locking as a mechanism to reduce lock blocking and lock-memory consumption for concurrent transactions. The change primarily affects row and page locks associated with INSERT, UPDATE, DELETE, and MERGE; it does not change other lock types such as schema locks. Microsoft Learn: Optimized locking
TID locking
Each modified row is associated with the transaction ID of the transaction that last changed it. Rather than keeping many low-level row or key locks until commit, SQL Server can release those locks as the update proceeds and retain a transaction-level TID lock to protect the changes.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
Lock after qualification (LAQ)
With RCSI, LAQ evaluates a write predicate against the latest committed row version without first taking a lock just to test whether the row qualifies. If the row qualifies and another transaction is actively modifying it, the writer may still have to wait; LAQ is not a guarantee of lock-free writes.
Microsoft illustrates the distinction with a transaction updating 1,000 rows: conventionally, 1,000 exclusive row locks might remain until transaction end, while optimized locking can release low-level locks as rows are updated and retain a TID lock. This is an explanatory example, not a benchmark or a promised performance result.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
Is optimized locking enabled by default?
No. In SQL Server 2025 (17.x), optimized locking is supported but off by default and configured per database. Microsoft lists SQL Server 2022 (16.x) and earlier as unsupported. Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric also support the feature, but their documented defaults are service-specific and should not be inferred from the SQL Server 2025 on-premises default. See the availability details in Microsoft Learn.
Does optimized locking require ADR or RCSI?
ADR is a prerequisite: enable accelerated database recovery in the database before enabling optimized locking. RCSI is not the prerequisite for TID locking, but it is required for LAQ and is recommended for the greatest benefit. Microsoft recommends using RCSI with the default READ COMMITTED isolation level for this feature.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Under that combination, readers use statement-level row versions, while LAQ evaluates writer predicates against the latest committed value. A qualifying row with an active writer can still make another writer wait. For configuration details and isolation behavior, see Microsoft’s transaction locking and row versioning guide.
How do I enable optimized locking in SQL Server 2025?
First check the database’s ADR, RCSI, and optimized-locking status. For the database you are changing, run:
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
SELECT name,
is_accelerated_database_recovery_on,
is_read_committed_snapshot_on,
is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();Microsoft also documents this check for the current database: SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn'); If ADR is off, configure it before proceeding. Then enable optimized locking per database:
ALTER DATABASE [YourDatabase] SET OPTIMIZED_LOCKING = ON;To turn it off later, use ALTER DATABASE [YourDatabase] SET OPTIMIZED_LOCKING = OFF;. Replace YourDatabase with the actual database name. The database must be online, and Microsoft requires that no other active connections to it remain while the setting is changed; the connection issuing the ALTER DATABASE command is the exception. Plan a maintenance window or otherwise arrange connections accordingly. Refer to Microsoft’s ALTER DATABASE SET options documentation.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteBest Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
How do isolation levels and lock hints affect the benefits?
Optimized locking’s effect depends on transaction settings and application behavior. The following comparison describes documented behavior, not a recommendation to change isolation levels without validating the workload.
| Configuration or behavior | Effect on optimized locking |
|---|---|
| RCSI with default READ COMMITTED | Recommended for greatest benefit; enables LAQ. Readers use statement-level row versions, and the engine handles and retries detected update conflicts. |
| REPEATABLE READ or SERIALIZABLE | Row and page locks can remain until transaction end, increasing lock memory and blocking. |
| SNAPSHOT isolation | Update conflicts behave as they did before optimized locking; the application must handle and retry conflicts. |
| Locking hints such as UPDLOCK, READCOMMITTEDLOCK, XLOCK, or HOLDLOCK | Hints remain honored but can reduce the optimization’s benefit. READCOMMITTEDLOCK can be used when an application intentionally needs blocking behavior under RCSI. |
Microsoft documents these isolation and conflict behaviors in its transaction locking and row versioning guide and optimized locking guidance.
Does optimized locking eliminate blocking?
No. It can reduce blocking caused by long-lived low-level write locks, and can reduce lock escalation and some deadlock scenarios. It does not eliminate blocking from every lock type, active conflicting write, stricter isolation level, or locking hint. It also does not change schema locks. Optimized locking is not used for modifications in tempdb or temporary tables, and read-only secondary replicas do not use it because they cannot run DML.
How much performance improvement should you expect?
Microsoft’s documentation describes qualitative benefits but does not provide a universal percentage improvement or a named benchmark result. The 1,000-row illustration explains lock behavior; it is not a measured performance claim. The effect on throughput, latency, memory, and contention will depend on the workload, transaction patterns, isolation settings, and hints. Measure the target workload under representative conditions before estimating gains or changing production settings.
For Microsoft’s SQL Server 2025 feature summary, see What’s new in SQL Server 2025.
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.
