Introducing sp_QuickieStore: What The Results Show You

Posted on by Erik Darling

Dos Equis

There are two ways to run sp_QuickieStore for your chosen database that alter the total results you get back. It’s controlled by a parameter called @expert_mode.

To get more concise results:

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @expert_mode = 0;

To get a whole lot more:

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @expert_mode = 1;

Concision

Under the more concise mode, you get one set of results back that combines metrics from query_store_runtime_stats, along with query text, query plan, and context settings. Some previews below. There’s a lot more in the actual results, but the screen caps would get very repetitive.

sp_QuickieStore Results
big time
sp_QuickieStore Results
selected markets
sp_QuickieStore Results
coming your way
sp_QuickieStore Results
contextual healing

Expertise

Under the more verbose output option, you get additional result sets back. Don’t worry, it’s not a bunch of extra columns tacked onto the end so you end up in an infinite side scroll. They show up underneath so you can, like, actually be aware that they show up. Again, these screen caps don’t show absolutely everything, they’re just here to get you comfortable with what you see.

Compilation:

These are metrics about query compilation, so you can figure out if your long-compiling queries are causing you problems.

sp_QuickieStore Results
parabore
sp_QuickieStore Results
sparkly

It Came From The Cache:

One thing that’s really annoying about Query Store is that it provides less information about queries than the plan cache. More specifically about thread and memory usage.

You get zero thread information back from query store, and very limited information about memory, specifically only what the query used. Not what the full grant was or anything else.

To remedy this, I query dm_exec_query_stats to try to locate additional details about returned queries. I can’t promise anything will be here, because most plan caches are about as stable as the South Pacific.

sp_QuickieStore Results
memory
sp_QuickieStore Results
threadly weapon

Waits By Query And Total:

In 2017+, when the option is enabled, I’ll pull back wait stats for the queries that show up in the results. The top waits show up in the normal result set, but in this mode there are two separate results that show wait stats per-query and also in total for the window of time that was examined.

sp_QuickieStore Results
wowsers

Options:

The last additional result here shows what your current Query Store settings are. You may be surprised!

sp_QuickieStore Results
slippers

Separated

This set of information should be enough to get you started on figuring out which queries to go after. It’s pretty complete, especially if you use set expert mode to 1.

In the next post, we’ll look at some of the filters you can use.

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.

Related Posts