Category: sp_QuickieStore

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.