Explorer le Magasin des requêtes

Effectué

Le magasin de requêtes 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, ce qui simplifie la résolution des problèmes de performances et le réglage des requêtes. Il fournit également des insights sur les modèles d’utilisation de base de données et la consommation des ressources.

Le Magasin des requêtes se compose de trois magasins :

• Magasin de plans : stocke les informations estimées du plan d’exécution.
• Magasin de statistiques d’exécution : stocke les informations sur les statistiques d’exécution.
• Magasin des statistiques d’attente : conserve 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 au pipeline de traitement des requêtes à plusieurs étapes. À 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 des E/S. Les points d’intégration sont les suivants :

1. 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.

2. Le plan est mis à jour dans le Magasin des requêtes lorsqu’une requête est recompilé. 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 effectue le suivi des statistiques d’exécution pour chaque plan de requête à des fins de comparaison.

3. 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.

4. 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 de requêtes SQL Server fournit des insights précieux sur les performances des opérations de base de données. Scénarios courants :

• Identification et correction des régressions de performances en raison d’une sélection de plan d’exécution de requête inférieure.
• Identification et réglage des requêtes de consommation de ressources les plus élevées.
• Test A/B pour évaluer les impacts des modifications de base de données et d’application.
• Garantir la stabilité des performances après les mises à niveau de SQL Server.
• Détermination des requêtes les plus fréquemment utilisées.
• Audit de l’historique des plans de requête pour une requête.
• Identification et amélioration des charges de travail non planifiées.
• Comprendre les catégories d’attente courantes d’une base de données et les requêtes et plans contributeurs affectant les temps d’attente.
• Analyse des modèles d’utilisation de base de données au fil du temps en termes de consommation de ressources (PROCESSEUR, E/S, 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 subit une dégradation des performances au fil du temps en raison des modifications apportées au plan d’exécution. Les plans d’exécution estimés peuvent changer en raison de différents facteurs, notamment les modifications de schéma, les modifications de statistiques et les modifications d’index. L’examen du cache de procédure peut être le premier instinct, mais il stocke uniquement le dernier plan d’exécution pour une requête, et les plans peuvent être supprimés en fonction des demandes de mémoire du système. Toutefois, le Magasin des requêtes conserve plusieurs plans d’exécution pour chaque requête, ce qui permet de choisir un plan spécifique par le biais du forçage de plan pour traiter la régression des performances des requêtes causée par les modifications 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. Cette vue permet de filtrer en fonction d’une métrique sélectionnée (par exemple, durée, temps processeur, nombre de lignes, etc.) et d’une statistique (total, moyenne, min, max ou écart type). Il répertorie ensuite les 25 premières requêtes régressées en fonction du filtre fourni. Par défaut, une vue graphique de graphique graphique des requêtes s’affiche, mais vous pouvez éventuellement afficher les requêtes dans un format de grille.

Après avoir sélectionné une requête dans le volet de requête en haut à gauche, le volet résumé du plan affiche les plans de requête persistants associés à la requête au fil du temps. La sélection d’un plan de requête dans le volet Résumé du plan affiche un plan de requête graphique dans le volet inférieur. Les boutons de barre d’outils du volet Résumé du plan et du volet plan de requête graphique vous permettent de forcer le plan sélectionné pour la requête sélectionnée. Cette structure de volet et ce comportement sont utilisés de manière cohérente dans toutes les vues 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.

La vue Principales requêtes consommatrices de ressources fournit la première indication de la nature non planifiée de la charge de travail lors de l’identification et de l’amélioration des charges de travail non planifiées. 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 qui doivent être paramétrables, elle peut également éclairer les mises à niveau potentielles de l’infrastructure qui seraient utiles.

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 explore 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 affiche 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.

Correction de plan automatique

Après analyse des données dans le Magasin des requêtes, SQL Server 2017 et Azure SQL Database ont introduit le concept de correction de plan automatique. Quand vous activez le Magasin des requêtes avec une base de données dans SQL Server 2017 (ou ultérieur) et dans Azure SQL Database, le moteur de SQL Server recherche les régressions de plan de requête et fournit des recommandations. Vous pouvez voir ces recommandations dans la vue de gestion dynamique (DMV) sys.dm_db_tuning_recommendations. Il s’agit notamment d’instructions T-SQL pour forcer manuellement un plan de requête quand les performances sont dans un état correct.

Si vous êtes en confiance dans ces suggestions, vous pouvez autoriser SQL Server à forcer les plans automatiquement lorsque des régressions sont rencontrées. Activez la correction automatique de plan en utilisant ALTER DATABASE et l’argument AUTOMATIC_TUNING.

Pour Azure SQL Database, vous pouvez également activer la correction de plan automatique par le biais des options de réglage automatique dans le portail Azure ou les API REST. Les recommandations en matière de correction de plan automatique sont toujours activées pour toute base de données où le Magasin des requêtes est activé (valeur par défaut pour Azure SQL Database et Azure SQL Managed Instance). Pour les nouvelles bases de données, la correction automatique de plan (FORCE_PLAN) est activée par défaut pour Azure SQL Database.