SIL — Because Holding 1,000 KEY Locks Is So 2022

Back in September I wrote about Optimized Locking in SQL Server 2025. Two components: Transaction ID (TID) locking and Lock After Qualification (LAQ). I told you the engine had stopped 'locking first and asking questions later'. That was true, but it was also only half the story.

Microsoft has since gone back and fine-tuned the feature, giving us two improvements that make a database more lock-free without you touching anything. The headliner is called Skip Index Locks (SIL), and the numbers are pretty good:

Measurement	Result
Key locks skipped (Microsoft demo)	99.6%
Page locks skipped (Microsoft demo)	79%
Nonclustered index locks skipped (Azure fleet, ~25K DBs)	81%

No configuration. No hint to add. If you're on SQL Server 2025 with Optimized Locking enabled, SIL is already in there working for you. This post explains what SIL is, how it works, and a demo you can run yourself to see the locks vanish.

Quick recap: TID and LAQ

Transaction ID (TID) locking. Every modified row gets stamped with the ID of the transaction that touched it last. Row and page locks are still taken, but they're released the moment each row is modified, instead of being held through to the commit. The only thing held until commit is one X lock on the TID. Lock memory drops, and lock escalation drops with it.

Lock After Qualification (LAQ). The engine evaluates the predicate against the latest committed row version first, then locks only the rows that actually qualify. Pre v2025, a WHERE OrderDate < '2025-01-01' UPDATE took a U lock on every row it scanned, qualified or not. With LAQ, locks land only on the rows that match.

Both require Accelerated Database Recovery and LAQ also requires Read Committed Snapshot Isolation. Both of these shipped in SQL Server 2025 at launch.

The improvement: Skip Index Locks (SIL)

Under TID locking with RCSI and LAQ in the mix, those short-duration row and page locks are only necessary if some other query is also touching the row and expecting it to stay stable. The kind of query that needs that guarantee is one running under Repeatable Read or Serializable, or using locking hints like 'HOLDLOCK' or 'READCOMMITTEDLOCK'. Microsoft calls these Row Locking Queries (RLQ).

If no RLQ is touching the page, those row and page locks aren't preventing any conflict. Meaning, there's nothing there to conflict with and SIL recognizes that and skips them. The row still gets modified, the change is still ACID-safe, and the lock manager never gets the call. Nice.

The mechanism: Every page in the buffer pool now carries a single No-RLQ bit. When that bit is set, it tells the engine that no Row Locking Query is currently touching any row on the page. A DML statement modifying a row on that page skips the row lock and the page lock entirely, and takes a short exclusive page latch instead. The latch is brief. Each lock would have been a round trip into the Lock Manager, and for a transaction touching thousands of rows, those round trips will add up. That is the overhead that SIL minimizes for us.

Per Microsoft's Azure telemetry, RLQ-style queries are uncommon in real-world workloads using RCSI or snapshot isolation. That is why the skip ratio is so high. Most pages, most of the time, have nobody using them in a way that demands a lock.

What SIL skips, and what it doesn't

SIL IS used for:

• INSERT statements on heaps - the intent-exclusive (IX) page lock is skipped.
• UPDATE statements on clustered, nonclustered indexes, and heaps - both the IX page lock and the exclusive (X) row lock are skipped. The engine leans on the page latch and the TID lock instead.

SIL is NOT used for:

• DELETE statements.
• UPDATE on heaps where the row has existing forwarding pointers, or where the update creates new ones.
• Rows with LOB columns - VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), JSON.
• Rows on pages that were split in the same transaction.

The exclusions matter. If your hot tables are LOB-heavy or your workload is heavy deletes, your skip ratio will be lower than the headline numbers. Those headline numbers come from update-heavy OLTP under RCSI, which is one of the most common workloads out there.

The other half: LAQ

SIL got the headline, but it shipped with a sibling. Lock After Qualification, or LAQ. LAQ is optimistic. It evaluates query predicates without placing locks on the rows while scanning. In short, it checks the latest committed version of the row and acquires the necessary lock only after the row has qualified for the modification.

The demo

Here is a full working demo for SQL Server 2025 and ADR and RCSI are required.

Step 1: Create the database and enable everything.

CREATE DATABASE SILDemo;
ALTER DATABASE SILDemo SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE SILDemo SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE SILDemo SET OPTIMIZED_LOCKING = ON;
GO

Step 2: Verify SIL is enabled.

USE SILDemo;
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;
GO

Step 3: Create a table with a clustered index and add data.

CREATE TABLE dbo.TestSIL
(
  ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  Value int NOT NULL
);
GO

INSERT INTO dbo.TestSIL (Value)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO

Step 4: SIL active — clustered index update.

Open a transaction, update rows, and see what locks the engine holds.

BEGIN TRANSACTION;

UPDATE dbo.TestSIL
SET Value = Value + 10;

SELECT resource_type,
       request_mode,
       COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY','PAGE','RID','OBJECT','XACT')
GROUP BY resource_type, request_mode
ORDER BY resource_type, request_mode;

COMMIT TRANSACTION;
GO

If SIL is working correctly, we will see ZERO KEY locks. Under classic locking, an update of 1,000 rows would hold 1,000 X KEY locks (one per modified row) all the way to the commit. SIL skipped every one of them. We still see a small number of PAGE locks (IX mode) and an OBJECT lock (IX), but those are expected and are not held long-term. The KEY lock count is where the story is, and the KEY lock count is zero.

In a nutshell, under classic locking without SIL, updating 1,000 rows would hold 1,000 X KEY locks. With SIL, those KEY locks are skipped entirely.

What you need, and why it matters

SIL turns on for free if the rest of the stack is in place:

• SQL Server 2025 — it shipped with GA release.
• Accelerated Database Recovery enabled on the database.
• Read Committed Snapshot Isolation enabled — without it, LAQ never engages and SIL has nothing to build on.
• Optimized Locking enabled — ALTER DATABASE [dbname] SET OPTIMIZED_LOCKING = ON;
• A workload that mostly INSERTs or UPDATEs indexed tables, under RCSI, light on LOB columns.

Azure SQL Database and SQL database in Fabric get all of this automatically. Azure SQL Managed Instance is rolling out. On-prem SQL Server 2025 needs it setup at the database-level.

When it's working, the payoff is the kind a DBA notices: lock memory drops, blocking chains on update-heavy OLTP shorten or even disappear, and you got there with no code changes and no new hints scattered throughout your queries. This is server level. There is no way to opt a single statement into SIL, and there doesn't need to be.

SQL Server 2025's Optimized Locking was already a win. I think SIL is the part that makes the numbers even more worth bragging about.

More to Read

Microsoft Tech Community: Introducing optimized locking v2
Microsoft Learn: Optimized Locking
Microsoft Learn: Accelerated Database Recovery
sqlfingers inc: Optimized Locking in SQL Server 2025 — Concurrency Gets Smarter (September 2025)