Remember 2016? It was a simpler time when some of us were probably still rocking some on-prem SQL Server hardware, and Query Store had just strutted onto the scene like an eager new intern trying to make a good impression by logging every execution plan in sight.
Well, buckle up. Query Store has spent the last few versions doing some serious leveling up. It’s no longer the new kid on the block. It’s the seasoned performance sleuth you definitely want on your side when queries start going rogue.
Whether you’ve been ignoring it since 2016 (no judgement… okay, a little judgement) or you’re just now poking around the settings, here’s what’s changed and why Query Store is more powerful, flexible, and hopeful than ever before.
From Debutante to Power Player: Key Enhancements Since SQL Server 2016
SQL Server 2017: Hello, Wait Stats!
Query Store 2017 got a shiny new upgrade, and it brought wait stats to the party. Before this release, Query Store could show you which queries were slow, but it couldn’t tell you why. You basically had to conduct an in-depth forensic analysis using half a toolbox and sheer determination to figure out if a query was bottlenecked by CPU, memory, or just existential dread (okay, maybe not that last one).
But now? Query Store tracks wait stats per query over time, giving you a clearer picture of what’s holding things up. So if your query is taking a nap instead of running like the boss it should be, Query Store can now tell you exactly what it’s waiting for: CPU, memory, or just blocking itself in a tragic twist of fate.
It’s like turning on subtitles for query performance. Suddenly, all that vague slowness starts to make sense.
SQL Server 2019: Auto-Correct, but for Queries
By the time SQL Server 2019 rolled around, Query Store had clearly been hitting the performance gym, and it brought back some serious gains.
One of its biggest flexes? Automatic Plan Correction.
Yes, it’s exactly what it sounds like. Suppose a query suddenly decides to take the scenic route and forget how to be efficient (as they do). In that case, Query Store can automatically roll it back to a previously better-performing execution plan. Like a helpful ghost of queries past. No cape required. Just solid plan history and some behind-the-scenes intervention.
It’s basically the query whisperer: quietly observing, patiently waiting, and then gently nudging your rogue query back into being a decent member of society.
But wait, there’s more!
The Query Store UI got a long overdue glow-up in SSMS. Think less “Boring Wall of Metrics” and more “Wow, I can actually use this!” Charts you can read. Data that makes sense. Fewer tears. Possibly fewer expletives.
It’s the feature equivalent of SQL Server tapping you on the shoulder and saying, “Hey, I noticed this plan is kind of a disaster. Mind if I fix it for you?”
And honestly, when SQL Server volunteers to fix something… you generally say yes. And if the plan still feels like it’s speaking in riddles, just ask us. We’re fluent in Query Store.
SQL Server 2022: Query Store World Tour
Query Store didn’t just get smarter. It went global (Okay, replica global, but still.)
- Query Store for Readable Secondaries:
At long last, secondary replicas, tired of being left out, were invited to the party. You can now gather Query Store insights even on readable secondaries in Always On Availability Groups. No more performance mysteries in the shadows. - Query Store Hints:
Finally! Hints you can apply without touching the code. It’s like giving your queries a motivational pep talk, quietly behind the scenes. You got this, little SQL. - Parameter Sensitive Plans (PSPs):
Before SQL Server 2022, queries with different parameter values often clung to a single execution plan like it was their emotional support plan, whether it made sense or not.
Now thanks to PSP support, SQL Server can generate multiple optimized plans based on actual parameter values. So, instead of your query saying, “This plan worked once in 2019, and I’m never letting it go,” now says, “Oh hey, small dataset? Here’s your streamlined plan, little buddy. Giant dataset? I got you covered too.”
It’s flexible, smart, and honestly? Long overdue. Goodbye stubbornness, hello adaptability. With these upgrades, Query Store is no longer just a historian. It’s a performance-tuning partner with an eye for nuance.
Bonus Perks in Azure SQL
If you’re using Azure SQL Database or Managed Instance, Query Store is always on and always in the mood to analyze. No middle-of-the-night panicked setup. No chaotic switch-flipping. Just pure, eager-to-please performance tracking.
And the best part? Azure gets the newest Query Store features before your on-prem SQL Server even hears about them, like a golden retriever puppy that already knows how to sort XML and also brings you your slippers. Because in the cloud, everything is just a little more.. enthusiastic.
So… Should You Be Using Query Store Now?
If you’ve been sleeping on Query Store, consider this your friendly wake-up call. It’s not just for troubleshooting anymore. It’s your all-access pass to performance insights, historical query trends, and plan management glory.
Also, if you’re still hanging out with SQL Server 2016 or (gasp) something even older? Well, this is your sign to upgrade, my friend. Since Query Store is your workload’s performance coach, you might as well give it both the whistle and the playbook. Otherwise, it’s just yelling ‘DO BETTER’ at your queries from the corner without a game plan.
Not sure where to start? We can help. Whether you’re planning an upgrade, enabling Query Store for the first time, or just need someone to untangle a few stubborn execution plans, we’ve got your back.
TL;DR
- Query Store is way smarter now.
- Wait stats, automatic plan correction, hints, and PSP support are here to make your life easier.
- It’s time to stop ignoring your queries’ emotional needs.
Embrace Query Store. Love Query Store. Or at least let it help your queries stop tripping over themselves in production.
Want to learn more? Read other Blogs.