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
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;
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.
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.
These are metrics about query compilation, so you can figure out if your long-compiling queries are causing you problems.
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.
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.
The last additional result here shows what your current Query Store settings are. You may be surprised!
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!
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.
- How To Find Poorly Performing SQL Server Queries To Tune Using Query Store
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: How To Dig Deeper With Expert Mode With sp_QuickieStore
- SQL Server Community Tools: Formatting sp_QuickieStore Output So It’s Easier To Understand