This procedure will dig into Query Store data for a specific database, or all databases with Query Store enabled.
It's designed to run as quickly as possible, but there are some circumstances that prevent me from realizing my ultimate dream.
The big upside of using this stored procedure over the GUI is that you can search for specific items in Query Store, by:
- query_id
- plan_id
- query hash
- sql handle
- module name
- query text
- query type (ad hoc or from a module)
You can also choose to filter out specific queries by those, too.
And you can do all that without worrying about incorrect data from the GUI, which doesn't handle UTC conversion correctly when filtering data.
By default, it will return the top 10 queries by average CPU. You can configure all sorts of things to look at queries by other metrics, or just specific queries.
Use the @expert_mode parameter to return additional details.
| parameter_name | data_type | description | valid_inputs | defaults |
|---|---|---|---|---|
| @database_name | sysname | the name of the database you want to look at query store in | a database name with query store enabled | NULL; current database name if NULL |
| @sort_order | varchar | the runtime metric you want to prioritize results by | cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions, recent, plan count by hashes, cpu waits, lock waits, locks waits, latch waits, latches waits, buffer latch waits, buffer latches waits, buffer io waits, log waits, log io waits, network waits, network io waits, parallel waits, parallelism waits, memory waits, total waits, rows | cpu |
| @top | bigint | the number of queries you want to pull back | a positive integer between 1 and 9,223,372,036,854,775,807 | 10 |
| @start_date | datetimeoffset | the begin date of your search, will be converted to UTC internally | January 1, 1753, through December 31, 9999 | the last seven days |
| @end_date | datetimeoffset | the end date of your search, will be converted to UTC internally | January 1, 1753, through December 31, 9999 | NULL |
| @timezone | sysname | user specified time zone to override dates displayed in results | SELECT tzi.* FROM sys.time_zone_info AS tzi; | NULL |
| @execution_count | bigint | the minimum number of executions a query must have | a positive integer between 1 and 9,223,372,036,854,775,807 | NULL |
| @duration_ms | bigint | the minimum duration a query must have to show up in results | a positive integer between 1 and 9,223,372,036,854,775,807 | NULL |
| @execution_type_desc | nvarchar | the type of execution you want to filter by (regular, aborted, exception) | regular, aborted, exception | NULL |
| @procedure_schema | sysname | the schema of the procedure you're searching for | a valid schema in your database | NULL; dbo if NULL and procedure name is not NULL |
| @procedure_name | sysname | the name of the programmable object you're searching for | a valid programmable object in your database, can use wildcards | NULL |
| @include_plan_ids | nvarchar | a list of plan ids to search for | a string; comma separated for multiple ids | NULL |
| @include_query_ids | nvarchar | a list of query ids to search for | a string; comma separated for multiple ids | NULL |
| @include_query_hashes | nvarchar | a list of query hashes to search for | a string; comma separated for multiple hashes | NULL |
| @include_plan_hashes | nvarchar | a list of query plan hashes to search for | a string; comma separated for multiple hashes | NULL |
| @include_sql_handles | nvarchar | a list of sql handles to search for | a string; comma separated for multiple handles | NULL |
| @ignore_plan_ids | nvarchar | a list of plan ids to ignore | a string; comma separated for multiple ids | NULL |
| @ignore_query_ids | nvarchar | a list of query ids to ignore | a string; comma separated for multiple ids | NULL |
| @ignore_query_hashes | nvarchar | a list of query hashes to ignore | a string; comma separated for multiple hashes | NULL |
| @ignore_plan_hashes | nvarchar | a list of query plan hashes to ignore | a string; comma separated for multiple hashes | NULL |
| @ignore_sql_handles | nvarchar | a list of sql handles to ignore | a string; comma separated for multiple handles | NULL |
| @query_text_search | nvarchar | query text to search for | a string; leading and trailing wildcards will be added if missing | NULL |
| @query_text_search_not | nvarchar | query text to exclude | a string; leading and trailing wildcards will be added if missing | NULL |
| @escape_brackets | bit | Set this bit to 1 to search for query text containing square brackets (common in .NET Entity Framework and other ORM queries) | 0 or 1 | 0 |
| @escape_character | nchar | Sets the ESCAPE character for special character searches, defaults to the SQL standard backslash (\) character | some escape character, SQL standard is backslash (\) | \ |
| @only_queries_with_hints | bit | only return queries with query hints | 0 or 1 | 0 |
| @only_queries_with_feedback | bit | only return queries with query feedback | 0 or 1 | 0 |
| @only_queries_with_variants | bit | only return queries with query variants | 0 or 1 | 0 |
| @only_queries_with_forced_plans | bit | only return queries with forced plans | 0 or 1 | 0 |
| @only_queries_with_forced_plan_failures | bit | only return queries with forced plan failures | 0 or 1 | 0 |
| @wait_filter | varchar | wait category to search for; category details are below | cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory | NULL |
| @query_type | varchar | filter for only ad hoc queries or only from queries from modules | ad hoc, adhoc, proc, procedure, whatever. | NULL |
| @expert_mode | bit | returns additional columns and results | 0 or 1 | 0 |
| @hide_help_table | bit | hides the "bottom table" that shows help and support information | 0 or 1 | 0 |
| @format_output | bit | returns numbers formatted with commas | 0 or 1 | 1 |
| @get_all_databases | bit | looks for query store enabled user databases and returns combined results from all of them | 0 or 1 | 0 |
| @include_databases | nvarchar(4000) | comma-separated list of databases to include (only when @get_all_databases = 1) | a string; comma separated database names | NULL |
| @exclude_databases | nvarchar(4000) | comma-separated list of databases to exclude (only when @get_all_databases = 1) | a string; comma separated database names | NULL |
| @workdays | bit | use this to filter out weekends and after-hours queries | 0 or 1 | 0 |
| @work_start | time | use this to set a specific start of your work days | a time like 8am, 9am or something | 9am |
| @work_end | time | use this to set a specific end of your work days | a time like 5pm, 6pm or something | 5pm |
| @regression_baseline_start_date | datetimeoffset | the begin date of the baseline that you are checking for regressions against (if any), will be converted to UTC internally | January 1, 1753, through December 31, 9999 | NULL |
| @regression_baseline_end_date | datetimeoffset | the end date of the baseline that you are checking for regressions against (if any), will be converted to UTC internally | January 1, 1753, through December 31, 9999 | NULL; One week after @regression_baseline_start_date if that is specified |
| @regression_comparator | varchar | what difference to use ('relative' or 'absolute') when comparing @sort_order's metric for the normal time period with any regression time period. | relative, absolute | NULL; absolute if @regression_baseline_start_date is specified |
| @regression_direction | varchar | when comparing against any regression baseline, what do you want the results sorted by ('magnitude', 'improved', or 'regressed')? | regressed, worse, improved, better, magnitude, absolute, whatever | NULL; regressed if @regression_baseline_start_date is specified |
| @include_query_hash_totals | bit | will add an additional column to final output with total resource usage by query hash | 0 or 1 | 0 |
| @include_maintenance | bit | Set this bit to 1 to add maintenance operations such as index creation to the result set | 0 or 1 | 0 |
| @help | bit | how you got here | 0 or 1 | 0 |
| @debug | bit | prints dynamic sql, statement length, parameter and variable values, and raw temp table contents | 0 or 1 | 0 |
| @troubleshoot_performance | bit | set statistics xml on for queries against views | 0 or 1 | 0 |
| @version | varchar | OUTPUT; for support | none; OUTPUT | none; OUTPUT |
| @version_date | datetime | OUTPUT; for support | none; OUTPUT | none; OUTPUT |
-- Basic execution - returns top 10 queries by CPU
EXECUTE dbo.sp_QuickieStore;
-- Look at top 20 queries by logical reads
EXECUTE dbo.sp_QuickieStore
@sort_order = 'logical reads',
@top = 20;
-- Search for a specific query text
EXECUTE dbo.sp_QuickieStore
@query_text_search = 'SELECT * FROM Orders';
-- Find queries from a specific procedure
EXECUTE dbo.sp_QuickieStore
@procedure_name = 'usp_GetCustomerOrders';
-- Filter to queries that executed at least 1000 times
EXECUTE dbo.sp_QuickieStore
@execution_count = 1000;
-- Show queries with a minimum duration of 500ms
EXECUTE dbo.sp_QuickieStore
@duration_ms = 500;
-- Look for regressions against a baseline period
EXECUTE dbo.sp_QuickieStore
@regression_baseline_start_date = '2025-01-01',
@regression_baseline_end_date = '2025-01-08',
@regression_direction = 'regressed';
-- Expert mode for additional details
EXECUTE dbo.sp_QuickieStore
@expert_mode = 1;
-- Get data from all databases with Query Store enabled, except for specific ones
EXECUTE dbo.sp_QuickieStore
@get_all_databases = 1,
@exclude_databases = 'Head, Shoulders, Knees, Toes';
-- Get data from only specific databases with Query Store enabled
EXECUTE dbo.sp_QuickieStore
@get_all_databases = 1,
@include_databases = 'StacOverflow2013, StackOverflow2010';