About

SQL Server has a standard tool that assists in analysing database performance and database tuning. Called ‘Query Store’, it’s available in SQL Server 2016 (13.0.1601.5) and later.

In Microsoft’s words, “The Query Store feature provides you with insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Query Store simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.”

Query Store is enabled by default in Azure SQL Database and Azure SQL Managed Instance databases.

Query Store is not enabled by default for SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x).

Query Store is enabled by default in the READ_WRITE mode for new databases starting with SQL Server 2022 (16.x).

So even if you’re using the latest SQL Server, the Query Store will still be off based on the database setting in prior versions.

How to turn on Query Store

Open SSMS and run the following

How to use Query Store

Now you have query store running, you need to wait a period while it gathers data. Maybe a week, maybe a month, it depends on your needs.

When you’re ready, expand your database node in SSMS

You will have a folder called, Query Store, expand that.

Double click on Top Resource Consuming Queries, that’s a good place to start. A view will open with graph and analysis of the top queries.

Open

From there, click on the graph to view the top queries and how much percentage of the resource their consuming.

Query Store can also make recommendations, but before you undertake any of these, check in with our support desk for advice first.