A Beginner-to-Advanced Guide to Query Store in SQL Server

---

SQL Server’s Query Store is the closest thing the engine has to a flight data recorder. Before Query Store existed, when a query suddenly became slow there was often no evidence to work with. The execution plan that caused the problem was gone. Cache had been cleared, the server had restarted, or SQL Server had recompiled the query and moved on. You were left guessing.

Query Store changes that permanently. It captures query text, execution plans, and runtime statistics inside the database itself, automatically, across restarts, across recompiles, across plan changes. When a query slows down you can see exactly which plan was used before, which plan replaced it, and what the performance difference was in measurable numbers.

This guide covers what Query Store stores, how to enable and configure it, how to read each section in SSMS, what the plan colors mean, and how to make the force plan decision correctly. For the complete screen-by-screen operational guide with bubble chart explanation, plan shape icons, and the full DMV toolkit, see the SQLYARD Query Store Complete Screen-by-Screen Guide.

1 What Query Store Actually Stores Beginner

Understanding what Query Store captures makes every other part of using it easier. Query Store maintains four internal stores as tables inside the database itself. They persist across restarts, survive recompiles, and survive plan cache clears.

• Query Text Store. The SQL text of every captured query, deduplicated. One record per unique query text regardless of how many times it ran.
• Plan Store. Every execution plan used for each query, across time. One query can have many plans accumulated over weeks and months. This plan history is what did not exist before SQL Server 2016.
• Runtime Stats Store. Performance metrics per plan per time interval: duration, CPU, logical reads, logical writes, physical reads, memory grants, row count. The interval length is configurable, defaulting to 60 minutes.
• Wait Stats Store (SQL Server 2017 and later). Wait categories per query per plan. Tells you whether a slow query is waiting on CPU, I/O, locks, or memory without having to guess from system-level wait statistics.

Everything in the Query Store SSMS screens is a visualization of these four stores. When you look at the bubble chart you are looking at the Runtime Stats Store. When you look at a plan you are reading the Plan Store. This connection between the UI and the underlying data makes both easier to understand.

2 How to Enable and Configure Query Store Beginner

Query Store is enabled per database. It is off by default on SQL Server 2016 through 2019. From SQL Server 2022 it is enabled by default on all user databases. On Azure SQL Database it is always on.

-- Enable Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;

ALTER DATABASE YourDatabase
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Recommended production configuration
ALTER DATABASE YourDatabase
SET QUERY_STORE (
    OPERATION_MODE              = READ_WRITE,
    MAX_STORAGE_SIZE_MB         = 2000,      -- increase from 1000MB default
    QUERY_CAPTURE_MODE          = AUTO,       -- exclude infrequent/cheap queries
    STALE_QUERY_THRESHOLD_DAYS  = 30,
    INTERVAL_LENGTH_MINUTES     = 15,         -- finer resolution for OLTP
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    SIZE_BASED_CLEANUP_MODE     = AUTO
);

-- Verify Query Store is active and healthy
SELECT
    desired_state_desc,
    actual_state_desc,
    current_storage_size_mb,
    max_storage_size_mb,
    CAST(current_storage_size_mb * 100.0
         / max_storage_size_mb AS DECIMAL(5,1)) AS StorageUsedPct,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Enable via SSMS

Right-click the database in Object Explorer, select Properties, go to the Query Store tab, and set Operation Mode to Read Write. The configuration options on this tab map directly to the T-SQL SET options above. For production environments the T-SQL approach is preferred because it can be scripted and version-controlled.

3 The Seven SSMS Sections and When to Use Each Beginner

Expand the Query Store node under any database in SSMS to see seven items. Each is a different lens on the same underlying data. They answer different questions and are useful in different situations.

Section	When to Open It	What You Are Looking For
Regressed Queries	First stop when a query was fast then became slow	Queries where recent performance is worse than historical baseline. SQL Server compares for you.
Overall Resource Consumption	Daily check for resource trend patterns	Bar chart of total CPU, duration, I/O across all queries. Useful for spotting when a spike occurred.
Top Resource Consuming Queries	Starting point for general performance tuning	Top 25 queries by CPU, duration, reads, writes, or memory. Sort by different metrics to find different problems.
Queries with Forced Plans	Weekly review of forced plan health	All queries where a plan is forced. Check force_failure_count. A non-zero count means the forced plan is not being applied.
Queries with High Variation	When a query is sometimes fast and sometimes slow	High standard deviation of duration. Usually parameter sniffing. One plan trying to serve all parameter values.
Tracked Queries	When you already know which query to watch	Enter a query ID to monitor one specific query’s performance over time as a line chart.
Query Wait Statistics	When you need to know WHY a query is slow	Wait categories per query: CPU, I/O, Lock, Memory. Narrows root cause immediately. SQL Server 2017 and later.

4 Reading Execution Plan Colors: Green, Yellow, Red Beginner

When you click a query in Query Store and view its execution plan in the bottom pane, individual operators are colored. The colors communicate warnings and estimated cost at a glance.

• Green. No warnings on this operator. It performed as estimated with no red flags.
• Yellow or orange. A warning is present. Hover over the operator to read it. Common warnings include missing index suggestions, implicit conversions that force a scan instead of a seek, row estimate mismatches between estimated and actual rows, and spills to TempDB. Yellow does not mean the operator is the bottleneck. It means something is worth investigating.
• Red. High estimated cost relative to the rest of the plan. The thicker the connecting line between operators the more rows are flowing through that part of the plan. A thick line feeding a red operator is the visual signature of an expensive operation on many rows.

5 How to Decide Which Plan to Force Intermediate

Forcing a plan stabilizes performance while you address the root cause. The decision should never be based on which plan looks cleaner in the diagram. It must be based on measured runtime performance data.

Step 1: Confirm the query has multiple plans. Click the query in Regressed Queries or Top Resource Consuming Queries and look at the right pane. If only one plan shape appears there was no plan change. The problem is elsewhere.

Step 2: Compare runtime numbers, not diagram shapes. Click each plan shape one at a time and read the average duration and average logical reads displayed below. Write them down. The plan with the lowest combination of duration and logical reads over a statistically significant number of executions is the candidate.

Step 3: Check execution count. A plan that ran 3 times with great numbers is not as reliable as one that ran 5,000 times and averaged 50ms. Look for both good performance and sufficient execution history.

Step 4: Understand why the plan changed before forcing. If you can identify the root cause, fix it instead. Statistics went stale, an index was dropped, parameter sniffing compiled a plan for an unrepresentative value. Fixing the root cause means the optimizer chooses correctly on its own and you do not need a forced plan.

Step 5: Force the better plan as a short-term fix. Select the better plan shape in the right pane and click Force Plan in the toolbar, or use T-SQL:

-- Force a specific plan
EXEC sys.sp_query_store_force_plan
    @query_id = 42,    -- replace with actual query_id
    @plan_id  = 7;     -- replace with actual plan_id

-- Unforce when root cause is resolved
EXEC sys.sp_query_store_unforce_plan
    @query_id = 42,
    @plan_id  = 7;

-- Check all forced plans and their failure counts
-- force_failure_count > 0 means the plan is not being applied
SELECT
    qsq.query_id,
    LEFT(qsqt.query_sql_text, 200)      AS QueryText,
    qsp.plan_id,
    qsp.is_forced_plan,
    qsp.force_failure_count,
    qsp.last_force_failure_reason_desc
FROM sys.query_store_plan              qsp
JOIN sys.query_store_query             qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text        qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsp.is_forced_plan = 1
ORDER BY qsp.force_failure_count DESC;

Step 6: Monitor and set a reminder to review. Forced plans are band-aids. After fixing the root cause (adding an index, updating statistics, rewriting the query) unforce the plan and confirm the optimizer now chooses correctly on its own.

6 Step-by-Step: Identifying and Fixing a Regressed Query Intermediate

This is the repeatable workflow for any performance regression investigation using Query Store.

1. Open Regressed Queries. Click Configure at the top right. Set the time range to cover when the problem started. Set the metric to Duration or CPU. Lower the minimum executions threshold if the query runs infrequently.
2. Click the query bubble. Large bubbles high on the Y axis are the most regressed queries on the selected metric. Click one. The right pane shows all plans for that query.
3. Compare plans in the right pane. Click each plan shape. Read the runtime statistics below each one. Identify the historically better plan.
4. Check for warnings. Click the slower plan and examine the execution plan in the bottom pane. Yellow warnings on operators often point to the root cause: missing index, implicit conversion, or row estimate mismatch.
5. Decide on a fix. Update statistics, add an index, rewrite the query, or force the good plan as a short-term stabilizer.
6. Verify improvement. After applying the fix, monitor the query in Tracked Queries or re-run the Regressed Queries report to confirm runtime statistics have improved.

-- Find regressed queries using T-SQL (more flexible than the UI)
-- Shows queries with recent performance significantly worse than historical

WITH PlanStats AS (
    SELECT
        qsp.query_id,
        qsp.plan_id,
        qsrs.avg_duration,
        qsrs.count_executions,
        qsrs.last_execution_time,
        ROW_NUMBER() OVER (
            PARTITION BY qsp.query_id
            ORDER BY qsrs.last_execution_time DESC
        ) AS PlanRecency
    FROM sys.query_store_plan              qsp
    JOIN sys.query_store_runtime_stats     qsrs ON qsrs.plan_id = qsp.plan_id
    WHERE qsrs.count_executions >= 5
)
SELECT
    recent.query_id,
    LEFT(qsqt.query_sql_text, 300)         AS QueryText,
    ROUND(recent.avg_duration / 1000.0, 2) AS CurrentAvgMs,
    ROUND(prior.avg_duration  / 1000.0, 2) AS PriorAvgMs,
    ROUND((recent.avg_duration - prior.avg_duration)
          * 100.0 / prior.avg_duration, 1) AS PctRegression
FROM PlanStats recent
JOIN PlanStats prior
    ON  prior.query_id    = recent.query_id
    AND prior.PlanRecency = 2
JOIN sys.query_store_query      qsq  ON qsq.query_id       = recent.query_id
JOIN sys.query_store_query_text qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE recent.PlanRecency = 1
AND   recent.avg_duration > prior.avg_duration * 1.5  -- 50% slower
ORDER BY PctRegression DESC;

7 Hands-On Lab: Fix a Regressed Query Using Query Store Advanced

This workshop walks through a realistic regression scenario on AdventureWorks. Run it on any non-production instance.

Step 1: Enable Query Store on the test database

USE AdventureWorks2019;
GO

ALTER DATABASE AdventureWorks2019
SET QUERY_STORE = ON;
GO

ALTER DATABASE AdventureWorks2019
SET QUERY_STORE (
    OPERATION_MODE             = READ_WRITE,
    CLEANUP_POLICY             = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES    = 60,
    MAX_STORAGE_SIZE_MB        = 1000
);
GO

-- Verify
SELECT actual_state_desc, desired_state_desc, readonly_reason
FROM sys.database_query_store_options;

Step 2: Create a stored procedure with parameter sensitivity

USE AdventureWorks2019;
GO

CREATE OR ALTER PROC dbo.GetSalesByCustomer
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT soh.CustomerID,
           soh.OrderDate,
           SUM(soh.TotalDue) AS TotalDue
    FROM Sales.SalesOrderHeader AS soh
    WHERE soh.CustomerID = @CustomerID
    GROUP BY soh.CustomerID, soh.OrderDate;
END
GO

-- Run several times to build initial history
EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
EXEC dbo.GetSalesByCustomer @CustomerID = 12000;
EXEC dbo.GetSalesByCustomer @CustomerID = 13000;

Step 3: Simulate a plan change that causes regression

-- Alter the procedure to add a non-sargable predicate
-- This encourages a different execution plan
CREATE OR ALTER PROC dbo.GetSalesByCustomer
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT soh.CustomerID,
           soh.OrderDate,
           SUM(soh.TotalDue) AS TotalDue
    FROM Sales.SalesOrderHeader AS soh
    WHERE soh.CustomerID = @CustomerID
      AND YEAR(soh.OrderDate) = 2013   -- function on column: non-sargable
    GROUP BY soh.CustomerID, soh.OrderDate;
END
GO

-- Run many times to accumulate history for the new (slower) plan
EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
GO 20

Step 4: Find the regressed query

-- Pull all plans for this stored procedure from Query Store
SELECT TOP 10
    qsq.query_id,
    qsp.plan_id,
    LEFT(qsqt.query_sql_text, 200)      AS QueryText,
    qsp.is_forced_plan,
    ROUND(rs.avg_duration / 1000.0, 2)  AS AvgDurationMs,
    ROUND(rs.avg_cpu_time / 1000.0, 2)  AS AvgCPUMs,
    rs.count_executions,
    rs.last_execution_time
FROM sys.query_store_query             AS qsq
JOIN sys.query_store_plan              AS qsp  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text        AS qsqt ON qsq.query_text_id  = qsqt.query_text_id
JOIN sys.query_store_runtime_stats     AS rs   ON qsp.plan_id        = rs.plan_id
WHERE qsqt.query_sql_text LIKE '%GetSalesByCustomer%'
ORDER BY rs.last_execution_time DESC;

-- Note the query_id and the plan_id of the better (lower avg_duration) plan

Step 5: Compare plans and force the better one

-- View plan XML for comparison (open in SSMS to see graphical plan)
SELECT qsp.plan_id, qsp.query_plan
FROM sys.query_store_plan AS qsp
WHERE qsp.query_id = @YourQueryId;  -- replace with actual query_id

-- Force the historically better plan
EXEC sp_query_store_force_plan
    @query_id = @YourQueryId,   -- replace
    @plan_id  = @GoodPlanId;    -- replace

-- Verify it is forced
SELECT plan_id, is_forced_plan, force_failure_count
FROM sys.query_store_plan
WHERE query_id = @YourQueryId;

Step 6: Verify improvement

-- Run the procedure again and check runtime stats
EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
GO 10

-- Compare before and after forcing
SELECT
    qsp.plan_id,
    qsp.is_forced_plan,
    rs.count_executions,
    ROUND(rs.avg_duration / 1000.0, 2)      AS AvgDurationMs,
    ROUND(rs.avg_logical_io_reads, 0)        AS AvgLogicalReads
FROM sys.query_store_plan              qsp
JOIN sys.query_store_runtime_stats     rs   ON rs.plan_id = qsp.plan_id
WHERE qsp.query_id = @YourQueryId
ORDER BY rs.last_execution_time DESC;

Step 7: Fix the root cause and unforce

-- The YEAR() function on OrderDate prevents index seek
-- Fix: rewrite the query to use a sargable range predicate
-- or add a supporting index

CREATE INDEX IX_SalesOrderHeader_Customer_OrderDate
ON Sales.SalesOrderHeader (CustomerID, OrderDate)
INCLUDE (TotalDue);

-- After deploying the fix and confirming stable performance:
EXEC sp_query_store_unforce_plan
    @query_id = @YourQueryId,
    @plan_id  = @GoodPlanId;

-- Monitor over the next few days to confirm the optimizer
-- now chooses correctly without the forced plan

-- Optional cleanup
ALTER DATABASE AdventureWorks2019 SET QUERY_STORE = OFF;
DROP PROC dbo.GetSalesByCustomer;
DROP INDEX IX_SalesOrderHeader_Customer_OrderDate
    ON Sales.SalesOrderHeader;

8 Force Plan Checklist Beginner

Use this checklist every time you consider forcing a plan in production.

• Open Regressed Queries or Top Resource Consuming Queries and confirm the query has multiple plan shapes in the right pane
• Click each plan shape and compare average duration, average CPU, and average logical reads using the runtime statistics numbers, not the plan diagram appearance
• Confirm the better plan has a sufficient execution count to be statistically reliable (at least 10 to 20 executions)
• Check for yellow warnings on the slower plan to identify root cause before forcing
• Investigate whether the root cause can be fixed directly (index, statistics update, query rewrite) without forcing
• If forcing is necessary, force the consistently better plan using sp_query_store_force_plan
• Verify force_failure_count is 0 after forcing to confirm the plan is actually being applied
• Create a ticket to address the root cause so the forced plan can be removed after the fix
• Monitor runtime statistics over the following days using Tracked Queries
• After fixing the root cause, unforce the plan and confirm the optimizer makes good choices independently

References

• Microsoft Docs: Monitoring Performance by Using the Query Store
• Microsoft Docs: Best Practices with the Query Store
• Microsoft Docs: ALTER DATABASE SET Options
• Microsoft Docs: sp_query_store_force_plan
• Microsoft Docs: Execution Plan Icons and Descriptions
• SQLYARD: Query Store Complete Screen-by-Screen Guide for DBAs
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server Blocking vs Deadlocks