How To Enable SQL Server Query Store [Best Practices]

What is the SQL Query Store?

The SQL Server Query Store is a feature introduced in SQL Server 2016.

Query Store provides insights into query plans and performance on a specific database.

Why should I enable Query Store?

Query Store is great for troubleshooting performance issues because it keeps execution history stored on disk within the database.

This means you can still access that data even if SQL Server restarts. On the other hand, DMVs don’t have this kind of persistence – their data disappears as soon as SQL Server restarts.

Bonus tip: Query Store data can also be analyzed by Database Engine Tuning Advisor (DTA) to generate workload-based recommendations for indexes and partitioning. Always test any suggested changes in a non-production environment first.

How do I turn on the Query Store?

These are steps how I do it from SSMS:

1. Right-click a Database
2. Click Properties
3. In the Database Properties dialog box, select the Query Store.
4. In the Operation Mode (Requested) box, select Read Write.

Note: SQL Server Query Store requires at least version 16 of SQL Server Management Studio.

Best practices is to set QUERY_CAPTURE_MODE to AUTO and MAX_STORAGE_SIZE_MB to 1GB, with 10GB as the absolute maximum (adjust CLEANUP_POLICY to keep less data, depending on your workload).

It’s a good idea to enable the trace flags 7752 and 7745.

Here is T-SQL how to enable Query Store using the ALTER DATABASE statement.

ALTER DATABASE [XXXX]
SET QUERY_STORE = ON (
		OPERATION_MODE = READ_WRITE
		,MAX_STORAGE_SIZE_MB = 1024
		,QUERY_CAPTURE_MODE = AUTO
		);

Query Store gives you the data; someone still has to act on it every month. Under Red9's SQL managed services, senior DBAs review Query Store trends and tune the top offenders as part of monthly optimization.

More information

• Microsoft – Query Store best practices