Best Practices for Activating SQL Server Query Store for a Database

Best Practices for Activating SQL Server Query Store for a Database

What is Query Store?

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

It provides information about query plan selection and performance against a given database.

Why should we enable it?

This information helps identify performance issues even after SQL Server has been restarted or upgraded. All data captured by SQL Server Query Store is stored on disk.

How Do We Open the Query Store?

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

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

Above all, best practices indicate that you should set QUERY_CAPTURE_MODE to AUTO and MAX_STORAGE_SIZE_MB to 1GB with an absolute maximum of 10GB (set CLEANUP_POLICY to hold less data depending on your workload).

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

1. [XXXX]’i database’i adınızla değiştirin:

ALTER DATABASE [XXXX]

SET QUERY_STORE = ON

(

OPERATION_MODE = READ_WRITE,

MAX_STORAGE_SIZE_MB = 1024,

QUERY_CAPTURE_MODE = AUTO

);