The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Use Query Store to compare a query’s plans and aggregated performance over defined time periods. It preserves query, plan, and runtime-statistics history beyond changes to the plan cache, making it a practical starting point for finding slowdowns. A plan change is a clue—not proof of cause: resource contention, workload changes, data growth, indexes, and statistics can also affect performance.
What Query Store can tell you
Query Store records query text, plans, and aggregated runtime statistics in time intervals. You can compare periods to see whether duration, CPU, I/O, execution count, or other measured values changed, even if a cached plan is no longer present. Microsoft describes the feature as providing insight into query plan choice and performance across SQL Server, Azure SQL Database, Fabric SQL database, Azure SQL Managed Instance, and Azure Synapse Analytics. Availability and specific capabilities vary by product and version; Query Store is available starting with SQL Server 2016. Microsoft’s Query Store monitoring guide explains its scope and operation.
Query Store is historical evidence, not a per-execution trace or an automatic diagnosis. It stores estimated plans and aggregated runtime statistics; it does not establish that a stored plan caused every observed slowdown or capture every active-request condition.
Establish scope and confirm Query Store is enabled
First identify the database platform: boxed SQL Server, Azure SQL Database, Azure SQL Managed Instance, Synapse dedicated SQL pool, or Fabric SQL database. Feature availability and the management experience differ. Check the database’s Query Store state and configuration before interpreting an empty or incomplete history. Query Store settings are configured with database-level ALTER DATABASE ... SET QUERY_STORE options; use the syntax and options documented for your platform and version.
On SQL Server, confirm the engine version as well. Query Store begins with SQL Server 2016, while wait-stat tracking and some monitoring views or capabilities require newer versions. Microsoft’s monitoring documentation and usage scenarios describe version-sensitive behavior.
Build a baseline that makes comparisons meaningful
Choose comparable time windows before deciding that a query regressed. Compare like with like—for example, ordinary business hours with the same period on a prior day or week—and note known releases, maintenance, data growth, or workload changes. Query Store aggregates activity by interval, so a comparison is only as useful as the periods and workload represented in it.
Choose the measurement that matches the symptom. Duration relates to latency; CPU and I/O show resource use; execution count indicates frequency; memory and, where supported, wait categories add other perspectives. Total resource use and average execution time answer different questions. A frequently executed query can rank high in total CPU while each execution is quick; a low-volume query can have poor average latency but little total impact. Maximum and average values also describe different behavior.
Rank #2
For a recurring monitoring routine, track overall resource consumption and the queries that consume the most, then review trends for important user-facing statements. Microsoft’s Query Store usage scenarios cover comparing workloads and identifying resource-consuming queries.
Recommended Free Tools
Find the query and choose the right view
Use SSMS for a visual investigation
In SQL Server Management Studio, open the database’s Query Store reports. Start with Regressed Queries when looking for recent performance deterioration. Use Top Resource Consuming Queries to understand which statements account for substantial workload impact. Select a time range and metric that reflect the problem rather than treating a default ranking as a universal list of “slow” queries.
Microsoft documents dimensions including duration, CPU, memory, I/O, and execution count. Some Query Store views require particular SSMS and SQL Server versions; the best-practices page notes SSMS v18.0 and SQL Server 2017 or later for some views. Check the current usage scenarios and monitoring guidance for your environment.
Use catalog views for scripted analysis
For repeatable or automated review, Microsoft documents Query Store catalog views including sys.query_store_query_text, sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats, and sys.query_store_runtime_stats_interval. The official tuning guide includes examples for recent executions, execution counts, high physical reads, and queries with multiple plans: Monitor Performance by Using the Query Store.
Adapt any sample query to the time intervals and aggregation you intend to analyze. Runtime-statistics rows are interval-based, and blindly combining them can obscure whether a value is an average, maximum, or total. Keep the chosen metric and time window explicit in reports.
Investigate whether a plan change explains the slowdown
Compare the query’s performance trend with its plan history. Multiple plans or a change near the start of a decline makes a plan-choice regression worth investigating, but does not prove causation. Microsoft describes the case in which a new plan is significantly worse as a “plan choice change regression.” Cardinality changes, indexes, and statistics can influence the optimizer’s plan choice, while resource pressure or a workload shift can make performance worse without a harmful plan choice.
Inspect the plan differences alongside the relevant runtime measures. If your platform and version support Query Store wait statistics, use wait categories to help connect a query or plan with possible bottlenecks. Microsoft’s monitoring documentation identifies Query Store wait information for SQL Server 2017 and Azure SQL Database; confirm availability for the specific environment rather than assuming it applies everywhere.
Correlate the timing with deployments, index or statistics maintenance, parameter patterns, data changes, and workload shifts where evidence supports it. If users report an active slowdown or the issue appears instance-wide, supplement Query Store’s historical view with appropriate live diagnostics. Microsoft’s monitoring guide also points to other SQL Server monitoring tools and DMV and Extended Events topics.
Choose a remediation based on evidence
Force a prior plan only for a confirmed plan-choice problem
If the query has multiple plans and evidence shows that a previous plan performs better under the current workload, plan forcing can provide a targeted mitigation. SQL Server attempts to use the forced plan; forcing can fail, in which case the optimizer can proceed normally. Treat forcing as reversible: monitor the result, review whether the underlying conditions changed, and unforce the plan when its justification no longer holds. Microsoft explains plan forcing and regression scenarios in its Query Store usage scenarios.
Consider automatic plan correction where supported
Microsoft’s automatic plan correction uses Query Store workload tracking to identify some plan regressions and recommend a last-known-good plan. The documented SQL Server capability applies to SQL Server 2017 and later; applicability and behavior depend on the environment. It is a supported option to evaluate, not a replacement for checking workload effects and validating the outcome. See Microsoft’s automatic tuning documentation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Keep Query Store data useful and healthy
Query Store writes asynchronously and aggregates runtime statistics over fixed intervals. Capture policy, retention, plan count, and storage settings affect how much useful history remains available. Set them for the workload and the troubleshooting window you need, and monitor Query Store size and health as part of database operations. Microsoft recommends a 900-second (15-minute) interval as a balance between capture performance and data availability; this is a configuration recommendation, not a measured universal optimum. Microsoft’s Query Store collection guide describes the collection model.
For SQL Server 2016 users relying on just-in-time workload insights, account for Microsoft’s note about scalability fixes in KB 4340759, referenced in its monitoring guide.
When built-in monitoring is enough—and when to look beyond it
Query Store is the natural starting point when you need query and plan history inside a database. Consider an estate-monitoring product only if you also need capabilities such as cross-server dashboards, broader alerting, or centralized visibility that your built-in workflow does not provide. Redgate describes its Monitor product as offering multi-platform monitoring, query-performance analysis, alerting, and estate visibility; those are vendor-stated capabilities, and fit, coverage, and current commercial terms should be checked directly at Redgate Monitor.
Free tools Windows power users keep installed
One-click scans. No signup required.
If you want to learn how to interpret plan operators and investigate poor query performance, SQL Server Execution Plans, 3rd Edition is a Redgate-published learning resource, not monitoring software. Its current retail availability is not established here. See the publisher’s book page.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
