Explorer le Magasin des requêtes
Le Magasin des requêtes de SQL Server est une fonctionnalité par base de données qui capture automatiquement un historique des requêtes, des plans et des statistiques d’exécution pour simplifier la résolution des problèmes de performances et le réglage des requêtes. Il fournit également des informations sur les schémas d’utilisation de la base de données et la consommation de ressources.
Au total, le Magasin des requêtes contient trois magasins :
- Magasin du plan : utilisé pour stocker les informations sur le plan d’exécution estimé
- Magasin des statistiques du runtime : utilisé pour stocker les informations sur les statistiques d’exécution
- Magasin des statistiques d’attente : utilisé pour conserver les informations sur les statistiques d’attente
Activer le magasin des requêtes
Le Magasin des requêtes est activé par défaut dans les bases de données Azure SQL. Si vous souhaitez l’utiliser avec SQL Server et Azure Synapse Analytics, vous devez commencer par l’activer. Pour activer la fonctionnalité du Magasin des requêtes, utilisez la requête suivante valide pour votre environnement :
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
Comment le Magasin des requêtes collecte les données
Le Magasin des requêtes s’intègre avec le pipeline de traitement des requêtes à de nombreuses étapes. Dans chaque point d’intégration, les données sont collectées en mémoire et écrites sur disque de manière asynchrone pour réduire la surcharge d’E/S. Les points d’intégration sont les suivants :
Quand une requête s’exécute pour la première fois, son texte et son plan d’exécution estimé initial sont envoyés au Magasin de requêtes qui les conserve.
Le plan est mis à jour dans le Magasin des requêtes lors de la recompilation d’une requête. Si la recompilation conduit à la génération d’un nouveau plan d’exécution, celui-ci est également conservé dans le Magasin des requêtes pour enrichir les plans précédents. En outre, le Magasin des requêtes conserve une trace des statistiques d’exécution pour chaque plan de requête à des fins de comparaison.
Pendant les phases de compilation et de vérification de la recompilation, le Magasin des requêtes identifie s’il existe un plan forcé pour la requête à exécuter. La requête est recompilée si le Magasin des requêtes fournit un plan forcé différent de celui figurant dans le cache de procédure.
Quand une requête s’exécute, ses statistiques de runtime persistent dans le Magasin des requêtes. Le Magasin des requêtes agrège ces données pour garantir une représentation précise de chaque plan de requête.
Pour en savoir plus sur la façon dont le Magasin des requêtes collecte les données, consultez Comment le magasin de requêtes collecte les données.
Scénarios courants
Le Magasin des requêtes de SQL Server fournit des informations précieuses sur les performances des opérations effectuées dans une base de données. Les scénarios les plus courants sont les suivants :
Identification et correction d’une régression des performances due à la sélection d’un plan d’exécution de requête inférieur
Identification et réglage des requêtes portant sur la consommation de ressources la plus élevée
Test A/B pour évaluer les impacts des modifications de base de données et d’application
Garantie de la stabilité des performances après des mises à niveau de SQL Server
Détermination des requêtes les plus fréquemment utilisées
Auditer l’historique des plans de requête pour une requête
Identification et amélioration des charges de travail ad hoc
Comprendre les catégories d’attente prédominantes d’une base de données, ainsi que les requêtes et plans contributeurs affectant les temps d’attente
Analyser des schémas d’utilisation de la base de données au fil du temps, en ce qu’ils s’appliquent à la consommation des ressources (CPU, I/O, mémoire)
Découvrir les affichages du Magasin des requêtes
Une fois le Magasin des requêtes activé sur une base de données, son dossier est visible pour la base de données dans l’Explorateur d’objets. Pour Azure Synapse Analytics, les affichages du Magasin des requêtes apparaissent sous les Vues système. Les affichages du Magasin des requêtes fournissent des insights agrégées et rapides sur les performances de la base de données SQL Server.
Requêtes régressées
Une requête régressée est une requête qui connaît une dégradation des performances au fil du temps en raison de modifications du plan d’exécution. Les plans d’exécution estimés changent en raison de nombreux facteurs, dont des changements de schéma, de statistiques et d’index. Le premier instinct peut être d’examiner le cache de procédure, mais le problème avec celui-ci est qu’il stocke uniquement le plan d’exécution le plus récent pour une requête. Même dans ce cas, des plans sont écartés en fonction des besoins en mémoire du système. Toutefois, le Magasin des requêtes conserve plusieurs plans d’exécution stockés pour chaque requête, offrant ainsi la flexibilité nécessaire pour choisir un plan spécifique dans un concept appelé forçage de plan afin de résoudre le problème d’une régression des performances de requête due à un changement de plan.
L’affichage Requêtes régressées peut épingler des requêtes dont les métriques d’exécution régressent en raison de modifications du plan d’exécution au cours d’une période spécifiée. L’affichage Requêtes régressées permet de filtrer en fonction de la sélection d’une métrique (par exemple, durée, temps processeur, nombre de lignes, etc.) et d’une statistique (total, moyenne, min, max ou écart type). Ensuite, l’affichage répertorie les 25 premières requêtes régressées en fonction du filtre fourni. Une vue d’histogramme graphique des requêtes s’affiche par défaut, mais vous pouvez éventuellement voir les requêtes dans un format de grille.
Le volet de résumé du plan affiche les plans de requête conservés associés à la requête au fil du temps, après avoir sélectionné une requête dans le volet de requête en haut à gauche. Vous verrez un plan de requête graphique dans le volet inférieur en sélectionnant un plan de requête dans le volet Résumé du plan. En outre, des boutons de barre d’outils sont disponibles dans le volet de résumé du plan et dans le volet du plan de requête graphique afin de forcer le plan sélectionné pour la requête sélectionnée. Cette structure de volet et ce comportement sont utilisés systématiquement dans tous les affichages de requête SQL.
Vous pouvez également utiliser la procédure stockée sp_query_store_force_plan pour utiliser un forçage de plan.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Consommation globale des ressources
L’affichage Consommation globale des ressources permet d’analyser la consommation totale de ressources pour plusieurs métriques d’exécution (telles que le nombre d’exécutions, la durée, le temps d’attente, etc.) pour une plage de temps spécifiée. Les graphiques rendus sont interactifs. Lors de la sélection d’une mesure de l’un des graphiques, un affichage explorable présentant les requêtes associées à la mesure choisie apparaît dans un nouvel onglet.
L’affichage détaillé fournit les 25 principales requêtes des consommateurs de ressources qui ont contribué à la métrique sélectionnée. Cet affichage détaillé utilise l’interface cohérente qui permet d’inspecter les requêtes associées et leurs détails, d’évaluer les plans de requête estimés enregistrés, voire d’utiliser un forçage de plan pour améliorer les performances. Cet affichage est précieux quand la contention de ressources système devient problématique, par exemple, quand l’utilisation du processeur atteint la capacité.
Principales requêtes consommatrices de ressources
L’affichage Principales requêtes consommatrices de ressources est similaire à l’exploration au niveau du détail de l’affichage Consommation globale des ressources. Il permet également de sélectionner une métrique et une statistique en tant que filtre. Toutefois, les requêtes qu’il affiche sont les 25 requêtes ayant le plus d’impact en fonction du filtre et de la plage de temps choisis.
L’affichage Principales requêtes consommatrices de ressources fournit la première indication de la nature ad hoc de la charge de travail lors de l’identification et de l’amélioration de charges de travail ad hoc. Par exemple, dans l’image suivante, la métrique Nombre d’exécutions et la statistique Total sont sélectionnées pour révéler qu’environ 90 % des principales requêtes consommatrices de ressources ne sont exécutées qu’une seule fois.
Requêtes avec des plans forcés
L’affichage Requêtes avec des plans forcés donne un aperçu rapide des requêtes qui ont des plans de requête forcés. Cet affichage devient pertinent si un plan forcé ne fonctionne plus comme prévu et doit être réévalué. Cet affichage offre la possibilité d’examiner tous les plans d’exécution estimés conservés d’une requête sélectionnée, afin de déterminer facilement si un autre plan est maintenant mieux adapté aux performances. Dans ce cas, des boutons de barre d’outils sont disponibles pour annuler le forçage d’un plan si nécessaire.
Requêtes avec variation forte
Les performances de requête peuvent varier entre les exécutions. L’affichage Requêtes avec variation forte contient une analyse des requêtes qui présentent la variation ou l’écart type les plus élevés pour une métrique sélectionnée. L’interface est cohérente avec la plupart des affichages du Magasin des requêtes, permettant l’inspection détaillée des requêtes, l’évaluation du plan d’exécution, voire le forçage d’un plan spécifique. Utilisez cet affichage pour régler des requêtes imprévisibles dans un modèle de performances plus cohérent.
Statistiques d’attente des requêtes
L’affichage Statistiques d’attente des requêtes analyse les catégories d’attente les plus actives pour la base de données, et affiche un graphique. Ce graphique est interactif. La sélection d’une catégorie d’attente donne accès aux détails des requêtes qui contribuent à la statistique de temps d’attente.
L’interface d’affichage des détails est également cohérente avec la plupart des affichages du Magasin des requêtes, permettant l’inspection détaillée des requêtes, l’évaluation du plan d’exécution, voire le forçage d’un plan spécifique. Cet affichage permet d’identifier des requêtes qui affectent l’expérience utilisateur dans les applications.
Requête de suivi
L’affichage Requête de suivi permet d’analyser une requête spécifique en fonction d’une valeur d’ID de requête entrée. Une fois exécuté, l’affichage fournit l’historique d’exécution complet de la requête. Une coche sur une exécution indique qu’un plan forcé a été utilisé. Cet affichage peut fournir des informations sur des requêtes, telles que celles avec des plans forcés pour vérifier que les performances de requête restent stables.
Utilisation du Magasin des requêtes pour trouver des attentes de requête
Lorsque les performances d’un système commencent à se dégrader, il est judicieux de consulter les statistiques d’attente des requêtes pour éventuellement identifier une cause. Outre l’identification des requêtes nécessitant un réglage, cela peut également mettre en lumière des mises à niveau potentielles de l’infrastructure qui seraient bénéfiques.
Le Magasin des requêtes de SQL fournit l’affichage Statistiques d’attente des requêtes qui donne des informations sur principales catégories d’attente pour la base de données. Actuellement, il existe 23 catégories d’attente.
Un histogramme affiche les catégories d’attente les plus impactantes pour la base de données quand vous ouvrez l’affichage Statistiques d’attente des requêtes. En outre, un filtre situé dans la barre d’outils du volet des catégories d’attente permet que les statistiques d’attente soient calculées en fonction du temps d’attente total (par défaut), du temps d’attente moyen, du temps d’attente minimal, du temps d’attente maximal ou du temps d’attente écart standard.
La sélection d’une catégorie d’attente permet d’explorer les détails des requêtes qui contribuent à cette catégorie d’attente. À partir de cet affichage, vous avez la possibilité d’examiner les requêtes individuelles qui sont les plus impactantes. Vous pouvez accéder à l’écran des plans d’exécution estimés conservés dans le volet Résumé du plan en sélectionnant une requête dans le volet de requête. La sélection d’un plan de requête dans le volet Résumé du plan a pour effet d’afficher le plan de requête graphique dans le volet inférieur. À partir de cet affichage, vous avez la possibilité de forcer ou non un plan de requête pour la requête afin d’améliorer les performances.
