Every Scooby-Doo mystery starts with a haunted house, a strange villain, and a trail of clues. With SQL Server 2025 now reaching its first Release Candidate (RC0), it feels just like stepping into that creepy old mansion—except this time the mystery isn’t about ghosts, it’s about uncovering the powerful new features that go way beyond AI hype.
And trust me, the clues are worth following.
But before Mystery Inc. starts sleuthing, you’ve got to make sure the clue generators are turned on. Many of these new features are controlled through database scoped configurations. Think of it like Scrappy-Doo charging into the room—if you don’t let him loose, you’ll miss the chance to flush out the villain and uncover what’s really going on
You can do this with a simple command:
ALTER DATABASE SCOPED CONFIGURATION SET FEATURE_NAME = ON;
*Replace FEATURE_NAME with the option you want to enable. (Full list here: Microsoft Docs)
For example, if you want to enable preview features:
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
Note: I would expect more new features to be under this new setting, rather than having to deal with confusing and often poorly documented trace flags.
Now, when you start following the breadcrumb trail through execution plans and Query Store, you’ll actually see the new features in action.
Intelligent Query Processing Evolves
The spotlight in this episode is on Intelligent Query Processing (IQP) and Query Store enhancements. One of the most exciting clues is Cardinality Estimation (CE) feedback for expressions.
In short, SQL Server can now look at repeated poor estimates for operations like scalar expressions and automatically adjust their values in execution plans. In the past, you might see a query get a bad plan because CE made the wrong guess about row counts. Now, the engine can learn from those mistakes and correct itself. It’s like Velma putting the pieces together after seeing the same footprints twice—it helps queries perform closer to how they should without you having to intervene.
Default Features That Pack a Punch
Some of the tools we got in SQL Server 2022 are now on by default in 2025:
- Parameter Sensitive Plan (PSP) optimization
- Degree of Parallelism (DOP) feedback
- Query Store for readable secondaries (it works now)
No more fumbling with switches—you get these powers right out of the box. It’s like Scooby and Shaggy stumbling into the secret passage without even trying.
Query Store Enhancements: Extra Clues
SQL Server 2025 gives Query Store even sharper detective skills. Even if you don’t always see it working in the background, Query Store is hot on the trail—tracking clues, gathering evidence, and piecing together the mystery of your workload performance.
- Optimized Halloween Protection (OPTIMIZED_HALLOWEEN_PROTECTION) – Like uncovering a hidden trapdoor, this feature reduces tempdb writes during large updates and deletes, saving I/O without a single code change. (Zoinks! Just be aware—during preview it revealed a spooky side effect around potential data integrity, so keep watch.)
- Transaction-ID (TID) Locking – Think of this as Fred’s careful trap design: smarter row-level lock tracking means less memory overhead and fewer waits when many users are running at once.
- Persistent Statistics on Secondaries – No more cold starts after a spooky failover. Stats now stick around, so your promoted secondary jumps right into action without the usual warm-up lag.
- Optional Parameter Plan Optimization (OPPO) – Like Velma spotting the right clue at the right time, OPPO helps SQL Server pick better plans based on runtime parameters, cutting down on parameter sniffing nightmares.
And the best part? Query Store keeps a record of all these moves—so if any new trick backfires, you’ve got the clues and the history to track it down and fix it fast.
Stopping Villains in Their Tracks
My favorite addition? The Abort_Query_Execution query hint.
This is the equivalent of Fred dropping the net on a villain mid-chase. You can tag known-problem queries so that if they show up, they’re stopped before they wreak havoc on your workload. For example, imagine a report query that occasionally explodes with bad parameters and hogs all your resources. Instead of letting it take down the system, Abort_Query_Execution cuts it off at the knees. A big win for keeping the gang (and your workload) safe.
Finding the Clues
Want to know if CE feedback, OPPO, or Abort_Query_Execution has been used? Look no further than the execution plan XML. Just like shining Velma’s flashlight in the dark, the plan details reveal where feedback kicked in or hints were applied. These are the breadcrumbs that help you understand not just what happened, but why.
Coming Soon…
This episode focused on query processing and Query Store clues. In the next one, we’ll venture deeper into the mansion and tackle the database engine enhancements waiting to be unmasked in SQL Server 2025.
Until then, keep your magnifying glass handy—because the best mysteries are solved by those willing to follow the clues.
The post The Mystery of SQL Server 2025’s New Tricks – Scooby Dooing Episode 5 first appeared on A Shot of SQLEspresso.