LLI Wiki — My Digital Knowledge Base

Query Store sur Azure SQL Database : le guide du DBA

Plus gros consommateurs, requêtes régressées, forçage de plan, réglage automatique FORCE_LAST_GOOD_PLAN et points d'attention dans les pools élastiques.

Sur Azure SQL Database, Query Store est le premier outil que j'ouvre quand quelqu'un dit « la base est lente ». Il est activé par défaut sur les nouvelles bases, il survit aux redémarrages et aux failovers, et il répond aux deux questions qui comptent : qu'est-ce qui consomme le plus, et qu'est-ce qui a changé. Voici la routine que je suis.

Les plus gros consommateurs

On part du coût, pas de la plainte. Cette requête liste les requêtes qui ont consommé le plus de CPU sur les dernières 24 heures :

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;

Triez par CPU, par durée, par lectures logiques et par nombre d'exécutions : une requête peu coûteuse exécutée un million de fois pèse souvent plus qu'une requête lente exécutée une fois. « Query Performance Insight » dans le portail et les rapports Query Store de SSMS montrent les mêmes données en graphique.

Les requêtes régressées

Une régression, c'est une requête dont les performances récentes sont pires que son propre historique, en général parce que l'optimiseur a choisi un autre plan (nouvelles statistiques, valeur de paramètre différente de celle de la compilation, changement de schéma). Dans SSMS, le rapport « Regressed Queries » le fait pour vous. En T-SQL, on cherche les requêtes qui ont plusieurs plans avec des moyennes très différentes :

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;

Les durées sont en microsecondes. Ouvrez les deux plans côte à côte avant de décider quoi que ce soit.

Forcer un plan

Quand le bon plan est connu, on le force :

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;
-- et pour annuler
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 17;

Forcer est un pansement, pas un correctif : la cause (paramètre asymétrique, statistiques périmées, index manquant) reste. Je note chaque plan forcé dans un ticket et je les revois régulièrement, car un plan forcé peut échouer après un changement de schéma (voir last_force_failure_reason dans sys.query_store_plan).

Le réglage automatique

La correction automatique de plan le fait à votre place. D'après la documentation, FORCE_LAST_GOOD_PLAN est activé dans les valeurs par défaut Azure, alors que CREATE_INDEX et DROP_INDEX sont désactivés. On le configure au niveau du serveur (recommandé, les bases héritent) ou par base :

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;

Le moteur force le plan précédent, vérifie qu'il est réellement meilleur et le déforce sinon. Je le laisse activé et je lis quand même les recommandations : c'est une liste gratuite des requêtes dont le plan a basculé.

Ce qu'il faut surveiller dans un pool élastique

Dans un pool, la requête d'une base consomme des ressources partagées avec toutes les autres : une régression pénalise les voisines. Ma liste de contrôle :

flowchart TD
  A["Métriques du pool : période chargée"] --> B["Query Store des bases actives"]
  B --> C["Plus gros consommateurs"]
  B --> D["Requêtes régressées"]
  D --> E{"Cause racine corrigée ?"}
  E -- "non" --> F["Forcer le plan (temporaire)"]
  E -- "oui" --> G["Index / stats / réécriture"]
  F --> G
  G --> H["Réglage automatique : vérifier"]

Retour d'expérience

Mastodon LLIMastodon SocialFosstodon