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.
By the way, this check is a part of our SQL Server Health Check service.
How do I turn on the Query store?
To enable it using SQL Server Management Studio (SSMS), follow the steps below:
- Firstly, right-click a database, and then click Properties.
- In the Database Properties dialog box, select the Query Store
- In the Operation Mode (Requested) 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 to thought 10GB at the absolute max (Adjust CLEANUP_POLICY to keep less data, depending on your workload).
In addition, it’s a good idea to enable the trace flags 7752 and 7745.
Alternatively, you can enable it using the ALTER DATABASE statement (TSQL).
- 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
