Query Store - Open Source
One of the best things about the SQL Server community is how people contribute in different ways. Some write blogs, others speak at events, and many share scripts and tools that make day-to-day work easier.
In this article, I want to highlight two community tools that can help you work with Query Store more effectively.
sp_QuickieStore
A T-SQL stored procedure developed by Erik Darling that quickly digs into Query Store data. Use this tool when you want fast T-SQL-based analysis. I often use this when I want quick insight without opening the SSMS reports.
It retrieves runtime stats (CPU, reads, duration, etc.), query text, plans, wait stats (SQL 2017+), and more for top queries in a database, which will helps identify the worst-performing queries.
For example, this will show the queries that took longer than 500ms. And there are many more options to use.
EXECUTE dbo.sp_QuickieStore @duration_ms = 500,
@database_name = 'StackOverflow2013';
There are some optional parameters, so when I include the @expert_mode = 1 parameter, it will return a lot of additional information:
EXECUTE dbo.sp_QuickieStore @duration_ms = 500,
@database_name = StackOverflow2013',
@expert_mode = 1;
Available in Erik Darling’s open-source scripts repository on GitHub (https://github.com/erikdarlingdata/DarlingData/tree/main/sp_QuickieStore ). Also check out his other scripts, which are great!
DBATools
If you don’t know DBATools yet, it really is time to give it a go. A few months back, I wrote about how this PowerShell library can help you automate tasks. Related to Query Store, there are also some nice commands available. This is especially useful when you’re managing multiple databases or instances.
Recommended by LinkedIn
Copy-DbaDbQueryStoreOption
Reads the Query Store configuration from a source database and applies those settings to the specified target databases. This helps you with standardizing Query Store settings across your environment.
Get-DbaDbQueryStoreOption
This returns the Query Store configuration for the given database. This function can help you audit Query Store configurations for your environment.
Set-DbaDbQueryStoreOption
Modifies the Query Store configuration for the given database(s). You don’t need to use SSMS and/or T-SQL to do this, but this module will do the work for you.
Test-DbaDbQueryStore
This command will evaluate the configuration of Query Store for the given database(s). It can also check if recommended Trace Flags are enabled. For example:
Test-DbaDbQueryStore -SqlInstance $instance |
Select-Object SqlInstance, Database, Name, Value, RecommendedValue, IsBestPractice |
Format-Table -AutoSize
Will give you a list of all the databases on your instance with the setting and if it is a best practice or may need a change. Don’t stress out if your environment is not following the best practice (yet?) as it might be different due to a good reason.
Both tools are free and worth knowing how to use them. Whether you want to dig through Query Store with T-SQL or automate configuration across multiple databases, these community contributions can save you time and make Query Store easier to use.
Previous articles:
Hi Peter, short but effective write up! I recommend everyone to read it. A follow up question: after using one or another tool and finding out the "slow" things, what is the action that DBAs/engineers usually take?