Query Store on Azure SQL Database: a DBA guide
Top consumers, regressed queries, plan forcing, FORCE_LAST_GOOD_PLAN automatic tuning and what to watch in elastic pools.
On Azure SQL Database, Query Store is the first tool I open when someone says "the database is slow". It is enabled by default on new databases, it survives restarts and failovers, and it answers the two questions that matter: what consumes the most resources, and what changed. This article is the routine I follow.
Top resource consumers
Start from the cost, not from the complaint. This query lists the queries with the most total CPU over the last 24 hours:
SELECT TOP (10)
q.query_id,
LEFT(qt.query_sql_text, 100) AS query_text,
SUM(rs.count_executions) AS executions,
SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time > DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY q.query_id, LEFT(qt.query_sql_text, 100)
ORDER BY total_cpu_ms DESC;
Sort by CPU, by duration, by logical reads and by execution count: a cheap query executed a million times often matters more than a slow query executed once. The portal "Query Performance Insight" and the SSMS Query Store reports show the same data graphically.
Regressed queries
A regression is a query whose recent performance is worse than its own history, usually because the optimizer chose another plan (new statistics, a parameter value that differs from the one used at compile time, a schema change). In SSMS the "Regressed Queries" report does it for you. In T-SQL, compare two periods per plan and look for queries that have more than one plan with very different averages:
SELECT p.query_id, COUNT(DISTINCT p.plan_id) AS plans,
MIN(rs.avg_duration) AS best_us, MAX(rs.avg_duration) AS worst_us
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY p.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1
AND MAX(rs.avg_duration) > 3 * MIN(rs.avg_duration)
ORDER BY worst_us DESC;
Durations are in microseconds. Open the two plans side by side before deciding anything.
Forcing a plan
When the good plan is known, force it:
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;
-- and to undo it
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 17;
Forcing is a patch, not a fix: the root cause (skewed parameter, stale statistics, missing index) stays. I note every forced plan in a ticket and review them regularly, because a forced plan can fail after a schema change (check last_force_failure_reason in sys.query_store_plan).
Automatic tuning
Automatic plan correction does this for you. Per the documentation, FORCE_LAST_GOOD_PLAN is enabled by default in the Azure defaults, while CREATE_INDEX and DROP_INDEX are disabled. It can be set at server level (recommended, databases inherit) or per database:
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
SELECT name, type, reason, state_transition_reason = JSON_VALUE(state, '$.reason')
FROM sys.dm_db_tuning_recommendations;
The engine forces the previous plan, checks that it is really better and unforces it otherwise. I leave it on, and I still read the recommendations: they are a free list of the queries whose plan flipped.
What to watch in elastic pools
In a pool, one database's query consumes resources shared with all the others, so a regression hurts the neighbours. My checklist:
- Query Store is per database: there is no pool-wide view. For a pool I look at pool metrics first (CPU, workers, log write, data IO percentage), find the busy period, then open Query Store in the databases that were active.
- Read-only Query Store (out of space or manually switched) silently stops capturing: check
sys.database_query_store_options(actual_state_desc,current_storage_size_mb). - Identical application queries in hundreds of databases have the same
query_hashbut differentquery_ids per database; aggregate on the hash when you collect centrally. - Wait categories per query are available in
sys.query_store_wait_stats, which links this article to the wait analysis of the pool.
flowchart TD
A["Pool metrics : busy period"] --> B["Query Store of the active databases"]
B --> C["Top consumers"]
B --> D["Regressed queries"]
D --> E{"Root cause fixed ?"}
E -- "no" --> F["Force plan (temporary)"]
E -- "yes" --> G["Index / stats / rewrite"]
F --> G
G --> H["Automatic tuning : verify"]
Lessons learned
- Check the Query Store state before trusting a report: a read-only store gives a confident but stale answer.
- Force a plan only with a documented reason and a review date.
- Compare periods of the same length and the same weekday; a monthly batch will always look like a regression against a quiet Sunday.
- Keep the retention reasonable: a store that fills up stops capturing at the worst moment.