SQL Server

Trouver les régressions de performance SQL Server avec Query Store

Diagnostiquer une régression Query Store dans SQL Server : comparer deux périodes, trouver les requêtes ralenties, analyser les changements de plan, forcer un plan sans risque et l’annuler.

Par JaviPublié 13 min de lectureIntermédiaire

« C’était rapide la semaine dernière » est l’une des plaintes les plus fréquentes sur SQL Server, et l’une des plus difficiles à traiter avec les seules DMV en temps réel. Au moment où quelqu’un regarde, la requête lente est peut-être terminée, le plan a peut-être quitté le cache et le serveur a peut-être redémarré. Ce qu’il vous faut, c’est un historique : quelles requêtes se sont exécutées, avec quels plans, et quelles étaient leurs performances avant et après le changement.

Query Store conserve cet historique dans la base de données. Cet article propose une méthode pratique pour l’utiliser afin d’analyser une régression de performance, du premier symptôme jusqu’à un correctif validé :

symptôme → éléments de preuve → régression → plan candidat → validation → action

Les exemples utilisent les vues de catalogue et les procédures stockées documentées de Query Store et fonctionnent sur SQL Server 2017 et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance, sauf mention contraire.

Ce qu’est une régression de performance

Une régression est une requête, ou une charge de travail, qui s’exécute moins bien qu’auparavant pour un travail comparable. Cette dernière précision compte. Un rapport qui prend plus de temps parce qu’il lit désormais dix fois plus de données n’a pas régressé : c’est sa charge de travail qui a changé. Une régression est un changement d’efficacité, pas seulement de durée.

Causes fréquentes :

  • Changements de plan. L’optimiseur choisit un autre plan après une mise à jour des statistiques, une recompilation, une modification d’index, une compilation sensible aux paramètres, un changement de niveau de compatibilité ou une mise à niveau.
  • Croissance des données ou évolution de leur distribution. Le même plan est désormais inadapté aux données qu’il rencontre.
  • Modifications du code. Un déploiement a modifié le texte de la requête, que Query Store traite alors comme une nouvelle requête.
  • Contention de ressources. Les blocages, la pression mémoire ou la latence d’E/S rendent plus lent un plan inchangé.

Query Store excelle sur les deux premières causes, et il aide à les distinguer des deux dernières.

Pourquoi Query Store est le bon outil

Query Store capture, pour chaque base de données :

  • Les requêtes et leur texte (sys.query_store_query, sys.query_store_query_text).
  • Chaque plan utilisé par chaque requête (sys.query_store_plan), y compris le showplan XML, les heures de compilation, ainsi que la version du moteur et le niveau de compatibilité qui l’ont produit.
  • Les statistiques d’exécution par plan et par intervalle de temps (sys.query_store_runtime_stats avec sys.query_store_runtime_stats_interval) : exécutions, durée, CPU, lectures logiques, écritures, etc.
  • Les statistiques d’attente par plan et par intervalle (sys.query_store_wait_stats), lorsque la capture des attentes est activée.

Les durées et les temps CPU sont stockés en microsecondes, et les lectures logiques en pages de 8 Ko. Les statistiques sont agrégées par intervalle (60 minutes par défaut) : vous comparez donc des intervalles, pas des exécutions individuelles.

La vue de l’état actuel des requêtes et sessions actives vous indique ce qui se passe maintenant. Query Store vous indique ce qui a changé.

Étape 0 : vérifier que Query Store collecte bien les données

Query Store est activé par défaut pour les nouvelles bases Azure SQL et, depuis SQL Server 2022, pour les nouvelles bases SQL Server. Les bases plus anciennes, et celles mises à niveau depuis des versions antérieures, peuvent ne pas l’avoir activé. Vérifiez avant de vous y fier :

Requête SQL: Étape 0 : vérifier que Query Store collecte bien les donnéesT-SQL
SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    interval_length_minutes,
    stale_query_threshold_days,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
T-SQL11 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Le résultat attendu est READ_WRITE dans les deux colonnes d’état. Si actual_state_desc vaut READ_ONLY alors que desired_state_desc vaut READ_WRITE, Query Store a cessé de collecter, le plus souvent parce qu’il a atteint sa taille maximale. Vous disposez alors de l’historique jusqu’à ce moment-là et de rien ensuite, ce qui peut rendre une régression invisible.

Une configuration raisonnable pour la production :

Requête SQL: Étape 0 : vérifier que Query Store collecte bien les données (2)T-SQL
ALTER DATABASE CURRENT
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 2048,
    SIZE_BASED_CLEANUP_MODE = AUTO
);
T-SQL8 lignesAffichage en lecture seule. Aucune connexion à une base de données.

QUERY_CAPTURE_MODE = AUTO ignore les requêtes ad hoc insignifiantes, ce qui garde le magasin centré sur l’essentiel. Dimensionnez le stockage en fonction de l’historique que vous souhaitez conserver, et laissez SIZE_BASED_CLEANUP_MODE = AUTO pour que le nettoyage s’exécute avant que le magasin soit plein. La rétention est contrôlée par le seuil des requêtes obsolètes (stale query threshold) ; vérifiez qu’il couvre les périodes de comparaison dont vous avez besoin, par exemple « le même jour la semaine dernière ».

Étape 1 : définir le symptôme et les périodes

Avant d’interroger quoi que ce soit, notez :

  • Ce qui est lent : un rapport, un point de terminaison d’API, un traitement par lots, ou « tout ».
  • Quand cela a commencé : un déploiement, une reconstruction d’index, un travail de mise à jour des statistiques, une mise à niveau, un chargement de données.
  • Une période de référence : une période de charge comparable pendant laquelle tout était normal, idéalement le même jour de la semaine et aux mêmes heures.
  • Une période d’incident : de même durée, après le début du problème.

Comparer un dimanche calme avec un lundi chargé met en évidence des variations de charge, pas des régressions. Ce sont des périodes de même durée, avec une activité métier similaire, qui rendent la comparaison pertinente. Si le problème se produit en ce moment, commencez par la méthode SQL Server est lent : une méthode pratique de diagnostic et par la vérification des requêtes actives pour écarter d’abord les blocages et la saturation.

Étape 2 : trouver les requêtes qui ont ralenti

Cette requête compare deux périodes pour chaque requête. Elle pondère les moyennes de chaque intervalle par son nombre d’exécutions, afin qu’un intervalle chargé compte davantage qu’un intervalle calme, et elle présente séparément les exécutions abandonnées et en échec. Les délais d’expiration côté client sont enregistrés comme des exécutions abandonnées : une régression qui se termine en timeouts peut donc ressembler à une baisse du nombre d’exécutions lentes plutôt qu’à une hausse.

Requête SQL: Requêtes devenues plus lentesT-SQL
DECLARE @baseline_start datetimeoffset = '2026-09-28T08:00:00+00:00';
DECLARE @baseline_end   datetimeoffset = '2026-09-28T12:00:00+00:00';
DECLARE @incident_start datetimeoffset = '2026-10-05T08:00:00+00:00';
DECLARE @incident_end   datetimeoffset = '2026-10-05T12:00:00+00:00';

WITH runtime AS (
    SELECT
        p.query_id,
        CASE
            WHEN rsi.start_time >= @incident_start AND rsi.start_time < @incident_end
                THEN 'incident'
            ELSE 'baseline'
        END AS period,
        rs.execution_type,
        rs.count_executions,
        rs.avg_duration,
        rs.avg_cpu_time,
        rs.avg_logical_io_reads
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (rsi.start_time >= @baseline_start AND rsi.start_time < @baseline_end)
       OR (rsi.start_time >= @incident_start AND rsi.start_time < @incident_end)
),
per_period AS (
    SELECT
        query_id,
        period,
        SUM(CASE WHEN execution_type = 0 THEN count_executions ELSE 0 END) AS regular_executions,
        SUM(CASE WHEN execution_type <> 0 THEN count_executions ELSE 0 END) AS aborted_or_failed,
        SUM(CASE WHEN execution_type = 0 THEN avg_duration * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_duration_ms,
        SUM(CASE WHEN execution_type = 0 THEN avg_cpu_time * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_cpu_ms,
        SUM(CASE WHEN execution_type = 0 THEN avg_logical_io_reads * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) AS avg_logical_reads
    FROM runtime
    GROUP BY query_id, period
)
SELECT TOP (25)
    i.query_id,
    b.regular_executions AS baseline_executions,
    i.regular_executions AS incident_executions,
    i.aborted_or_failed AS incident_aborted_or_failed,
    CAST(b.avg_duration_ms AS decimal(18, 2)) AS baseline_avg_ms,
    CAST(i.avg_duration_ms AS decimal(18, 2)) AS incident_avg_ms,
    CAST(b.avg_cpu_ms AS decimal(18, 2)) AS baseline_cpu_ms,
    CAST(i.avg_cpu_ms AS decimal(18, 2)) AS incident_cpu_ms,
    CAST(b.avg_logical_reads AS decimal(18, 0)) AS baseline_reads,
    CAST(i.avg_logical_reads AS decimal(18, 0)) AS incident_reads,
    CAST((i.avg_duration_ms - b.avg_duration_ms) * i.regular_executions / 1000.0 AS decimal(18, 1))
        AS additional_seconds,
    LEFT(qt.query_sql_text, 200) AS query_text_start
FROM per_period AS i
INNER JOIN per_period AS b
    ON b.query_id = i.query_id
   AND b.period = 'baseline'
INNER JOIN sys.query_store_query AS q
    ON q.query_id = i.query_id
INNER JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
WHERE i.period = 'incident'
  AND i.avg_duration_ms > 1.5 * b.avg_duration_ms
ORDER BY additional_seconds DESC;
T-SQL66 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • lignes 1–4Les deux fenêtres à comparer. Donnez-leur la même durée et la même heure de la journée.
  • lignes 33–34Query Store stocke les durées en microsecondes : diviser par 1000.0 donne des millisecondes.
  • lignes 53–54Le temps supplémentaire coûté par la dégradation pendant la fenêtre de l’incident : la liste est triée selon cette valeur.
  • ligne 65Conserve les requêtes au moins 50 % plus lentes en moyenne. Modifiez 1.5 pour élargir ou resserrer la liste.

Lisez le résultat dans cet ordre :

  • additional_seconds classe les requêtes selon le temps supplémentaire qu’elles ont ajouté à la période d’incident. Une requête devenue 3× plus lente mais exécutée deux fois par jour compte moins qu’une requête devenue 30 % plus lente et exécutée dix mille fois par heure.
  • Durée, CPU et lectures. Si le CPU et les lectures ont augmenté avec la durée, la requête effectue plus de travail, ce qui suggère un changement de plan ou de données. Si la durée a augmenté mais pas le CPU ni les lectures, la requête attend, ce qui oriente vers les blocages, les E/S ou la mémoire plutôt que vers le plan.
  • Exécutions. Une forte variation du nombre d’exécutions est un changement de charge de travail. Traitez-la séparément des régressions par exécution.
  • Exécutions abandonnées ou en échec. Une hausse à ce niveau peut signaler des timeouts. Vérifiez si les exécutions lentes ont été interrompues par le client.

Les requêtes qui n’apparaissent que dans la période d’incident ne figurent pas dans ce résultat, car elles n’ont pas de référence. Il s’agit souvent de nouveau code ou d’un nouveau texte de requête après un déploiement ; listez-les séparément si le déploiement est suspect.

Étape 3 : vérifier si le plan a changé

Pour chaque requête candidate, examinez ses plans et leurs performances dans le temps :

Requête SQL: Plans d’une requête dans le tempsT-SQL
DECLARE @query_id bigint = 4711;

SELECT
    p.plan_id,
    p.is_forced_plan,
    p.compatibility_level,
    p.engine_version,
    p.last_compile_start_time,
    rsi.start_time AS interval_start,
    rs.execution_type_desc,
    rs.count_executions,
    CAST(rs.avg_duration / 1000.0 AS decimal(18, 2)) AS avg_duration_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(18, 2)) AS avg_cpu_ms,
    CAST(rs.avg_logical_io_reads AS decimal(18, 0)) AS avg_logical_reads,
    CAST(rs.max_duration / 1000.0 AS decimal(18, 2)) AS max_duration_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE p.query_id = @query_id
  AND rsi.start_time >= DATEADD(day, -14, SYSUTCDATETIME())
ORDER BY rsi.start_time, p.plan_id;
T-SQL23 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 1Remplacez 4711 par un query_id issu du résultat précédent.

Trois profils sont fréquents :

  1. Un plan avant, un autre plan après, et le nouveau plan est systématiquement moins bon. C’est une régression de plan classique, et le meilleur candidat au forçage de plan.
  2. Le même plan avant et après, mais il est devenu plus lent. Le plan n’a pas changé ; ce sont les données ou l’environnement qui ont changé. Examinez la croissance des tables, les statistiques, les blocages et la pression sur les ressources. La taille des tables et SQL Server Performance Tuning : une checklist pratique sont les étapes suivantes.
  3. Plusieurs plans en alternance, chacun bon pour certaines exécutions et mauvais pour d’autres. Cela signale généralement une sensibilité aux paramètres : un plan compilé pour une valeur de paramètre est réutilisé pour des valeurs très différentes. Forcer un seul plan peut aider certains appelants et en pénaliser d’autres.

Sur SQL Server 2022 et versions ultérieures, l’optimisation des plans sensibles aux paramètres (parameter sensitive plan optimization) peut stocker plusieurs plans variants pour une même requête paramétrée. sys.query_store_plan.plan_type_desc distingue les plans répartiteurs (dispatcher) et les plans variants, et sys.query_store_query_variant les relie. Tenez-en compte avant de conclure qu’une requête a « trop de plans ».

Étape 4 : comparer les plans candidats

Récupérez les plans et comparez-les dans SQL Server Management Studio ou dans un autre visualiseur de plans :

Requête SQL: Étape 4 : comparer les plans candidatsT-SQL
SELECT
    plan_id,
    query_id,
    last_compile_start_time,
    TRY_CONVERT(xml, query_plan) AS query_plan_xml
FROM sys.query_store_plan
WHERE plan_id IN (82, 97);
T-SQL7 lignesAffichage en lecture seule. Aucune connexion à une base de données.

TRY_CONVERT renvoie NULL pour les rares plans trop profondément imbriqués pour être convertis en xml ; dans ce cas, enregistrez le texte de query_plan avec l’extension .sqlplan et ouvrez-le directement.

Lors de la comparaison, cherchez l’opérateur qui a modifié le profil de coût : un scan là où il y avait un seek, une boucle imbriquée sur une entrée estimée volumineuse, un ordre de jointure différent, un parallélisme absent, ou un écart important entre lignes estimées et lignes réelles si vous disposez aussi d’un plan réel. Notez l’heure de compilation et le niveau de compatibilité de chaque plan ; un plan compilé juste après une mise à niveau ou un changement de niveau de compatibilité est un indice fort.

Les rapports intégrés Regressed Queries et Queries With Forced Plans de Management Studio présentent les mêmes données sous forme visuelle. Ils permettent de confirmer ce que le T-SQL vous a appris.

Étape 5 : décider s’il faut forcer un plan

Si un plan précédent était systématiquement meilleur pour la même charge de travail, le forcer est une mesure d’atténuation rapide et réversible :

Requête SQL: Étape 5 : décider s’il faut forcer un planT-SQL
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 82;
T-SQL1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

Le forçage indique à l’optimiseur d’utiliser ce plan pour la requête. Il ne modifie pas le texte de la requête et prend effet à la compilation suivante.

Ne forcez pas de plan lorsque :

  • Le plan n’a pas changé. Forcer le même plan ne résout rien ; la cause est ailleurs.
  • L’ancien plan n’était meilleur que pour certaines valeurs de paramètres. Vous échangez un groupe d’appelants lents contre un autre.
  • L’ancien plan dépend d’un index ou d’un objet qui a changé depuis. Le forçage peut échouer, et l’optimiseur revient alors à une compilation normale.
  • Vous n’avez pas comparé des charges de travail équivalentes. Un plan qui semblait meilleur un jour calme ne l’est pas forcément sous charge.
  • Le vrai correctif est peu coûteux et disponible, par exemple corriger un problème de statistiques ou ajouter un index manquant évident. Le forçage ne doit pas masquer une cause que vous pouvez éliminer.

Le forçage est une mesure d’atténuation. Consignez pourquoi vous avez forcé le plan, qui l’a fait et quand il sera réévalué.

Étape 6 : valider le résultat

Après le forçage, attendez que la requête soit recompilée et s’exécute sous une charge représentative, puis répétez la comparaison de l’étape 2 avec une nouvelle période « après ». Vous devez constater une baisse de la durée, du CPU et des lectures par exécution, avec un nombre d’exécutions comparable et sans hausse des exécutions abandonnées.

Vérifiez aussi que le forçage réussit réellement. L’utilisation d’un plan forcé n’est pas garantie ; si l’optimiseur ne peut pas le produire (par exemple parce qu’un index qu’il utilise a été supprimé), le forçage échoue et la requête est compilée normalement :

Requête SQL: Étape 6 : valider le résultatT-SQL
SELECT
    p.plan_id,
    p.query_id,
    q.object_id AS containing_object_id,
    p.plan_forcing_type_desc,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;
T-SQL11 lignesAffichage en lecture seule. Aucune connexion à une base de données.

force_failure_count n’augmente que lorsque la requête est recompilée, pas à chaque exécution. Consultez cette liste régulièrement, et pas seulement après un incident. Renommer la base de données, supprimer un index utilisé par le plan ou modifier le schéma d’objets référencés peut casser le forçage sans aucun avertissement.

Étape 7 : annuler ou remplacer le plan forcé

Annulez le forçage d’un plan lorsqu’il n’aide plus, lorsqu’un correctif durable est en place, ou lorsque la validation montre qu’il a aggravé la situation :

Requête SQL: Étape 7 : annuler ou remplacer le plan forcéT-SQL
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 82;
T-SQL1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

Le forçage de plan est une solution d’attente. Le correctif durable est généralement l’un des suivants : une meilleure maintenance des statistiques, un index qui prend en charge le prédicat, une réécriture de la requête, la séparation de profils de charge réellement différents, ou un indicateur ciblé. Sur SQL Server 2022 et versions ultérieures et dans Azure SQL, les indicateurs Query Store (Query Store hints : sys.sp_query_store_set_hints et sys.sp_query_store_clear_hints) permettent d’appliquer un indicateur de requête sans modifier le code applicatif, ce qui constitue souvent un meilleur moyen de contrôle à long terme que le forçage d’un plan entier.

Correction automatique des plans

SQL Server 2017 et versions ultérieures peuvent effectuer automatiquement la partie de cette méthode qui concerne les régressions de plan. Avec le réglage automatique (automatic tuning) activé, le moteur détecte les régressions liées au choix du plan, force le dernier plan connu comme bon, vérifie qu’il améliore la situation et revient en arrière si ce n’est pas le cas :

Requête SQL: Correction automatique des plansT-SQL
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
T-SQL2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Même sans l’activer, vous pouvez lire les recommandations qu’il formulerait :

Requête SQL: Correction automatique des plans (2)T-SQL
SELECT
    name,
    reason,
    score,
    JSON_VALUE(state, '$.currentValue') AS current_state,
    JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;
T-SQL7 lignesAffichage en lecture seule. Aucune connexion à une base de données.

sys.dm_db_tuning_recommendations n’est pas conservée après un redémarrage, et la correction automatique ne traite que les régressions liées au choix du plan. Elle ne corrige ni la croissance des données, ni les blocages, ni la pression sur les ressources. Considérez-la comme un premier intervenant utile, et conservez la méthode manuelle pour tout le reste.

Points d’attention sur le stockage et la capture

  • Dimensionnez-le pour l’historique dont vous avez besoin. Un magasin qui se remplit passe en lecture seule et cesse d’enregistrer précisément au moment où vous en avez besoin.
  • Mode de capture. AUTO est le choix pratique par défaut. ALL capture toutes les requêtes et peut faire gonfler le magasin avec des charges ad hoc ; CUSTOM permet de définir des seuils.
  • Les charges non paramétrées créent une nouvelle requête pour chaque valeur littérale, ce qui remplit le magasin et complique la comparaison. Paramétrez au niveau de l’application, ou envisagez le paramétrage forcé lorsque c’est approprié.
  • La durée des intervalles arbitre entre niveau de détail et espace. Des intervalles plus courts aident à situer précisément le moment d’un changement ; 60 minutes est une valeur par défaut raisonnable pour la plupart des systèmes.
  • Accès en lecture. Le texte des requêtes peut contenir des valeurs sensibles. Contrôlez qui peut lire les vues Query Store en production.

Erreurs fréquentes

  • Comparer des périodes de charge différente, puis qualifier de régression un changement de charge de travail.
  • Utiliser des moyennes non pondérées entre intervalles, si bien qu’un intervalle calme compte autant qu’un intervalle chargé.
  • Forcer un plan sans vérifier que le plan a changé.
  • Forcer un plan pour une requête sensible aux paramètres et reporter le problème sur d’autres appelants.
  • Ne jamais revérifier les plans forcés, si bien que les échecs passent inaperçus pendant des mois.
  • Ignorer les exécutions abandonnées, ce qui masque les timeouts.
  • Laisser Query Store passer en lecture seule et analyser une période qu’il n’a jamais enregistrée.

Recommandation

Faites de Query Store un élément des opérations courantes, pas un outil d’urgence : gardez-le en READ_WRITE, dimensionnez-le pour au moins quelques semaines d’historique, et sachez comparer deux périodes avant d’en avoir besoin.

Lorsqu’une régression apparaît, suivez les éléments de preuve : confirmez que les périodes sont comparables, classez les requêtes selon le temps supplémentaire ajouté, vérifiez si le plan a changé, comparez les plans candidats, validez le résultat et planifiez le correctif durable. Revenez à la démarche d’optimisation pour l’ordre complet, utilisez la supervision en temps réel pour l’activité actuelle et gardez la fiche Query Store à portée de main.

Étiquettes

  • SQL Server
  • Performance
  • Dépannage
  • Query Store