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?

Information Query Store provides helps in identifying performance issues even after SQL Server restart. Which SQL DMVs don’t.

All data SQL Server Query Store captures are stored on disk.

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 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 TSQL 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
		);

More information

• Microsoft – Query Store best practices
• Microsoft – Monitoring performance by using the Query Store