Introducing sp_QuickieStore: What You Can Filter

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.

Going Further

---

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.