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):
- 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.
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).
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 );