Introducing sp_QuickieStore: What The Results Show You

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.

big time
selected markets
coming your way
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.

parabore
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.

memory
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.

wowsers

Options:

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

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!



Leave a Reply

Your email address will not be published. Required fields are marked *