SQL Server Query Store is a powerful feature introduced in SQL Server 2016 that helps database administrators and developers track query performance over time. By providing insights into query execution, execution plans, and performance trends, Query Store makes it easier to troubleshoot slow-running queries and optimize database performance.
What is Query Store?
Query Store is often referred to as the “flight recorder” for SQL Server, as it continuously captures a history of query executions, along with their performance metrics and associated execution plans. Unlike dynamic management views (DMVs) that reset after a server restart, Query Store retains its data even after restarts, making it a reliable tool for long-term performance tracking.
Key capabilities include:
Enabling Query Store
Query Store is disabled by default on SQL Server databases. We can enable query store by running the below command:
ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON;
We can also configure specific settings such as the retention period, data size, and collection intervals. Here’s an example:
ALTER DATABASE [DatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 15);
Understanding Query Store Components
Analyzing Query Performance
Once Query Store is enabled, we can start leveraging its data to analyze query performance and execution plan behavior.
SQL Server provides several system views to query Query Store data:
We can retrieve performance data using the below query:
SELECT
q.query_id,
qsqt.query_sql_text,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats AS rs ON qp.plan_id = rs.plan_id
JOIN sys.query_store_query_text AS qsqt ON q.query_text_id = qsqt.query_text_id
ORDER BY rs.avg_duration DESC;
This query shows the slowest queries by average duration, along with their execution stats.
SQL Server Management Studio (SSMS) provides built-in Query Store reports under the database context:
Handling Query Plan Regressions
One of the key benefits of Query Store is its ability to capture multiple execution plans for a single query. This helps to identify when SQL Server switches to a less efficient plan.
When we notice a query plan regression, we can force a better-performing plan using Query Store. This prevents SQL Server from using suboptimal plans in the future.
Here’s how we can force a query plan:
EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 3;
To unforce a plan if needed:
EXEC sp_query_store_unforce_plan @query_id = 1;
Using Query Store for Performance Tuning
Query Store enables proactive performance tuning through the following strategies:
Best Practices for Query Store
To get the most out of Query Store, follow these best practices:
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));
Conclusion
Query Store is an indispensable tool for analyzing and tuning SQL Server performance. By leveraging its capabilities, you can track query behavior, identify bottlenecks, and optimize query plans, all while maintaining a history of performance data. Whether you are a DBA or a developer, enabling and using Query Store should be a key part of your SQL Server performance monitoring strategy.
Pramodh P