Databases Azure SQL Database

Azure SQL Query Store

Find the exact query plan regression that appeared after an application deployment or database compatibility-level change.; Compare normal and incident windows before deciding whether a SKU scale-up is justified.

Source: Microsoft Learn - Monitor performance by using the Query Store Reviewed 2026-06-02

Exam trap: Assuming Azure CLI directly exposes all Query Store plan details, then missing the required T-SQL or SQL tool investigation.
Production check: Confirm Query Store is enabled and writable before the next release window, not after the first outage.

Practice this topic Microsoft Learn

Article details and learning context

Aliases: Azure SQL Query Store
Difficulty: fundamentals
CLI mappings: 4
Last verified: 2026-06-02
Review level: premium
Article depth: field-manual-complete

Related context No curated learning path mapped yet; use the category trail.

Compare related concepts Search related resources Browse the glossary

Understand the concept

In plain English

Azure SQL Query Store is the flight recorder for query performance inside a database. Instead of guessing why a page suddenly slowed down, you can see which queries ran, which execution plans they used, how long they took, and whether a plan changed after a deployment or data shift. Developers use it to find regressions, database administrators use it to compare time windows, and operations teams use it to prove whether the database engine, code, index design, or workload pattern caused the slowdown.

Why it matters

Query Store matters because most database incidents begin with a vague complaint: the app is slow. Without historical query evidence, teams burn hours comparing dashboards, blaming the network, or scaling compute before knowing whether a bad plan appeared. Query Store preserves the before-and-after view needed to connect a deployment, statistics change, index removal, parameter pattern, or compatibility-level change to real query behavior. It also helps prevent overreaction. A team can tune one query or force a stable plan instead of doubling database spend. For learners, it turns performance tuning from folklore into measurable evidence tied to plans, durations, waits, and execution counts.

Official wording and source

Microsoft Learn describes Query Store as a performance-troubleshooting feature for SQL Server and Azure SQL that captures query text, execution plans, runtime statistics, and wait statistics over time. It helps teams identify plan regressions, compare workload behavior, and force a known-good plan when needed.

Open Microsoft Learn

Technical context

Technically, Query Store lives inside the database and records query plans, runtime statistics, wait information, capture settings, retention policy, and plan forcing state. In Azure SQL Database it supports performance troubleshooting for single and pooled databases, and it complements metrics, automatic tuning, indexes, and application telemetry. It is not a separate Azure resource, so operators usually inspect it through T-SQL, SQL tools, portal performance views, and adjacent Azure CLI commands that identify the database, SKU, region, and monitoring configuration.

Exam context

Compare with

Application Insights Azure Monitor Azure SQL Query Store

Where it is used

Where you see it

In SQL Server Management Studio or Azure Data Studio, Query Store appears under database performance views showing query texts, plans, regressions, runtime statistics, and forced-plan status.
In Azure portal Query Performance Insight, Query Store-backed data helps surface top resource-consuming and long-running queries for single and pooled Azure SQL databases during release reviews.
In T-SQL catalog views such as sys.database_query_store_options, operators confirm desired state, actual state, retention settings, cleanup policy, and capture mode during incident reviews.

Common situations

Find the exact query plan regression that appeared after an application deployment or database compatibility-level change.
Compare normal and incident windows before deciding whether a SKU scale-up is justified.
Force a known-good plan temporarily while a safer index or query rewrite is prepared.
Build a performance baseline before major schema, ORM, parameterization, or statistics changes.
Identify top resource-consuming queries that are driving CPU, duration, waits, or support tickets.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Dispatch regression after ORM release Scenario, objectives, solution, measured impact, and takeaway.

Scenario

A logistics software provider saw driver-dispatch screens slow from two seconds to twenty-two seconds after a Friday ORM release changed parameter patterns in its routing query.

Goals

Restore P95 dispatch response below three seconds before Monday peak
Identify whether the release, indexes, or Azure SQL capacity caused the regression
Avoid an emergency scale-up unless evidence showed capacity was the blocker
Create a repeatable release baseline for future database-sensitive changes

Approach using Azure SQL Query Store

The database team used Azure CLI to confirm the production database, service objective, and metric window, then reviewed Query Store for queries whose duration changed after the deployment. Query Store showed one stored procedure had switched from a selective seek plan to a broad scan plan for common city routes. Engineers compared the previous stable plan, forced it for the affected query, and opened a code fix to stabilize parameter handling. They also added a release checklist requiring Query Store snapshots before ORM upgrades and after major traffic windows.

Potential outcomes

P95 dispatch latency fell from twenty-two seconds to 2.4 seconds within forty minutes
The team avoided a proposed four-vCore scale-up that would have added unnecessary monthly cost
Root cause was tied to one query plan instead of a general database outage
Future releases now include before-and-after Query Store regression checks

What to learn

Query Store gives teams the evidence to fix the exact slow query instead of treating every performance incident as a capacity problem.

Scenario 02 Admissions portal capacity decision Scenario, objectives, solution, measured impact, and takeaway.

Scenario

A university admissions office expected application traffic to triple during scholarship week, but the Azure SQL database was already showing unpredictable CPU spikes during transcript uploads.

Goals

Find the top database contributors before increasing the service tier
Keep applicant submission P95 latency under five seconds
Preserve a baseline for the scholarship-week command center
Give developers a ranked list of fixes instead of vague performance complaints

Approach using Azure SQL Query Store

Operators collected Azure SQL metrics with CLI and used Query Store to compare upload, search, and status-check windows. The worst query was not the upload path; it was a dashboard count that scanned historical applications every time staff refreshed the portal. Query Store showed high execution counts and long duration with the same plan. The team added a filtered index, cached the staff dashboard count for short intervals, and kept Query Store retention long enough for the command center to compare hourly behavior during the event.

Potential outcomes

Average CPU during the busiest hour dropped from 82 percent to 47 percent
Applicant submission P95 latency stayed below 3.8 seconds during scholarship week
The planned tier upgrade was deferred until after enrollment season
Developer backlog items were prioritized by measured query cost and execution count

What to learn

Query Store helps teams spend performance effort where the workload actually hurts, not where users happen to complain first.

Scenario 03 Factory reporting slowdown after data growth Scenario, objectives, solution, measured impact, and takeaway.

Scenario

A precision manufacturing plant added new sensor history tables to its quality reporting database, and supervisors began waiting over a minute for daily defect dashboards.

Goals

Separate query-plan regressions from normal data growth
Restore dashboard load time below ten seconds for shift supervisors
Protect reporting during a scheduled compatibility-level change
Document rollback and tuning evidence for the plant operations review

Approach using Azure SQL Query Store

The data platform engineer verified the target Azure SQL Database with CLI, captured resource metrics, and reviewed Query Store before and after the new history tables were loaded. Query Store showed two reports had increased logical reads because an index no longer matched the date and product filters used by supervisors. Instead of scaling the database, the team created a covering index, tested the change in staging, and used Query Store to verify that the new plan reduced reads without forcing a fragile plan permanently.

Potential outcomes

Dashboard load time improved from 68 seconds to 7.5 seconds
Logical reads for the largest report dropped by 91 percent
No production plan forcing remained after the index fix was validated
The compatibility-level change proceeded with Query Store comparison gates

What to learn

Query Store makes performance tuning safer because it links a visible business delay to measurable plans, reads, waits, and before-and-after results.

Azure CLI

With ten years of Azure engineering scars, I use Azure CLI around Query Store because the database name alone is never enough during an incident. CLI proves the tenant, subscription, server, database, SKU, region, elastic pool, identity, diagnostics, and recent metrics before anyone changes a plan. Query Store details still come from T-SQL and SQL tools, but CLI gives repeatable inventory and evidence that the team is looking at the right production database. It also fits pipelines and runbooks: export database properties, capture metrics, attach diagnostic settings, and compare environments without relying on screenshots from the portal. It keeps incident facts portable.

Useful for

Inventory Azure SQL databases and confirm which server, resource group, elastic pool, SKU, and region own the Query Store evidence.
Capture CPU, DTU, storage, and deadlock metrics around the same time window being reviewed in Query Store.
Export diagnostic settings and database properties before a performance tuning change or plan-forcing decision.
Compare production and staging database settings so Query Store findings are not applied to the wrong environment.

Before you run a command

Confirm tenant, subscription, resource group, logical server, database name, and whether the database is single, pooled, Hyperscale, or serverless.
Verify you have read access to Azure resources and separate database permissions for Query Store catalog views or plan forcing.
Treat plan forcing, Query Store clearing, SKU changes, and diagnostic retention changes as production-impacting actions requiring approval.
Use explicit output formats and timestamps so CLI evidence lines up with Query Store time windows and incident records.

What the output tells you

Database output confirms the exact resource ID, location, edition, service objective, elastic pool, status, and tags for the performance investigation.
Metric output shows whether CPU, data IO, log IO, DTU, or storage pressure matches the Query Store regression window.
Diagnostic-setting output shows where audit, metrics, and logs are being sent for longer incident review and governance evidence.
Server and database identifiers prevent accidental tuning against a restored copy, staging database, or similarly named tenant database.

Mapped commands

Azure SQL Query Store supporting inspection

adjacent-diagnostic

az sql db show --name <database-name> --server <server-name> --resource-group <resource-group>

az sql dbdiscoverDatabases

az sql db list-usages --name <database-name> --server <server-name> --resource-group <resource-group>

az sql dbdiscoverDatabases

az monitor metrics list --resource <database-resource-id> --metric cpu_percent,dtu_consumption_percent,deadlock

az monitor metricsdiscoverDatabases

az monitor diagnostic-settings list --resource <database-resource-id>

az monitor diagnostic-settingsdiscoverDatabases

Architecture context

In architecture reviews, I treat Query Store as a required observability dependency for serious Azure SQL workloads. It sits below the application layer but above raw engine internals, giving database owners a durable record of how the workload behaves over time. It should be enabled and sized deliberately, with retention, capture mode, cleanup policy, and storage impact aligned to incident response needs. It connects directly to release governance: before major schema, index, ORM, or compatibility changes, capture a healthy baseline, then compare the post-change window. It also supports controlled remediation, because plan forcing can stabilize a critical query while developers prepare a safer code or indexing fix.

Security: Security impact is indirect, but Query Store still needs careful handling because it can expose query text, object names, execution patterns, and sometimes literals that hint at business data. Access should be limited to database administrators, performance engineers, and trusted support roles rather than broad application teams. Operators should avoid exporting Query Store evidence into unsecured tickets or chat channels. Plan forcing and configuration changes are privileged actions because a bad forced plan can degrade production. Auditors also care about who changed capture settings, cleared Query Store, or disabled it before an incident, so database permissions and change logs matter.
Cost: Query Store affects cost mainly by improving decisions. It consumes database storage and engine overhead, so retention and capture settings should match workload size, but the larger cost story is avoiding blind scale-ups. Teams often add vCores because a few queries regressed, used bad indexes, or scanned too much data. Query Store can show whether the right fix is an index, plan correction, query rewrite, or SKU change. It also helps FinOps teams challenge expensive emergency scaling with evidence. The cost risk appears when no one manages retention, causing avoidable storage growth or keeping too much noisy query history. That discipline protects budgets.
Reliability: Query Store improves reliability by preserving the performance history needed to recover from query plan regressions quickly. When a deployment or data distribution change causes a bad plan, teams can compare the previous stable plan, force it temporarily, and restore service while they work on root cause. Reliability still depends on configuration: if Query Store is disabled, too small, read-only, or aggressively cleaned, the evidence may disappear before the incident review. It also does not replace backups, failover, or application retries. Its value is operational stability for the database workload, especially during releases, scaling changes, and statistics-sensitive application behavior. Review ownership quarterly.
Performance: Performance is the core reason to use Query Store, but it is not magic tuning by itself. It gives teams the evidence to find slow queries, plan changes, high variance, wait patterns, and regressions between time windows. The feature has some collection overhead, so capture mode and storage limits should be chosen deliberately for busy systems. Forced plans can improve response time when used carefully, but they can hurt performance if data shape changes and nobody reviews them. The best use is disciplined: baseline, compare, remediate, monitor, and remove temporary plan forcing when the real fix is deployed. Review results after every release.
Operations: Operators use Query Store during release validation, incident triage, tuning reviews, and capacity planning. They compare time windows, identify top resource-consuming queries, check forced-plan status, review wait categories, and confirm whether performance changed after a code, index, or SKU update. Azure CLI helps by inventorying databases, confirming the target server and edition, collecting metrics, and exporting diagnostic settings, while SQL tools read the Query Store details. Runbooks should define who can force plans, who can clear data, how long Query Store is retained, and how evidence is attached to a problem record without leaking sensitive query text. This avoids ad hoc hero debugging.

Common mistakes

Assuming Azure CLI directly exposes all Query Store plan details, then missing the required T-SQL or SQL tool investigation.
Forcing a plan during a spike without checking whether statistics, data distribution, or parameter patterns have changed.
Leaving forced plans in place after the real code or index fix ships, creating a future performance trap.
Sizing Query Store too small or cleaning it too aggressively, which removes the baseline needed for regression analysis.

On this page