SQL Server Query Store: A Practical Beginner's Guide

Query Store is like a flight recorder for your database. It keeps a history of the queries that ran, the execution plans they used and how they performed, so you can answer questions such as "what changed since yesterday?" without having caught the problem live. If you use SQL Server 2016 or later, or Azure SQL, it is one of the most valuable features you are probably not using enough.

Is Query Store already on?

Query Store is enabled by default in Azure SQL Database and for new databases created in SQL Server 2022. Older databases, including those upgraded from earlier versions, may have it off. Check with:

SELECT actual_state_desc, desired_state_desc,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc
FROM sys.database_query_store_options;

If actual_state_desc shows READ_ONLY when you expected READ_WRITE, the most common reason is that it has reached its maximum size and stopped collecting.

Turn it on with sensible settings

ALTER DATABASE [YourDb]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,          -- skip trivial, rarely run queries
    MAX_STORAGE_SIZE_MB = 2048,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

• QUERY_CAPTURE_MODE = AUTO avoids filling Query Store with one-off ad hoc statements.
• MAX_STORAGE_SIZE_MB should be big enough for your workload; busy databases with many distinct queries need more.
• SIZE_BASED_CLEANUP_MODE = AUTO removes old data as it approaches the limit, so it does not flip to read-only.

The four reports worth knowing

In SSMS, expand the database, then Query Store. These reports cover most day-to-day needs:

• Top Resource Consuming Queries: the heaviest queries by CPU, duration, reads or executions over the time window you choose. Start here for general tuning.
• Regressed Queries: queries that got worse compared with an earlier period. Ideal for "it was fine last week".
• Queries With High Variation: queries whose performance swings a lot, often a sign of parameter sniffing.
• Overall Resource Consumption: a timeline of total load, useful to find when a problem started.

Click any query to see each plan it has used as a coloured dot on a chart. Different colours mean different plans, so a jump in duration that lines up with a new colour usually means a plan change caused the slowdown.

Compare plans and force the good one

When a query has a good plan and a bad plan, you can select both in the report and click Compare Plans to see what changed, such as a seek turning into a scan or a different join type.

To stop the problem quickly, force the good plan, either with the Force Plan button or in T-SQL:

EXEC sp_query_store_force_plan @query_id = 1234, @plan_id = 56;

-- Later, to remove it:
EXEC sp_query_store_unforce_plan @query_id = 1234, @plan_id = 56;

Plan forcing is an excellent emergency fix. Treat it as temporary: find the root cause, such as statistics, parameter sniffing or a missing index, and unforce the plan once it is fixed. Forced plans can also fail if an index they rely on is dropped, so check the Queries With Forced Plans report occasionally.

Query the data yourself

The reports are built on catalog views you can query directly. For example, the slowest queries by average duration in the last 24 hours:

SELECT TOP (10)
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions)  AS executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_runtime_stats_interval i
     ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
JOIN sys.query_store_plan p        ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q       ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY avg_duration_ms DESC;

Query Store stores times in UTC and durations in microseconds, which is why the script divides by 1,000.

Good habits

• Check Query Store after every deployment or upgrade, using the Regressed Queries report.
• Before changing database compatibility level, make sure Query Store has a baseline. It makes it much easier to spot and fix regressions afterwards.
• Watch its size and state; a read-only Query Store silently stops recording.
• Prefer fixing the cause over leaving plans forced forever.

Related reading:

• How to Find Slow Queries in SQL Server
• SQL Server Parameter Sniffing: What It Is and How to Fix It
• How to Read a SQL Server Execution Plan