LLI Wiki — My Digital Knowledge Base

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:

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

Mastodon LLIMastodon SocialFosstodon