9.1 Configuring and Monitoring with Query Store

Why Query Store Matters for DP-300

The plan cache is volatile and memory-only: when SQL Server restarts, memory pressure evicts plans, or plans get recompiled, the history of what ran well and what regressed is gone. Query Store (sometimes written QS) solves this by persisting query performance data in the user database as system tables, behaving like a flight-data recorder for query workloads. For the DP-300 exam, Query Store is the single most important tool in the "Monitor, configure, and optimize" domain because nearly every regression investigation starts with it, and on Azure SQL Database it powers automatic plan correction.

Query Store captures four classes of information per query and per plan:

Because Query Store separates runtime stats per plan, you can see that the same query had a cheap plan last week and an expensive plan today - something the live plan cache, which only keeps the current plan, cannot show.

Enabling and Configuring Query Store

Query Store is a database-scoped feature enabled with ALTER DATABASE ... SET QUERY_STORE = ON. On Azure SQL Database it is enabled by default for new databases with automatic plan correction also enabled; on Azure SQL Managed Instance and SQL Server (2016+) it is off by default and you enable it explicitly. Enable Query Store before you need it - it cannot backfill history that was never captured.

The configuration knobs live under ALTER DATABASE ... SET QUERY_STORE = (...) and map to several categories:

• Operation mode: READ_WRITE (capture and persist) is the default when Query Store is enabled; READ_ONLY freezes capture but keeps reporting on existing data. Use READ_ONLY deliberately when you want to observe without growing the store.
• Capture mode controls which queries get recorded: AUTO (default) skips trivial, small, and no-op queries to save space; ALL captures every query batch; NONE pauses capture while keeping history available for reporting. CUSTOM unlocks granular predicates via custom capture policies.
• Size-based cleanup policy: SIZE_BASED_CLEANUP_MODE = AUTO purges the oldest data when Query Store approaches MAX_STORAGE_MB (default 100 MB on SQL Server; larger defaults on Azure SQL DB). Disable cleanup only for short diagnostic windows.
• Stale query threshold (STALE_QUERY_THRESHOLD_DAYS, default 30 on SQL Server, 90 on Azure SQL DB): queries with no activity for this many days are eligible for cleanup.
• Statistics collection interval (DATA_FLUSH_INTERVAL_SECONDS, default 15 minutes): how often runtime stats are flushed from memory to disk; shorter intervals reduce data loss on crash but add I/O.
• Query capture mode also includes the modern CUSTOM mode, which exposes capture_policy with predicates like EXECUTION_COUNT, TOTAL_COMPILE_CPU_TIME_MS, TOTAL_LOGICAL_READS_MB, and WAIT_TIME_MS thresholds so you can capture only queries exceeding a cost bar - useful on busy warehouses where AUTO still floods the store.

A classic configuration trap: leaving MAX_STORAGE_MB too small on a busy OLTP database causes size-based cleanup to churn, evicting the very history you need for regression analysis. Microsoft guidance is to size Query Store so that cleanup is rare, then monitor sys.database_query_store_options to confirm actual_state_desc stays READ_WRITE and readonly_reason is 0.

Querying Query Store Catalog Views

The core catalog views join on query_id and plan_id. The three most heavily tested:

-- Top 10 queries by average CPU in the last 7 days
SELECT TOP (10)
    q.query_id, q.query_hash, qt.query_sql_text,
    rs.avg_cpu_time, rs.avg_duration, rs.avg_logical_io_reads, rs.count_executions
FROM sys.query_store_runtime_stats rs
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 rs.last_execution_time > DATEADD(day, -7, SYSUTCDATETIME())
ORDER BY rs.avg_cpu_time DESC;

Other catalog views to know: sys.query_store_query_variant (for the plan variants produced by Parameter Sensitive Plan optimization), sys.query_store_replica_specs (for readablesecondary stats), and sys.query_store_wait_stats for wait breakdowns. The built-in Query Store reports in SSMS and Azure portal wrap these views: Top Resource Consuming Queries, Regressed Queries, Queries with Forced Plans, Queries with High Wait Time, and Tracked Queries.

Forced Plans and Automatic Plan Correction

When the optimizer picks a bad plan, you can pin a known good plan with sp_query_store_force_plan @query_id, @plan_id. A forced plan is one that the optimizer must use regardless of new statistics or parameter values - useful for parameter-sensitivity regressions. Force the plan only after confirming it is stable across parameter distributions; a forced plan that was good for one parameter set can backfire for others.

Automatic plan correction is the cloud-native extension: on Azure SQL Database and SQL Managed Instance, the engine watches Query Store data and, when it detects that the last forced good plan outperforms the current plan, automatically forces it via sp_query_store_force_plan and records the action. If the forced plan later regresses, automatic tuning unforces it. The verification is continuous - you see these as actions in sys.dm_db_tuning_recommendations with status Pending, Executing, Verifying, Success, Reverted, or Error. You can also script a recommendation manually with the T-SQL it emits.

Interplay points the exam likes: Query Store must be ON and READ_WRITE for automatic plan correction to work; forcing is per query, not per database; forcing does not change the plan the optimizer generates, only the one it uses; and a forced plan survives statistics updates but is removed if the underlying schema changes. On SQL Server on Azure VMs, automatic plan correction is also available when Query Store is enabled, but you must opt in via database-scoped configuration AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON).

Platform Differences at a Glance

• Azure SQL Database: Query Store ON by default, automatic plan correction ON by default, larger default storage and stale threshold, captures readable-secondary stats.
• Azure SQL Managed Instance: off by default; once enabled behaves like SQL Server but supports automatic plan correction via database-scoped config.
• SQL Server (on Azure VMs and on-premises): off by default in 2016-2019, ON by default in 2022+ for new databases; automatic plan correction requires SQL 2017+ and explicit opt-in.

The exam frames Query Store as the gateway to automatic tuning: a scenario describing intermittent query regression on Azure SQL Database almost always resolves to confirming Query Store is enabled and automatic plan correction is on, then inspecting sys.dm_db_tuning_recommendations.

Practical Workflow: Diagnosing a Regression

A repeatable investigation pattern when a user reports "the report got slow overnight":

1. Open the Regressed Queries report (or query sys.query_store_runtime_stats for the query hash) scoped to the last 24-48 hours.
2. Identify the query whose avg_duration or avg_logical_io_reads jumped; note its query_id.
3. Pull all plans for that query_id from sys.query_store_plan joined with runtime stats - you will typically see two plans, the old cheap one and the new expensive one, with a compile timestamp near the regression start.
4. Compare the two plan XMLs: check for a missing index hint, a scan instead of seek, a different join type, or parameter-sniffing indicators.
5. If the old plan is consistently better across parameter distributions, force it with sp_query_store_force_plan; on Azure SQL DB watch for automatic plan correction to do this for you.
6. Verify the force took effect by re-running the query and checking sys.query_store_plan for is_forced_plan = 1 and improved avg_duration.

This workflow is the connective tissue between sections 9.1, 9.3, and 9.4: Query Store tells you what regressed; the execution plan tells you why; and the index or query construct change tells you how to fix it permanently.