How to give developers access to Query Store

Let’s have devs look at their own query performance. Yes, please, sign me up for that! Sometimes, it’s hard for me to know the best course of action, especially when they are using Entity Framework, but it’s a great start for them to use Query Store to see how impactful their queries are. I’m happy to help them decipher results if they are confused, but I really like performance tuning being a team sport. I was giving them a list of queries with, for example, high CPU usage, but it was even better when they could go in there and use Query Store for themselves on a regular basis.

What is Query Store?

Per Microsoft:

The Query Store feature provides insight into query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. 

Common scenarios for using the Query Store feature are:

• Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
• Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
• Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
• Audit the history of query plans for a given query.
• Analyze a particular database’s resource (CPU, I/O, and Memory) usage patterns.
• Identify top n queries that are waiting on resources.
• Understand wait nature for a particular query or plan.

Enabling Query Store

In some varieties, it is enabled by default; in others, it’s not.

• Query Store is enabled by default for new Azure SQL Database and Azure SQL Managed Instance databases.
• Query Store is not enabled by default for SQL Server 2016, 2017, and 2019.
• It is enabled by default in the READ_WRITE mode for new databases starting with SQL Server 2022.

If it’s not enabled already, it’s super easy to enable.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Starting with SQL Server 2017 and Azure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

You can see more options here if you want to tweak them.

Best Way to Use Query Store

Use Erik Darling’s sp_QuickieStore. This makes it SO much easier to use the Query Store results and get to the brass tacks of the issues you need to look at.

There is a way to get Query Store info in SSMS with built-in reports, but I use Azure Data Studio, which doesn’t appear to have any way to use these reports. If you ask me, sp_QuickieStore is the way to go in either SSMS or ADS. There are also queries on system tables provided by Microsoft, but sp_QuickieStore makes it very easy to query the Query Store, especially when I want to hand something off to the devs.

It is also easy to grant sp_QuickieStore to devs and access to the Query Store itself. The following script will work in SQL Server 2022 and Azure SQL but not in previous versions. Granular permissions are available in newer versions.

GRANT VIEW DATABASE PERFORMANCE STATE TO dev_group; 
GRANT EXEC ON dbo.sp_QuickieStore TO dev_group; 

If you are on previous versions of SQL Server, you will need to use this script instead.

GRANT VIEW DATABASE STATE TO dev_group; 
GRANT EXEC ON dbo.sp_QuickieStore TO dev_group; 

You can refer to this link if you need people to have additional permissions to the Query Store. I don’t want devs to have more than read, though.

Using sp_QuickieStore

It’s very easy to use sp_QuickieStore, and I have a whole post on Erik’s stored procs and how/if you can use them in Azure SQL. I like using CPU to start.

EXEC sp_QuickieStore 
     @sort_order = 'cpu', 
     @top = 10; 

There are lots of other options, as well. You can get help right from the proc itself.

EXEC sp_QuickieStore @help = 1;  

This will show you all the goodies you can use inside this stored proc.

I just have the devs start with CPU and then we can go from there if they run out of high CPU queries.