SQL Server Consulting, Education, and Training
Thanks for watching!
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.
I’ve been working on scripts like this for long enough to know that I don’t know everything that might happen out there in your kooky-krazy environments.
The beauty of open source is that it’s really easy to let me know, or jump in and get to fixing things on your own.
To help you figure out where things are getting messy, I’ve added a few parameters to make troubleshooting easier.
The first place to look for information or details is the help parameter.
EXEC dbo.sp_QuickieStore
@help = 1;
You’ll get information about parameters, results, shortcomings, and licensing.
Of course, if you’re hitting an error whether it’s red text or logical, you probably need a bit more than this.
If you want to see the queries that the dynamic SQL builds and executes, you can use the debug mode.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@debug = 1;
What debug mode returns:
This should help you figure out what’s going wrong and when. If you find something, open an issue on GitHub to tell me about it.
If you find this proc is regularly running slowly for you, you can use run this to return query plans and see where the trouble’s at.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@troubleshoot_performance = 1;
This will SET STATISTICS XML ON; for queries that touch Query Store views, where we typically will have performance issues, along with a table of queries and runtimes.
Once data is in temp tables, things are fast because they’re small. Before that, you’re at the whim and fancy of the elements.
If you come across something that you think I can fix (and isn’t just poor Query Store schema design/indexing), open an issue on GitHub to tell me about it.
This week we covered all the major aspects and features of sp_QuickieStore. I’ll do some video code reviews to close out the series for now.
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.
Sometimes it’s hard to get people to understand the scale of a number without some commas.
I don’t know why that is, but that’s why I added the option to format numbers.
You can do that by running the procedure like so:
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow',
@expert_mode = 1,
@format_output = 1;
What you get back should look something like this.
This isn’t the most elegant screenshot in the world, I just want to give you an idea of what you should see.
Essentially, if it’s a number, it will get commas inserted into the proper places.
I know that this is a somewhat Americanized version of things, and that the FORMAT function supports other language stuff.
If that’s something you care about and you’d like to contribute, head over here. Code contributions are always welcome.
Tomorrow, we’ll look at options you have to get help, and troubleshoot code and performance issues.
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.
What good is writing all this dynamic SQL and whatnot if you’re just going to firehose data at people?
None. None at all.
You’re going to want to answer questions about what happened and when, and you’re not going to want to struggle with it.
So what can you do?
First, you can control how many queries you’re going to get back in your result set:
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10;
You can also filter on start and end times. These filters are applied to the last_execution_time column in avg_duration in query_store_runtime_stats.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@start_date = '20210101',
@end_date = '20211231';
If you want to target queries that execute a lot, or that run for over a certain amount of time, you can use filter like this.
These filters will be applied to count_executions and avg_duration respectively, also in query_store_runtime_stats.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@execution_count = 20;
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@duration_ms = 1000;
Targeting a specific stored procedure can be done like this. If you don’t specify a schema, I’ll assume you’re looking in dbo.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@procedure_schema = 'some_schema',
@procedure_name = 'some_procedure';
To include or ignore a single or list of query or plan IDs, you can use these parameters. Spaces and other whitespace should be handled alright.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@include_plan_ids = '1,2,3,4,5';
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@include_query_ids = '1,2,3,4,5';
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@ignore_plan_ids = '1,2,3,4,5';
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@ignore_query_ids = '1,2,3,4,5';
Finally, if you want to search for query text, you can do this. I’ve found this search to be a bit unreliable, unless you’re generous with wildcards. If you don’t add them at the beginning and end, I’ll do that for you.
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@query_text_search = 'some text here';
I think that covers most of the important filtering you’d want to do in Query Store. Of course, if you think anything else would be useful, you can request it here.
Tomorrow, we’ll look at some formatting options available.
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.
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;
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.
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.
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.
If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.
Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.
I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.
With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.
Out of the box, you get a lot of choices about what you want to get and how you want to get it.
You can choose:
sp_QuickieStore is compatible with SQL Server 2016 SP2+, 2017 CU3+, any version of 2019, and probably Azure SQL DB. I designed it to work there, but long term who knows what changes will occur that make support not possible. No promises.
It’ll take advantage of new columns added to SQL Server 2017 and 2019, and the wait stats tracking added in SQL Server 2017.
Testing on my end is done on case-sensitive instances from 2016 up. If you find bugs, please report them on GitHub. I do not offer private email support.
GitHub is also where you can go to ask for features, with the usual caveats that open source is not free development or consulting.
Thanks for understanding, and all that.
sp_QuickieStore has a bunch of documentation built in to the procedure, and there are a number of example calls in the repo.
Now, I know. It’s called sp_QuickieStore, so you expect it to be fast. I’ve done what I can to make things as efficient as possible, but gosh darn it all, sometimes Query Store is just gonna be Query Store.
It would be nice if the maintainer of a database that promises high performance maybe put some useful indexes on their views to analyze performance so the queries that touch those views to analyze performance run quickly.
I know, I know. Take off your Crazy Pants, Erik.
Anyway, I’ll have some more posts coming this week to fill in details.
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.