Événement
31 mars, 23 h - 2 avr., 23 h
Le plus grand événement d’apprentissage SQL, Fabric et Power BI. 31 mars au 2 avril. Utilisez le code FABINSIDER pour économiser 400 $.
Inscrivez-vous aujourd’huiCe navigateur n’est plus pris en charge.
Effectuez une mise à niveau vers Microsoft Edge pour tirer parti des dernières fonctionnalités, des mises à jour de sécurité et du support technique.
S’applique à : SQL Server 2016 (13.x) et versions
ultérieures d’Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics (pool SQL dédié uniquement)
base de données SQL dans Microsoft Fabric
La fonctionnalité Magasin des requêtes vous fournit des informations sur le choix et les performances du plan de requête pour SQL Server, Azure SQL Database, Fabric 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 Magasin des requêtes à l’aide de l’option ALTER DATABASE SET.
Important
Si vous utilisez le Magasin des requêtes pour avoir un aperçu juste-à-temps de la charge de travail dans SQL Server 2016 (13.x), prévoyez d’installer les correctifs d’évolutivité des performances dans KB 4340759 dès que possible.
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.Dans l’Explorateur d’objets, faites un clic droit sur une base de données, puis sélectionnez Propriétés.
Note
Nécessite au moins la version 16 de Management Studio.
Dans la boîte de dialogue Propriétés de la base de données , sélectionnez la page Magasin de requêtes .
Dans la zone Mode d’opération (demandé) , sélectionnez Lecture Écriture.
Utilisez l’instruction ALTER DATABASE
pour activer la Magasin des requêtes pour une base de données donnée. Par exemple :
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
Les options de configuration des Magasin des requêtes dans la base de données SQL Fabric avec ALTER DATABASE
sont actuellement limitées.
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).
Note
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.
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.
Note
Le magasin des requêtes collecte des plans pour les instructions DML telles que SELECT
, INSERT
, UPDATE
, DELETE
, MERGE
, et BULK INSERT
.
Par conception, le Magasin des requêtes ne collecte pas de plans pour les instructions DDL telles que CREATE INDEX
, etc. Le Magasin des requêtes capture la consommation cumulative des ressources en collectant des plans pour les instructions DML sous-jacentes. Par exemple, le 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 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 :
Le magasin des requêtes contient trois magasins :
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;
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.
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 .
Note
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 dans le Magasin des 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.
À 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 :
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. |
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.
Note
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 le magasin des requêtes par le biais de Management Studio ou à l’aide des vues et procédures suivantes.
Les fonctions facilitent les opérations avec le Magasin des requêtes.
Les affichages catalogue présentent des informations sur le magasin de requêtes.
Les procédures stockées configurent le magasin de requêtes.
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
.
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.
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 :
Événement
31 mars, 23 h - 2 avr., 23 h
Le plus grand événement d’apprentissage SQL, Fabric et Power BI. 31 mars au 2 avril. Utilisez le code FABINSIDER pour économiser 400 $.
Inscrivez-vous aujourd’huiFormation
Module
Explorer l’optimisation des performances des requêtes - Training
Explorer l’optimisation des performances des requêtes
Certification
Microsoft Certified : Azure Database Administrator Associate - Certifications
Administrer une infrastructure de base de données SQL Server pour les bases de données relationnelles cloud, locales et hybrides à l’aide des offres de bases de données relationnelles Microsoft PaaS.
Documentation
Meilleures pratiques pour la gestion du Magasin des requêtes - SQL Server
Découvrez les meilleures pratiques de gestion du Magasin des requêtes SQL Server, notamment les détails spécifiques à la version, la gestion des stratégies de capture personnalisées et d’autres fonctionnalités de performances.
Découvrez les bonnes pratiques relatives à l’utilisation du Magasin des requêtes SQL Server avec votre charge de travail, telles que l’utilisation des versions les plus récentes de SQL Server Management Studio et de Query Performance Insight.
sys.database_query_store_options (Transact-SQL) - SQL Server
sys.database_query_store_options retourne les options de Magasin des requêtes pour cette base de données.