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 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
