Category: Query Store

Things Are Getting Better

Centipede

Thanks for watching!

Checking It Twice

Thanks for watching!

Four Hours

Thanks for watching!

Red Text

Thanks for watching!

Headboard

Thanks for watching!

No One’s Perfect

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.

Help

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.

Debug

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:

• Dynamic SQL statements, and statement length in the Messages tab
• Supplied parameter values to the procedure
• Declared parameter values
• All temp table contents with table name identifiers
• Replacement selects for empty tables with table name identifiers

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.

Performance

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.

Wrapper’s Delight

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!

Random Assortment

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;

Overview

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.

Yep

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!

Safe Words

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?

Answer Me

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';

Over And Out

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.

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.

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.

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.

Options:

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

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!