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

Summary

SQL Server’s Query Store is like a “black box recorder” for your queries. It tracks query text, execution plans, runtime statistics, and performance history, all in one place. Instead of guessing why a query slowed down, Query Store gives you evidence: which plan changed, what execution time looked like before and after, and which queries are causing trouble.

In this blog, we’ll walk through:

1. What Query Store is and why it matters.
2. How to enable and configure it.
3. A breakdown of Query Store sections (regressed queries, top resource consumers, forced plans, etc.).
4. How to read Query Store execution plans, including what the colors (green, yellow, red) mean.
5. How to decide which execution plan to force (band-aid fix).
6. Step-by-step process for identifying and fixing issues.
7. Examples and T-SQL commands.
8. Best practices and final thoughts.

By the end, you’ll know how to set up Query Store, read its reports, and use it to solve real-world SQL Server performance problems.

---

1. What Is Query Store?

Query Store is a feature introduced in SQL Server 2016 (and newer, including Azure SQL Database). Think of it as a performance history database inside your database.

• Without Query Store: You only see the current execution plan in cache. If SQL Server recompiles or clears cache, the history is gone.
• With Query Store: You keep historical data on queries, plans, and runtime statistics—even across restarts.

📖 Microsoft reference: Monitoring Performance by Using the Query Store

---

2. How to Enable Query Store

Enable it per database.

Enable via T-SQL

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;
GO

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

Enable via SSMS

1. Right-click your database → Properties.
2. Go to Query Store tab.
3. Set Operation Mode to Read Write.

📖 Microsoft reference: ALTER DATABASE SET Options

---

3. Query Store Sections in SSMS

• Regressed Queries – Queries that suddenly started performing worse.
• Top Resource Consuming Queries – Heavy CPU/IO/duration queries.
• Tracked Queries – Let you monitor specific queries.
• Query Variance Reports – Compare performance before/after a time range.
• Forced Plans – Lets you force SQL Server to use a particular plan.

---

4. Reading Execution Plans (and What the Colors Mean)

When you open plans in Query Store:

• Green → Operator is not a concern (efficient).
• Yellow/Orange → Warning; potential inefficiency (missing index, row estimate mismatch, spills to tempdb).
• Red → Heavy resource consumer; investigate.

⚠️ Important: Colors are just hints. A plan can look “green” but still be slower overall depending on parameters and workload. Always confirm with runtime statistics.

📖 Microsoft reference: Execution Plan Icons and Descriptions

---

5. How to Decide Which Execution Plan to Force

Forcing a plan is like applying a band-aid: it stabilizes performance until you fix the root cause. But you don’t choose a plan based just on color. Instead:

Step 1: Compare Historical Plans

• Look at runtime statistics for each plan:
  • Duration
  • CPU time
  • Logical reads
  • Execution count

👉 Force the plan with the best balance of low duration and stable resource usage over time.

Step 2: Look for Stability

• The “good” plan is the one that worked consistently in the past, not just for one execution.
• Avoid forcing a plan that’s only efficient for a small parameter set (parameter sniffing trap).

Step 3: Validate Against Your Workload

• Test the candidate plan with multiple parameter values if the query uses variables or stored procs.
• If it’s consistently good, it’s safer to force.

Step 4: Force the Plan (T-SQL)

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 123;

Step 5: Monitor

• Use Query Store reports to confirm performance stays stable.
• If things worsen, unforce the plan:

EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 123;

6. Step-by-Step: Identifying and Fixing Issues

Here’s a repeatable workflow:

1. Identify the problem query
   • Query Store → Regressed Queries or Top Resource Consuming Queries.
2. Compare execution plans
   • Check which plan was historically faster.
3. Check warnings
   • Yellow warnings may show missing indexes, conversions, or spills.
4. Decide on a fix
   • Update stats, add an index, rewrite the query, or as a short-term fix, force the good plan.
5. Force if necessary
   • Use sp_query_store_force_plan.
6. Verify improvement
   • Monitor runtime stats again in Query Store.

7. Example

Bad query (no index on OrderDate):

SELECT CustomerID, OrderDate, SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2020
GROUP BY CustomerID, OrderDate;

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

• Don’t force blindly — always confirm with Query Store stats.
• Fix root cause (indexes, stats, query rewrites) once stable.

Checklist: Picking the Right Plan to Force

1. Open Query Store → find query (Regressed Queries or Top Resource Consumers).
2. Compare plans side-by-side.
3. Look at avg. duration, CPU, and reads.
4. Confirm the better plan is consistently better (not a one-off).
5. Check warnings (yellow/red icons) but don’t rely only on color.
6. If stable → force the good plan.
7. Monitor results.
8. Unforce if things degrade.

---

Final Thoughts

Query Store is one of SQL Server’s most valuable tools for troubleshooting performance regressions. Colors (green, yellow, red) give you quick visual hints, but the real decision-making comes from Query Store’s historical runtime data.

• Use Query Store reports to identify regressed queries.
• Compare historical plans to find the most stable one.
• If needed, force a plan as a band-aid, but always plan to fix the root cause (indexes, statistics, code changes).

With these steps and the checklist above, you can confidently use Query Store to keep your SQL Server workloads running smoothly.

📖 References:

• Monitoring Performance by Using the Query Store
• Execution Plan Icons
• sp_query_store_force_plan

Workshop: Using Query Store to Fix a Regressed Query

Objective
Walk through a realistic scenario where a query suddenly becomes slower, use Query Store to find the regression, force a better plan, and then put in a longer term fix.

You can do this lab on a test instance with AdventureWorks or any non production database.

---

Step 1: Enable Query Store and Configure Basics

Run this in your test database:

USE YourDatabaseName;
GO

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;
GO

ALTER DATABASE [YourDatabaseName]
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

Quick check that it is on:

SELECT actual_state_desc, desired_state_desc, readonly_reason
FROM sys.database_query_store_options;

Step 2: Create a Sample Query To Track

Use a query that can have very different plans based on parameters. For example:

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 it a few times with different values:

EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
EXEC dbo.GetSalesByCustomer @CustomerID = 12000;
EXEC dbo.GetSalesByCustomer @CustomerID = 13000;

This gives Query Store some initial history.

---

Step 3: Simulate a “Bad” Plan

Now add a non selective predicate to encourage a different plan. For example, change the procedure:

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
    GROUP BY soh.CustomerID, soh.OrderDate;
END
GO

Run this a bunch of times with a parameter that returns a lot of rows:

EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
GO 20

You now have a mix of “old” and “new” plans in Query Store, with different runtimes.

---

Step 4: Find the Regressed Query in Query Store

Use T SQL to pull it up:

SELECT TOP 10
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    qsp.is_forced_plan,
    rs.avg_duration,
    rs.avg_cpu_time,
    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;

Things to look at:

• query_id and plan_id values
• Differences in avg_duration and avg_cpu_time between plans

Pick out:

• A “good” plan with lower average duration and stable stats
• A “bad” plan with much higher average duration

---

Step 5: Compare Plans and Decide Which One To Force

Get estimated plans for the two plan ids you identified:

SELECT qsp.plan_id, qsp.query_plan
FROM sys.query_store_plan AS qsp
WHERE qsp.query_id = @YourQueryId;

Open the XML plans in SSMS and compare:

• Do you see a scan versus a seek
• Is there a warning symbol on any operator
• Are estimates and actual row counts far apart

Combine that with runtime numbers from Query Store to choose the candidate plan you want to force as a short term fix.

---

Step 6: Force the “Good” Plan

Once you know the target query_id and plan_id:

EXEC sp_query_store_force_plan
    @query_id = @YourQueryId,
    @plan_id  = @GoodPlanId;

Confirm the plan is now forced:

SELECT plan_id, is_forced_plan
FROM sys.query_store_plan
WHERE query_id = @YourQueryId;

Run the stored procedure again several times with the same parameters as before:

EXEC dbo.GetSalesByCustomer @CustomerID = 11000;
GO 10

Then re check the runtime stats:

SELECT
    qsp.plan_id,
    rs.count_executions,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_runtime_stats AS rs
    ON qsp.plan_id = rs.plan_id
WHERE qsp.query_id = @YourQueryId
ORDER BY rs.last_execution_time DESC;

You should see execution times stabilize closer to the “good” plan numbers.

---

Step 7: Put In a Longer Term Fix

Plan forcing is a band aid. Now look at root cause fixes:

• Add an index on (CustomerID, OrderDate)
• Rewrite the query to avoid functions on columns
• Update statistics on the underlying tables

Example index:

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

After you deploy the fix and see stable performance:

• Unforce the plan so the optimizer can choose plans again

EXEC sp_query_store_unforce_plan
    @query_id = @YourQueryId,
    @plan_id  = @GoodPlanId;

Monitor Query Store over the next few days to confirm performance stays stable without forcing.

---

Step 8: Clean Up and Review

Optional cleanup for your lab:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = OFF;
GO

DROP PROC dbo.GetSalesByCustomer;

Questions to ask your team after the workshop:

• How would we document which queries are currently forced
• Who owns the decision to force or unforce plans in production
• Do we have a standard baseline report from Query Store for each release

---

Outcome
After this workshop, participants should be comfortable enabling Query Store, finding regressed queries, comparing historical plans, forcing a known good plan when needed, and then implementing a proper long term fix so they are not relying on plan forcing forever.