SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. This will helps you to identifying performance problems in the SQL Server. Query Store basically captures and stores the history of query execution plans and its performance data.
Query store is a database level feature, which is disabled by default. To enable this follow below steps.
- Run the below Query to Enable Query Store against database.
SET QUERY_STORE = ON
- Run the below To Desable and clear Query Store
ALTER DATABASE [DatabaseName]
SET QUERY_STORE CLEAR;
- The following script sets a new Max Size.
ALTER DATABASE [DatabaseName]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024)
- To set different value for Data Flush Interval.
ALTER DATABASE [DatabaseName]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900)
- To Set Statistical Collection Intervel.
ALTER DATABASE [DataBaseName]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60)
- To Set Time based cleanup policy.
ALTER DATABASE [DatabaseName]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90))
- To set size based auto cleanup.
ALTER DATABASE [DatabaseName]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);
Once you Enabled Query Store you can see Query Store Folder Under Database
Open the Top Resource Consumed Query, You can see the SQL Server start itself to analyze Query performance with execution plan
