Azure SQL Database Query Store: Enabling, Diagnosing, and Managing Performance Data

Problem & takeaway

\n

Your Azure SQL Database performance fluctuates without obvious cause, and you lack historical query data after restarts or plan changes. Query Store solves this by automatically persisting per-query execution metrics, letting you spot trends, detect regressions, and revert to a known‑good plan—all from T‑SQL or the Azure Portal.

\n\n

When Query Store is the right tool

\n

Use Query Store when you need a persistent, low‑overhead view of query‑level performance over time. Common scenarios include identifying suddenly expensive queries, correlating performance changes with deployment windows, and forcing a previously good execution plan after an unintended optimization.

\n\n

Enabling Query Store

\n

Query Store can be enabled at the database level with a single T‑SQL command. Run the following in sqlcmd, SSMS, or Azure Cloud Shell:

\n

ALTER DATABASE [<database_name>] SET QUERY_STORE = ON;

\n

Required permission: ALTER DATABASE on the target database. The command returns immediately, but Query Store begins capturing statistics from the next query compilation event.

\n\n

What gets captured

\n

Once enabled, Query Store automatically records per‑query statistics including execution count, average CPU time, average duration, and I/O metrics. These metrics are stored across three core dynamic management views:

\n

\n
• sys.query_store_query – contains one row per unique query text signature.
\n
• sys.query_store_plan – contains one row per execution plan associated with a query.
\n
• sys.query_store_runtime_stats – contains aggregated runtime statistics per plan, refreshed at the flush interval (default 60 seconds).
\n

\n

Dynamic management view (DMV) refers to SQL Server‑style catalog views that expose server‑state information; they are the standard mechanism for querying Query Store data.

\n\n

Querying execution data – a concrete example

\n

The following query returns the top ten queries by average duration, ordered from slowest to fastest:

\n

SELECT TOP (10) \n    q.query_name, \n    ROUND(MAX(r.avg_duration), 2) / 1000.0 AS avg_duration_ms, \n    ROUND(MAX(r.avg_cpu_time), 2) / 1000.0 AS avg_cpu_ms, \n    MAX(r.request_count) AS request_count\nFROM sys.query_store_runtime_stats AS r\nJOIN sys.query_store_query AS q ON r.query_id = q.query_id\nGROUP BY q.query_name\nORDER BY avg_duration_ms DESC;

\n

Expected result: a list of query names with their average duration (ms), average CPU time (ms), and total execution count. This helps prioritize which queries to investigate further.

\n\n

Detecting regressions and forcing a plan

\n

If a query’s average duration spikes after a code change or parameter shift, Query Store makes the regression visible. Compare avg_duration across consecutive time windows, or examine plan_id changes in sys.query_store_plan. When a regression is confirmed and a previous plan is known to be stable, you can force that plan using the built‑in stored procedure:

\n

EXEC sp_query_store_force_plan \n    @query_id = <query_id>, \n    @plan_id = <plan_id>;

\n

Replace <query_id> and <plan_id> with the identifiers returned from the DMV queries. This action immediately directs the optimizer to use the specified plan for future compilations, which is useful when an automatic plan correction would be too disruptive.

\n

Plan forcing requires ALTER ANY DATABASE QUERY STORE or appropriate database‑level permissions, and it only affects new compilations after the command runs.

\n\n

Retention, size, and overhead considerations

\n

\n
1. CPU and latency overhead: Enabling Query Store adds minimal compilation overhead per new query, but on workloads with thousands of ad‑hoc statements per second, cumulative impact can be noticeable. Monitor CPU utilization and query latency after enabling, especially during peak hours.
\n
2. Database growth: Retention beyond the default 30 days (or the tier‑specific default) causes Query Store data to accumulate, potentially increasing the overall database size. Regularly review the current_storage_size_mb column in sys.database_querystore_stats and adjust the retention policy if size becomes a concern.
\n

\n

Practical check: run SELECT current_storage_size_mb FROM sys.database_querystore_stats; to see current consumption and the configured limit.

\n\n

Mode: READ_WRITE vs READ_ONLY

\n

\n
• READ_WRITE – captures new query statistics and retains the ability to purge or disable data capture.
\n
• READ_ONLY – prevents further captures after the mode is switched; existing data remains queryable, which is helpful after you’ve finished a performance investigation and want to preserve the snapshot without additional overhead.
\n

\n

To change the mode:

\n

ALTER DATABASE [<database_name>] SET QUERY_STORE (OPERATION_MODE = READ_ONLY);

\n\n

Disabling or clearing Query Store

\n

\n
• Clear data: ALTER DATABASE [<database_name>] QUERY_STORE CLEAR; removes all captured statistics while keeping Query Store enabled.
\n
• Disable entirely: ALTER DATABASE [<database_name>] SET QUERY_STORE = OFF; stops data capture, retains existing data temporarily, and marks is_query_store_on as 0 in sys.databases.
\n

\n

After disabling, the stored DMVs become empty; re‑enabling Query Store starts a fresh capture cycle.

\n\n

Quick verification checklist

\n

\n
1. Confirm Query Store is on: SELECT is_query_store_on FROM sys.databases WHERE name = N'<database_name>'; – a value of 1 means enabled.
\n
2. Check the operation mode: SELECT query_store_mode FROM sys.database_querystore_options; – READ_WRITE or READ_ONLY.
\n
3. Review top‑cost queries using the example DMV query above.
\n
4. Measure database size impact: SELECT current_storage_size_mb FROM sys.database_querystore_stats;
\n