Superviser le niveau de performance avec le Magasin des requêtes

S’applique à : SQL Server 2016 (13.x) et versions ultérieures base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics (pool SQL dédié uniquement)

La fonctionnalité Magasin des requêtes vous fournit des insights sur le choix et les performances du plan de requête pour SQL Server, Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics. Le Magasin des requêtes simplifie la résolution des problèmes de performances en vous permettant de trouver rapidement les différences de performances provoquées par des changements de plan de requête. Le magasin de requête capture automatiquement l’historique des requêtes, des plans et des statistiques d’exécution et les conserve à des fins de révision. Elle sépare les données en périodes, ce qui vous permet de voir les modèles d'utilisation de base de données et de comprendre à quel moment les changements de plan de requête ont eu lieu sur le serveur. Vous pouvez configurer le magasin de requêtes à l’aide de l’option ALTER DATABASE SET .

• Pour plus d’informations sur l’utilisation du magasin de requêtes dans Azure SQL Database, consultez Utilisation du magasin de requêtes dans Azure SQL Database.
• Pour plus d’informations sur la découverte d’informations actionnables et le réglage des performances avec le Magasin des requêtes, consultez Régler les performances avec le Magasin des requêtes.
• Pour plus d’informations sur la mise en forme des plans de requête sans changer le code de l’application, consultez Indicateurs du Magasin des requêtes.

Activer le magasin des requêtes

• Le Magasin des requêtes est activé par défaut pour les nouvelles bases de données Azure SQL Database et Azure SQL Managed Instance.
• Le Magasin des requêtes n’est pas activé par défaut pour SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Il est activé par défaut en mode READ_WRITE pour les nouvelles bases de données à partir de SQL Server 2022 (16.x). Pour permettre aux fonctionnalités de mieux suivre l’historique des performances, résoudre les problèmes liés au plan de requête et activer de nouvelles fonctionnalités dans SQL Server 2022 (16.x), nous vous recommandons d’activer le Magasin des requêtes sur toutes les bases de données.
• Le Magasin des requêtes n’est pas activé par défaut pour les nouvelles bases de données Azure Synapse Analytics.

Utiliser la page Magasin des requêtes dans SQL Server Management Studio

1. Dans l’Explorateur d’objets, faites un clic droit sur une base de données, puis sélectionnez Propriétés.

   Remarque

   Nécessite au moins la version 16 de Management Studio.

2. Dans la boîte de dialogue Propriétés de la base de données , sélectionnez la page Magasin de requêtes .

3. Dans la zone Mode d’opération (demandé) , sélectionnez Lecture Écriture.

Utilisation d’instructions Transact-SQL

Utilisez l’instruction ALTER DATABASE afin d’activer le magasin des requêtes pour une base de données déterminée. Par exemple :

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Dans Azure Synapse Analytics, activez le Magasin des requêtes sans aucune option supplémentaire. Par exemple :

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Pour obtenir d’autres options de syntaxe relatives au Magasin des requêtes, consultez Options ALTER DATABASE SET (Transact-SQL).

Informations sur le magasin de requêtes

Les plans d’exécution d’une requête spécifique dans SQL Server évoluent généralement au fil du temps pour un certain nombre de raisons, telles que les modifications des statistiques, les modifications de schémas, la création/suppression d’index, etc. Le cache de procédures (où sont stockés les plans de requête mis en cache) stocke uniquement le dernier plan d'exécution. Les plans sont également supprimés du cache du plan en raison de la sollicitation de la mémoire. Par conséquent, les régressions des performances de requête provoquées par des modifications du plan d'exécution peuvent être significatives et longues à résoudre.

Comme le magasin des requêtes conserve plusieurs plans d’exécution par requête, il peut appliquer des stratégies pour indiquer au processeur de requêtes d’utiliser un plan d’exécution spécifique pour une requête. On parle alors de forçage de plan. Le forçage de plan dans un magasin de requêtes est fourni à l'aide d'un mécanisme semblable à l’indicateur de requête USE PLAN , mais il ne nécessite pas d’apporter des modifications dans les applications utilisateur. Le forçage de plan peut résoudre une régression des performances de requête provoquée par une modification du plan dans un délai très court.

Les statistiques d’attente sont une autre source d’informations qui aide à résoudre les problèmes de performances dans. Pendant longtemps, les statistiques d’attente ont été disponibles seulement au niveau de l’instance, ce qui rendait difficile leur rétroaction sur une requête spécifique. À compter de SQL Server 2017 (14.x) et d’base de données Azure SQL, le Magasin des requêtes inclut une dimension qui suit les statistiques d’attente. L’exemple suivant permet au Magasin des requêtes de collecter les statistiques d’attente.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Voici des scénarios courants pour l'utilisation de la fonctionnalité de magasin de requêtes :

• Recherchez et corrigez rapidement une régression des performances du plan en forçant l’application du plan de requête précédent. Résolvez les requêtes qui ont récemment rencontré une régression des performances suite à la modification du plan d'exécution.
• Déterminez le nombre de fois où une requête a été exécutée dans une fenêtre de temps donnée, en aidant un administrateur de base de données à résoudre les problèmes liés aux ressources de performances.
• Identifiez les n requêtes les plus importantes (en termes de temps d’exécution, de consommation de mémoire, etc.) au cours des x dernières heures.
• Auditez l'historique des plans de requête pour une requête donnée.
• Analysez les ressources (UC, E/S et mémoire) des modèles d'utilisation pour une base de données spécifique.
• Identifiez les n premières requêtes qui attendent des ressources.
• Comprenez la nature de l’attente d’une requête ou d’un plan en particulier.

Le magasin des requêtes contient trois magasins :

• Un magasin de plans pour rendre persistantes les informations du plan d’exécution.
• Un magasin de statistiques de runtime pour rendre persistantes les informations des statistiques d’exécution.
• Un magasin de statistiques d’attente pour rendre persistantes les informations des statistiques d’attente.

Le nombre de plans uniques pouvant être stockés pour une requête dans le magasin de plans est limité par l’option de configuration max_plans_per_query . Pour améliorer les performances, les informations sont écrites dans les magasins de façon asynchrone. Pour optimiser l'espace, les statistiques d'exécution du runtime du magasin de statistiques du runtime sont agrégées sur une période fixe. Les informations contenues dans ces magasins sont visibles en interrogeant les vues de catalogue du magasin des requêtes.

La requête suivante renvoie des informations sur les requêtes, leurs plans, la compilation des statistiques de temps et d’exécution à partir du Magasin des requêtes.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Magasin des requêtes pour les réplicas secondaires

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x))

La fonctionnalité Magasin des requêtes pour les réplicas secondaires active la même fonctionnalité de Magasin des requêtes sur les charges de travail de réplica secondaire que celle disponible pour les réplicas principaux. Quand le Magasin des requêtes pour les réplicas secondaires est activé, les réplicas envoient les informations d’exécution de requête qui seraient normalement stockées dans le Magasin des requêtes vers le réplica principal. Le réplica principal conserve ensuite les données sur le disque dans son propre Magasin des requêtes. En substance, il existe un Magasin des requêtes partagé entre le réplica principal et tous les réplicas secondaires. Le Magasin des requêtes existe sur le réplica principal et stocke les données de tous les réplicas ensemble.

Pour obtenir des informations complètes sur le Magasin des requêtes pour les réplicas secondaires, consultez Magasin des requêtes pour les réplicas secondaires du groupe de disponibilité Always On.

Utiliser la fonctionnalité Requêtes régressées

Après avoir activé le magasin des requêtes, actualisez la partie de la base de données du volet de l’Explorateur d’objets pour ajouter la section Magasin des requêtes .

Sélectionnez Requêtes régressées pour ouvrir le volet Requêtes régressées dans SQL Server Management Studio. Le volet Requêtes régressées affiche les requêtes et les plans du magasin de requêtes. Utilisez les zones de liste déroulante en haut pour filtrer les requêtes en fonction de différents critères : Durée (ms) (par défaut), Temps processeur (ms), Lectures logiques (Ko), Écritures logique (Ko), Lectures physiques (Ko), Temps CLR (ms), DOP, Consommation de mémoire (Ko), Nombre de lignes, Mémoire utilisée par la journalisation (Ko), Mémoire utilisée par la base de données temporaire (Ko) et Temps d’attente (ms).

Sélectionnez un plan pour afficher le plan de requête sous forme graphique. Des boutons sont disponibles pour afficher la requête source, forcer et désactiver l’application forcée d’un plan de requête, basculer entre les formats de grille et de graphique, comparer des plans sélectionnés (si plusieurs plans sont sélectionnés) et actualiser l’affichage.

Pour forcer un plan, sélectionnez une requête et un plan, puis Forcer le plan. Vous pouvez uniquement forcer des plans qui ont été enregistrés par la fonctionnalité de plan de requête et sont toujours conservés dans le cache du plan de requête.

Rechercher les requêtes en attente

À compter de SQL Server 2017 (14.x) et base de données Azure SQL, les statistiques d’attente par requête au fil du temps sont disponibles dans le Magasin des requêtes.

Dans le Magasin des requêtes, les types d’attente sont combinés en catégories d’attente. Vous trouverez dans sys.query_store_wait_stats (Transact-SQL) une correspondance entre les catégories d’attente et les types d’attente.

Sélectionnez Statistiques d’attente des requêtes pour ouvrir le volet Statistiques d’attente des requêtes dans SQL Server Management Studio 18.0 ou des versions ultérieures. Le volet Statistiques d’attente des requêtes contient un graphique à barres qui indique les principales catégories d’attente dans le Magasin des requêtes. Utilisez la liste déroulante en haut pour sélectionner un critère d’agrégation pour le temps d’attente : moy, max, min, écart type et total (valeur par défaut).

Sélectionnez une catégorie d’attente en sélectionnant la barre. Un affichage détaillé de la catégorie d’attente sélectionnée apparaît. Ce nouveau graphique à barres contient les requêtes qui ont contribué à cette catégorie d’attente.

Utilisez la zone de liste déroulante en haut pour filtrer les requêtes en fonction de différents critères de temps d’attente pour la catégorie d’attente sélectionnée : moy, max, min, écart type et total (valeur par défaut). Sélectionnez un plan pour afficher le plan de requête sous forme graphique. Des boutons permettent d'afficher la requête source, de forcer un plan de requête et d’annuler son application forcée, ainsi que d'actualiser l'affichage.

Les catégories d’attente combinent différents types d’attente dans des compartiments similaires par nature. Différentes catégories d’attente nécessitent une analyse de suivi différente pour résoudre le problème, mais les types d’attente d’une même catégorie entraînent des expériences de résolution de problèmes très similaires à condition que la requête affectée au-dessus des attentes soit l’élément manquant de la plupart de ces expériences.

Voici quelques exemples vous permettant d’obtenir plus d’insights sur votre charge de travail avant et après l’introduction des catégories d’attente dans le Magasin des requêtes :

Options de configuration

Pour connaître les options disponibles pour configurer les paramètres du Magasin des requêtes, consultez les options ALTER DATABASE SET (Transact-SQL).

Interrogez l’affichage sys.database_query_store_options pour déterminer les options actuelles du Magasin des requêtes. Pour plus d’informations sur les valeurs, consultez sys.database_query_store_options.

Pour obtenir des exemples sur la définition des options à l’aide d’instructions Transact-SQL, consultez Gestion des options.

Vues, fonctions et procédures associées

Affichez et gérez le magasin des requêtes par le biais de Management Studio ou à l’aide des vues et procédures suivantes.

Fonctions du Magasin des requêtes

Les fonctions facilitent les opérations avec le Magasin des requêtes.

Affichages catalogue du Magasin des requêtes

Les affichages catalogue présentent des informations sur le magasin de requêtes.

Procédures stockées du Magasin des requêtes

Les procédures stockées configurent le magasin de requêtes.

¹ dans les scénarios extrêmes Magasin des requêtes pouvez entrer un état ERREUR en raison d’erreurs internes. Depuis SQL Server 2017 (14.x), le cas échéant, le Magasin des requêtes peut être récupéré via l’exécution de la procédure stockée sp_query_store_consistency_check dans la base de données affectée. Consultez sys.database_query_store_options pour obtenir plus d’informations dans la description de la colonne actual_state_desc.

Maintenance du Magasin des requêtes

Les meilleures pratiques et les recommandations relatives à la maintenance et à la gestion du Magasin des requêtes ont été développées dans cet article : Meilleures pratiques pour la gestion du Magasin des requêtes.

Audit et résolution des problèmes de performances

Pour plus d’informations sur le réglage des performances avec le Magasin des requêtes, consultez Régler les performances avec le Magasin des requêtes.

Autres rubriques sur les performances :

• Scénarios d’utilisation du magasin de requêtes

Contenu connexe

• Procédures stockées du Magasin des requêtes (Transact-SQL)
• Affichages catalogue du Magasin des requêtes (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Statistiques des requêtes dynamiques
• Moniteur d’activité
• Comment le Magasin des requêtes collecte les données
• Surveillance et réglage des performances
• Outils de surveillance et de réglage des performances
• Utilisation du Magasin des requêtes avec l’OLTP en mémoire
• Meilleures pratiques relatives au Magasin des requêtes
• Meilleures pratiques pour la gestion du Magasin des requêtes
• Régler les performances avec le Magasin des requêtes
• Indicateurs du Magasin des requêtes
• Scénarios d’utilisation du magasin de requêtes
• Ouvrir le Moniteur d’activité (SQL Server Management Studio)