SQL Server Query Store Improvements I’d Like To See

Not Love Or Hate

I like Query Store, I do. I don’t love the GUI, though. In fact, I kinda hate it — enough that I spent a hundred or so hours writing a script called sp_QuickieStore to avoid it — which should prove a point.

In the war of Chicken v Egg, one might say that it’s not worth developing a feature that isn’t exactly widely in use. Of course, the reason for that might be that… What got developed isn’t what people need.

Here are some similar examples:

Chicago Pizza
Tabs
Cats
Chunky peanut butter

See what I mean? I want better for Query Store.

With that said, here’s what I think it should do better.

Searching

Query Store needs to give you a way to search for things you care about. A few of the things sp_QuickieStore lets you search for:

Query Ids
Plan Ids
Object names (functions, procedures)
Query Text

I built that because I need that, and so do a lot of other people. Just opening up a GUI and sorting by some metrics doesn’t help you when you need to find a specific thing that’s causing a problem.

If I can write queries to do that, I’m sure the fine folks at Microsoft can manage. I’m not that bright.

Ignoring

Some queries you don’t care about. At all. Ever.

You know how you can track some queries? I’d love a way to unsubscribe from some queries, too.

And uh, sp_QuickieStore lets you ignore a list of Query and Plan Ids in the output.

It should also ignore a few other things that you can’t really tune:

Index maintenance
Statistics updates
Query Store Queries

If you’ve ever looked in Query Store, seen some crazy outlier, and found one of the queries you just ran to populate the GUI, you’re not alone.

Visualizing

In SQL Server 2017, Query Store started collecting high level wait stats. I’m not asking for anything more granular, but how about giving me an option to see them broken down over time?

Right now, you open that view up and there are some columns that show you the highest waits, but not when they happened.

When is pretty important! You might wanna see things that use high CPU, but only during the day. Sure, that’s easy if you know when you want to look at, but if you just wanna get a sense of how things ebbed and flowed over the last day or week, you’re outta luck.

A nice line graph that you can choose which waits to include in broken down over time would be awesome, especially if you could zoom in to find out what was running during a window.

The reports now are all very rudimentary. Maybe Microsoft is being nice to monitoring tool companies and not stepping on their toes, but not everyone has one of those.

Or will ever have one of those.

Please, help!

Collecting

I’ve griped before about this, but Query Store doesn’t collect one of the most useful metrics from a query: the granted memory. It only collects used memory.

You can get that from the Plan Cache, but that thing is an unreliable grump. And yeah, you can get that if you look in the query plan, but… If I want to do some analysis and quickly figure out which queries asked for huge grants and then didn’t use them, it’s not very practical to open every query plan to do it.

I’d also love if it stored some of the actual/runtime metrics. Not every one. Maybe the last 1-5 plans, either for tracked queries, or for ones that meet a certain threshold for resource usage.

Imagine if you could get the compile and runtime parameters for a query you know is sensitive to parameter sniffing, so that you could easily reproduce the issue?

Your life would get a whole lot easier.

Analyzing

Since Query Store is somewhat asynchronous, it would be helpful if you could get some advice about some of the gremlins in your Query Plan.

There’s a ton of detail and data in those query plans that’s ripe for analysis. If you’ve ever looked at the Warnings column in sp_BlitzCache, you’ll understand what I mean.

Yeah, that all comes from a bunch of terrible XQuery, but dammit… Normal folks looking at query plans don’t always know what to look for.

Heck, I’d forget to look for everything on that list, too, and I spend a good chunk of my days staring at these darn things.

It doesn’t have to be as exhaustive, but it would give civilians a fighting chance of understanding why things are bonked.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.