Introduction
As databases grow, maintaining consistent query performance becomes increasingly challenging. Changes in data volume, indexes, statistics, or execution plans can cause queries that once ran quickly to become noticeably slower. Identifying the root cause manually can be difficult, especially in production environments where performance issues affect users.
Query Store is a built-in SQL Server feature that continuously collects query history, execution plans, runtime statistics, and performance metrics. It helps database administrators and developers monitor query behavior over time and quickly identify performance regressions.
In this article, you'll learn how Query Store works, how to use it to troubleshoot slow queries, and the best practices for maintaining a high-performing SQL Server database.
What Is Query Store?
Query Store automatically captures information about executed queries and stores it inside the database.
It records details such as:
Query text
Execution plans
Execution count
CPU usage
Duration
Memory consumption
Runtime statistics
Instead of relying on temporary execution plans in memory, Query Store provides historical performance data that remains available even after SQL Server restarts.
Why Use Query Store?
Query Store offers several advantages for database performance tuning:
Tracks query performance over time
Detects execution plan changes
Identifies slow-running queries
Helps compare query performance before and after deployments
Simplifies troubleshooting
Supports execution plan forcing when appropriate
These capabilities make it one of the most valuable tools for diagnosing SQL Server performance issues.
Enable Query Store
Query Store can be enabled at the database level.
ALTER DATABASE SalesDB
SET QUERY_STORE = ON;
Once enabled, SQL Server begins collecting query execution data automatically.
View Top Resource-Consuming Queries
You can identify expensive queries by reviewing Query Store reports in SQL Server Management Studio (SSMS) or by querying Query Store system views.
Example:
SELECT
q.query_id,
qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id;
This helps locate queries that deserve further investigation.
Identify Execution Plan Changes
Sometimes a query becomes slower because SQL Server chooses a different execution plan.
For example:
Before deployment: Index Seek
After deployment: Table Scan
Query Store keeps previous execution plans, making it easy to compare performance and determine when a regression occurred.
This historical view is especially useful after schema changes or software updates.
Force a Stable Execution Plan
If a newer execution plan performs poorly, Query Store allows you to force a previously successful plan.
EXEC sp_query_store_force_plan
@query_id = 15,
@plan_id = 3;
Plan forcing should be used carefully and reviewed periodically, as data distribution and workloads can change over time.
Monitor Query Performance Trends
Query Store makes it possible to monitor trends instead of looking at a single execution.
Useful metrics include:
Average execution time
CPU usage
Logical reads
Execution count
Wait statistics
Memory consumption
Tracking these metrics over time helps identify gradual performance degradation before users notice it.
Improve Slow Queries
Once a slow query has been identified, consider the following optimization techniques:
Create or update indexes.
Rewrite inefficient SQL statements.
Reduce unnecessary joins.
Return only the required columns.
Update database statistics.
Review execution plans for expensive operations.
Query Store helps verify whether these improvements actually reduce execution time.
Real-World Example
Imagine an online retail application where customers report that the order history page has become slow.
Using Query Store, you discover that:
The query execution time increased from 150 ms to 2.5 seconds.
A recent deployment introduced a different execution plan.
The new plan performs a table scan instead of using an existing index.
After reviewing the execution plans, you update the relevant index and confirm through Query Store that the query now consistently runs in under 200 ms.
Without historical performance data, finding this regression would have taken much longer.
Monitor Query Store Regularly
Query Store provides the most value when it is monitored as part of routine database maintenance.
Review:
Frequently executed queries
Queries with increasing execution time
High CPU queries
Long-running reports
Plan regressions
Failed or cancelled executions
Regular monitoring helps prevent small performance issues from becoming major production problems.
Best Practices
When using Query Store, follow these recommendations:
Enable Query Store on production databases unless there is a specific reason not to.
Review Query Store reports regularly instead of waiting for user complaints.
Investigate execution plan changes after deployments.
Force execution plans only when testing confirms they consistently improve performance.
Keep statistics updated so the query optimizer can generate efficient plans.
Monitor CPU, memory, and I/O usage alongside Query Store metrics.
Test query optimizations in a non-production environment before deploying them.
Periodically review Query Store settings and retention policies to manage storage efficiently.
Conclusion
Query Store is an essential feature for monitoring and optimizing SQL Server performance. By collecting historical query execution data, tracking execution plans, and highlighting performance regressions, it provides developers and database administrators with the information needed to diagnose and resolve slow queries quickly.
When combined with good indexing strategies, updated statistics, and regular performance monitoring, Query Store becomes a powerful tool for maintaining fast, reliable, and scalable SQL Server databases.
Join the conversation! Your thoughts help the community grow.