SQL Server / Advanced / Lesson 13

Query Store and performance investigation

Investigate a regression using history, workload impact, and controlled comparisons. Understand Query Store state, parameter sensitivity, and the limits of plan forcing.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Read Query Store configuration and interval metrics.
Prioritize workload cost rather than one slow sample.
State SQL Server 2022 feature requirements precisely.

Define a measurable regression

A regression is a meaningful deterioration against a comparable baseline, not simply an execution that felt slow. Record query identity, parameter class, time window, execution frequency, latency distribution, CPU, reads, and relevant waits. Confirm whether the problem affects all callers or one data-heavy tenant. A ten-second query executed hourly can matter less than a fifty-millisecond query executed thousands of times per minute.

Separate application latency from database elapsed time. Connection acquisition, network transfer, result consumption, and serialization may dominate a request even when the server plan is unchanged. Compare equivalent workloads and result sizes. State the success criterion before changing indexes or hints, and retain enough evidence to reverse an intervention that helps one parameter class while harming another.

Inspect Query Store before trusting its history

Query Store retains query, plan, and runtime history in the database. It was introduced in SQL Server 2016 and is available in the 2019 baseline. Starting with SQL Server 2022, it is enabled by default for newly created databases; upgraded or restored databases can retain earlier settings. Always inspect actual state rather than inferring it from the engine version.

Requested READ_WRITE does not guarantee that capture is currently writable. Storage limits or other conditions can leave the actual state READ_ONLY. Review capture policy, retention, maximum size, and readonly_reason. Query Store is not an unlimited audit log and does not promise every literal execution is captured forever. Its overhead and data lifecycle belong to the operational design.

sql Read Query Store operating state

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

Values describe the current database. Empty or limited history requires investigation before comparing periods.

Compare interval metrics correctly

Runtime statistics aggregate executions over intervals and plans. Weight average duration by execution count when combining groups; averaging averages directly can misrepresent workload cost. Exclude or separate aborted and exception executions as the question requires. The query illustrates ranking plans by accumulated duration and keeps regular completed execution statistics only. Units are converted from microseconds to milliseconds.

A plan-level total does not by itself identify one business operation or root cause. Join to query and query-text views under appropriate permissions, select comparable time intervals, and relate a plan change to measured behavior. Do not publish captured query text containing sensitive literals. Query Store evidence should narrow a hypothesis, not replace actual-plan and workload analysis.

sql Rank captured plans by aggregate duration

SELECT TOP (10) plan_id,
 SUM(count_executions) AS executions,
 SUM(avg_duration*count_executions)/1000.0 AS total_duration_ms
FROM sys.query_store_runtime_stats
WHERE execution_type=0
GROUP BY plan_id
ORDER BY total_duration_ms DESC,plan_id;

This sums execution time, not wall-clock occupancy; concurrent executions can overlap. A real comparison must additionally filter matched time intervals.

Recognize skew and version-gated help

Parameter sensitivity occurs when one cached plan is unsuitable for substantially different parameter distributions. Reproduce small and large parameter classes with representative data. Check statistics, access paths, estimated rows, memory grants, and spills. Parameterization remains essential for safety; replacing it with concatenation is not a responsible tuning strategy.

SQL Server 2022 introduced Parameter Sensitive Plan optimization at compatibility level 160 for eligible parameterized statements. It can retain variants rather than relying on a single plan, but it is not a promise that every query or skew pattern qualifies. SQL Server 2019 does not gain this feature merely because a client tool is new. Verify engine, compatibility, and feature settings.

Use forcing as a monitored intervention

A known-good plan can sometimes stabilize a regression while the underlying cause is investigated. Plan forcing is an intervention with eligibility and failure conditions, not permanent proof of optimality. Track force failures, compare representative parameter classes, and assign an owner and review date. Schema and workload changes can make an old good plan unsuitable.

Prefer the smallest evidence-backed change, validate result correctness, and compare latency and resource costs after deployment. Keep a rollback path for index, hint, or compatibility changes. A tuning record should say what was measured, why the hypothesis fit, and what would invalidate it. Historical charts are useful only when their capture and comparison assumptions remain clear.

Common errors and fixes

Query Store history stopped growing.

Why it happens: Actual state became read-only or capture policy excluded the workload.
How to fix it: Inspect state, reason, size, and policy before an approved configuration change.
Prevent it next time: Monitor capture health.

Averaged averages misranked workload.

Why it happens: Groups with different execution counts received equal weight.
How to fix it: Use execution-weighted totals or averages.
Prevent it next time: State metric units and weights.

PSP was expected on SQL Server 2019.

Why it happens: Feature requirements were inferred from tool freshness.
How to fix it: Verify SQL Server 2022 and compatibility 160 requirements.
Prevent it next time: Record version gates with every tuning proposal.

Check your understanding

1. One plan averages 10 ms over 1000 executions; another averages 1000 ms once. Which consumed more duration?

Show worked solution

The first accumulated 10000 ms versus 1000 ms. This supports workload prioritization, although user-facing tail latency and business criticality may still justify investigating the second.

2. After an engine upgrade Query Store is OFF. Is that inconsistent with the 2022 default?