Best practices to enable SQL Server Query Store

What is the Query Store?

The SQL Server Query Store is a feature introduced in SQL Server 2016. It provides you with insight on query plan choice and performance against a specific database.

Why should I enable it?

This information helps in identifying performance issues even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.

How do I turn on a query store?

To enable it using SQL Server Management Studio (SSMS):

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

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

Best practices indicate that you should set QUERY_CAPTURE_MODE to AUTO and MAX_STORAGE_SIZE_MB to 1GB to thought 10GB at the absolute max (Adjust CLEANUP_POLICY to keep less data, depending on your workload).

Also, it’s a good idea to enable the trace flags 7752 and 7745.

Alternatively, you can enable it using the ALTER DATABASE statement (TSQL).

1. Change the [XXXX] to your database name:

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

More information:

Microsoft – Best practice with the query store
Microsoft – Monitoring performance by using the query store