No Scooby-Doo story is complete without footprints leading to a hidden passage. In SQL Server 2025, those footprints point us straight toward the next big feature: optimized locking.
And like any good sleuth, we’re going to follow the trail step by step.
But first, flip the levers that open the secret door:
- Turn on ADR before you hunt.
Think of Accelerated Database Recovery (ADR) as the latch that unlocks the passage. You must enable ADR before you can enable optimized locking.
(And if you ever need to turn ADR off, you’ll have to disable optimized locking first—no sneaking out the window!)
- RCSI = brighter flashlight.
For the biggest win, enable Read Committed Snapshot Isolation (RCSI). The LAQ (Lock after qualification) piece of optimized locking only springs into action when RCSI is on, letting the gang glide past unnecessary blocking like they’ve got VIP passes through the haunted hallway.
With those switches flipped, the clue generators are live—and the gang can follow the trail without tripping over the old locking ghosts.
To check if these are turned on, you’ll do your sluething in sys.databases.
SELECT database_id,
name,
is_accelerated_database_recovery_on,
is_read_committed_snapshot_on,
is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();
Following the Trail of a Transaction
Traditionally, when a transaction updated, inserted, or deleted rows, SQL Server tracked locks at the row level. Each lock was recorded individually, like Scooby and Shaggy marking every single sandwich they ate. On big workloads with lots of activity, this lock tracking could balloon, slowing performance and causing memory overhead.
With optimized locking, SQL Server introduces transaction ID (TID)–based tracking. Instead of keeping track of every single row lock, the engine assigns a single transaction ID to represent all those locks. Now, when Scooby follows the footsteps across the haunted floor, he doesn’t have to count every single print—he just follows the ID trail.
This means fewer locks to manage, less overhead, and faster performance under heavy workloads.
The Crimes of Old Locking
Locks are supposed to keep your data safe, but left unchecked they can turn into villains haunting your workload. Here are a few of the usual suspects the gang has to chase down:
- Blocking – One query grabs a lock and won’t let go, forcing every other query to pile up behind it like Scooby and Shaggy trapped in a hallway with doors slamming shut.
- Lock Escalation – Too many little row locks suddenly throw off the mask and transform into a giant table lock, shutting down the whole party.
- Memory Bloat – Thousands of tiny locks eat up memory until your system is staggering around like a zombie, dragging performance with it.
With optimized locking, SQL Server 2025 steps in like Fred with a net—it streamlines the chaos, keeping locks tidy and manageable. Instead of tracking every muddy footprint, it tags them all under one transaction ID and follows the single trail. No more villains multiplying in the shadows.
Diagnosing the Mystery
How do you know optimized locking is at work? Like Velma’s flashlight cutting through cobwebs, you can use DMVs and wait stats to reveal what’s really happening behind the scenes:
- Keep an eye on waits like LCK_M_* to see if ghoulish blocking patterns start. Daphne keeps sp_whoisactive in her pocket for times like these.
- Check memory use in lock manager DMVs—optimized locking should slim things down instead of ballooning out. Fred’s trusty pal sys.dm_os_memory_clerks is a great resource for this or take a peak at Shaggys favorite performance counter
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Memory Manager%';
- Peek into execution plan XML to spot how transactions are being tagged and tracked.
But here’s where the gang can really follow the footprints: the new wait types for optimized locking. Instead of tracking every row, these clues point you right to what the transaction is up to.
- LCK_M_S_XACT_READ – Like Scooby and Shaggy sneaking a peek at the fridge, this wait shows up when a query is holding back, waiting to read under a shared lock.
- LCK_M_S_XACT_MODIFY – This one’s like Fred fiddling with a trap. It happens when a query is lined up, waiting for its turn to change something—an insert, update, or delete.
- LCK_M_S_XACT – The most mysterious clue of all. You see the footprints, but you can’t quite tell what the culprit was trying to do. Not common, but definitely worth jotting in the detective’s notebook.
And if you want to shine the lantern deeper into the haunted mansion, you’ve got more tools:
- sys.dm_tran_locks → Use the resource_description column to see which XACT locking resources are being tracked.
- sys.dm_exec_requests → The wait_resource column shows you where queries are standing in line, waiting for their turn.
Put all these clues together, and you can trace the exact footsteps of a transaction—whether it’s reading, modifying, or just lurking in the shadows. It’s like following muddy prints through the old mansion until the villain has nowhere left to hide.
Before the Gang Opens the Door
The Mystery Inc. gang always checks for hidden traps, and so should you:
- Test your workloads – Some applications get spooked if blocking doesn’t behave the way they’re used to. Run through your scenarios so you don’t end up with a “ruh-roh” moment in production.
- Check your hardcoded hints – Old locking hints still work, but like Scooby and Shaggy opening the wrong door, they might not always act how you expect. Better to double-check than get surprised by a ghost.
- Do your diagnostics – Know your baselines before you start sleuthing. That way, when performance changes, you can tell if it’s a real improvement or just another masked villain.
Unmasking the Villain
Optimized locking is like finding the secret passageway in the haunted mansion—it clears the hallway, frees up resources, and keeps your queries moving. Just remember, every mystery takes a bit of testing before you can unmask the villain.
In the end, optimized locking doesn’t just reduce blocking—it makes SQL Server 2025 a smarter, leaner detective in handling your workloads.
The post The Mystery of the Locked-Up Database -Scooby Dooing Episode 6 first appeared on A Shot of SQLEspresso.