Introduction
- The Query Store is a feature in SQL Server that provides insight into query performance by tracking and storing detailed information about query execution over time.
- The Query Store helps database administrators and developers troubleshoot performance issues by allowing them to analyze query execution history, identify regressions, and compare performance before and after changes.
How Query Store Works
The Query Store operates by automatically capturing and storing query-related data in a dedicated database repository. It records information about query text, execution plans, runtime statistics (such as execution counts and duration), and wait statistics. This data is stored in system tables and can be accessed through system views.
Query Store can be enabled in two ways
- To enable the Query Store using GUI
Right click on the database on which you want to enable Query Store and click on Query Store and then modify Operation Mode to Read Write
- To enable the Query Store by executing the following command:
ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON;
Query Store Modes
- Off: Query Store is disabled.
- Read-Only: Captured data is preserved, but new data is not collected.
- Read-Write: Both capture and retrieval of data are allowed.
Query Store options
- Data Flush Interval option, an interval in minutes can be set which shows how frequent the query runtime statistics and query execution plans will be flushed from memory of SQL Server instance to disk. By default it is 15 minutes:
- Statistics Collection Interval option defined aggregation interval of query runtime statistics that should be used inside the Query Store. By default, it is set to 60 minutes.
- Max Size (MB) option is for configuring the maximum size of the SQL Server Query Store. By default it is 100 MB, when the Max size is reached the Operation Mode will be switched to the Read Only mode, automatically, and new query execution plan and query runtime statistics will not be collected:
- Query Store Capture Mode option determines what type of query will be captured in the Query Store
- Size Based Cleanup Mode option is for cleaning the Query Store data when the maximum size in the Max Size is reached to 90% of capacity. The cleanup process will remove the oldest and less expensive query data.
- Stale Query Threshold (Days) option is for defining how long the data will stay in the Query Store. By default, it is 30 days
Query Store Views and Functions
- query_store_query: Provides information about queries stored in the Query Store.
- query_store_plan: Contains execution plan information for each query.
- sys.query_store_runtime_stats: Includes runtime statistics like execution count, total duration, and more.
Once we enable the Query Store, refresh and expand the database. The Query Store folder will appear with the list of available built-in reports:
Reports
The Query Store will start to collect query runtime statistics and query Execution Plans. Built-in reports uses the collected data to analyze them and show them in report.
Conclusion
The Query Store is a powerful feature in SQL Server that empowers developers and DBAs to monitor and optimize database performance. By capturing detailed information about query execution and performance, the Query Store allows for easy identification of regressions, troubleshooting of performance issues, and tuning of queries. With its ability to store and compare execution plans over time, the Query Store provides a comprehensive view of database behavior, making it an essential tool for maintaining and improving SQL Server performance.
