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

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure 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 .

Important

Si vous utilisez Magasin des requêtes pour les insights de charge de travail juste-à-temps dans SQL Server 2016 (13.x), envisagez d’installer les correctifs d’extensibilité des performances dans Ko 4340759 dès que possible.

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.
  • 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 READ_WRITE mode 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, de résoudre les problèmes liés au plan de requête et d’activer de nouvelles fonctionnalités dans SQL Server 2022 (16.x), nous vous recommandons d’activer 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).

Remarque

Vous ne pouvez pas activer le Magasin des requêtes pour les bases de données master outempdb.

Important

Pour plus d’informations sur l’activation du Magasin des requêtes et la manière de le garder ajusté à votre charge de travail, reportez-vous à Bonnes pratiques concernant le magasin de requêtes.

Informations sur le magasin de requêtes

Les plans d’exécution de toute requête spécifique dans SQL Server évoluent généralement au fil du temps en raison de plusieurs raisons différentes telles que les modifications de statistiques, les modifications de schéma, la création/suppression d’index, etc. Le cache de procédures (où les plans de requête mis en cache sont stockés) stocke uniquement le plan d’exécution le plus récent. 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.

Remarque

Le magasin des requêtes collecte des plans pour les instructions DML telles que SELECT, INSERT, UPDATE, DELETE, MERGE et BULK INSERT.

Par conception, Magasin des requêtes ne collecte pas de plans pour les instructions DDL telles que CREATE INDEX, etc. Magasin des requêtes capture la consommation cumulative des ressources en collectant des plans pour les instructions DML sous-jacentes. Par exemple, Magasin des requêtes peut afficher les instructions SELECT et INSERT exécutées en interne pour remplir un nouvel index.

Par défaut, le magasin des requêtes ne collecte pas de données pour les procédures stockées compilées en mode natif. Utilisez sys. sp_xtp_control_query_exec_stats pour activer la collecte des données pour les procédures stockées compilées en mode natif.

Les statistiques d’attente sont une autre source d’informations qui permet de résoudre les problèmes de performances dans le Moteur de base de données. 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’Azure SQL Database, 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 retourne des informations sur les requêtes et plans du magasin des requêtes.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_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 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 .

Screenshot of the Query Store reporting tree in SSMS Object Explorer.

Remarque

Pour Azure Synapse Analytics, les vues du Magasin des requêtes sont disponibles sous Vues système dans la partie base de données du volet Explorateur d’objets.

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 logiques (Ko), Lectures physiques (Ko), Temps CLR (ms), DOP, Consommation de mémoire (Ko), Nombre de lignes, Mémoire journal utilisée (Ko), Mémoire de base de données temporaire utilisée (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.

Screenshot of the SQL Server Regressed Queries report in SSMS Object Explorer.

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 Azure SQL Database, les statistiques d’attente par requête au fil du temps sont disponibles dans 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 de requête pour ouvrir le volet Statistiques d’attente de requête dans SQL Server Management Studio v18 ou version ultérieure. 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 : avg, max, min, std dev et total (valeur par défaut).

Screenshot of the SQL Server Query Wait Statistics report in SSMS Object Explorer.

Sélectionnez une catégorie d’attente en sélectionnant dans la barre et une vue détaillée sur la catégorie d’attente sélectionnée s’affiche. Ce nouveau graphique à barres contient les requêtes qui ont contribué à cette catégorie d’attente.

Screenshot of the SQL Server Query Wait Statistics detail view in SSMS Object Explorer.

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 : avg, max, min, std dev 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 :

Expérience précédente Nouvelle expérience Action
Attentes élevées de RESOURCE_SEMAPHORE par base de données Attentes élevées de mémoire dans le Magasin des requêtes pour des requêtes spécifiques Recherchez les principales requêtes consommatrices de mémoire dans le Magasin des requêtes. Ces requêtes retardent probablement davantage la progression des requêtes affectées. Utilisez l’indicateur de requête MAX_GRANT_PERCENT pour ces requêtes ou pour les requêtes concernées.
Attentes élevées de LCK_M_X par base de données Attentes élevées de verrouillage dans le Magasin des requêtes pour des requêtes spécifiques Vérifiez les textes de requêtes pour les requêtes affectées et identifiez les entités cibles. Recherchez dans le Magasin des requêtes d’autres requêtes modifiant la même entité, qui sont fréquemment exécutées et/ou ont une durée importante. Après avoir identifié ces requêtes, envisagez de changer la logique d’application pour améliorer l’accès concurrentiel, ou utilisez un niveau d’isolation moins restrictif.
Attentes élevées de PAGEIOLATCH_SH par base de données Attentes élevées d’E/S de mémoire tampon dans le Magasin des requêtes pour des requêtes spécifiques Recherchez les requêtes comportant un grand nombre de lectures physiques dans le Magasin des requêtes. Si elles correspondent aux requêtes avec des attentes élevées d’E/S, introduisez un index sur l’entité sous-jacente pour faire des recherches au lieu d’analyses et ainsi réduire la surcharge d’E/S des requêtes.
Attentes élevées de SOS_SCHEDULER_YIELD par base de données Attentes élevées du processeur dans le Magasin des requêtes pour des requêtes spécifiques Recherchez les requêtes les plus consommatrices de processeur dans le Magasin des requêtes. Parmi elles, identifiez celles pour lesquelles la tendance de processeur élevé correspond aux attentes élevées de processeur pour les requêtes concernées. Concentrez-vous sur l’optimisation de ces requêtes : il peut y avoir une régression de plan ou peut-être un index manquant.

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.

Remarque

Pour Azure Synapse Analytics, le Magasin des requêtes peut être activé comme sur les autres plateformes, mais les options de configuration supplémentaires ne sont pas prises en charge.

Affichez et gérez Magasin des requêtes via 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.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Affichages catalogue du Magasin des requêtes

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

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

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

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

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

1 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 de Magasin des requêtes

Les meilleures pratiques et recommandations en matière de maintenance et de gestion des Magasin des requêtes ont été développées dans cet article : meilleures pratiques pour la gestion des 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 :

Voir aussi

Étapes suivantes