Analyser et résoudre les problèmes de performances

Effectué

La supervision et le dépannage sont des éléments clés permettant de fournir des performances cohérentes. Azure SQL propose les mêmes outils et fonctionnalités que SQL Server pour superviser et résoudre les problèmes de performances, ainsi que des fonctionnalités supplémentaires. Parmi ces fonctionnalités, citons les vues de gestion dynamique (DMV), les événements étendus et Azure Monitor. Il est également important d’apprendre à utiliser ces outils et fonctionnalités dans divers scénarios de performances pour Azure SQL. Ces scénarios comprennent l’utilisation intensive du processeur ou l’attente d’une ressource.

Outils et fonctionnalités pour surveiller les performances

Azure SQL propose des fonctionnalités de supervision et de dépannage dans l’écosystème Azure ainsi que des outils familiers fournis avec SQL Server. Les sections suivantes décrivent brièvement ces outils et fonctionnalités.

Azure Monitor

Azure Monitor fait partie de l’écosystème Azure et Azure SQL est intégré pour prendre en charge les métriques, alertes et journaux Azure. Vous pouvez visualiser les données Azure Monitor dans le Portail Azure et les applications peuvent accéder à ces données via Azure Event Hubs ou des API. Tout comme l’Analyseur de performances Windows, Azure Monitor vous permet d’accéder aux mesures d’utilisation des ressources pour Azure SQL sans utiliser les outils de SQL Server.

Vues de gestion dynamique (DMV)

Azure SQL offre quasiment la même infrastructure de vue de gestion dynamique (DMV) que SQL Server, à quelques différences près. Les DMV sont un aspect essentiel de la supervision des performances, car vous pouvez afficher les données de performance clés de SQL Server à l’aide de requêtes T-SQL standard. Par exemple, vous pouvez afficher des informations telles que les requêtes actives, l’utilisation des ressources, les plans de requête et les types d’attente des ressources. Vous trouverez plus d’informations sur les DMV avec Azure SQL plus loin dans cette unité.

Événements étendus

Azure SQL offre quasiment la même infrastructure d’événements étendus que SQL Server, à quelques différences près. Les événements étendus vous donnent la possibilité de suivre les événements clés de l’exécution dans SQL Server qui alimente Azure SQL. Pour des performances optimales, les événements étendus vous permettent de suivre l’exécution de requêtes individuelles. Vous trouverez plus d’informations sur les événements étendus avec Azure SQL plus loin dans cette unité.

Profilage de requête léger

Le profilage léger est une méthode avancée pour résoudre des problèmes de scénarios qui nécessitent la récupération du plan d’exécution réel pour des requêtes en transit et des requêtes à valeur élevée. En raison de sa faible surcharge, tout serveur qui n’est pas déjà lié au processeur peut exécuter un profilage léger en continu et permettre aux professionnels de la base de données de tirer parti de toute exécution en cours à tout moment ; par exemple, en utilisant le Moniteur d’activité dans SQL Server Management Studio (SSMS) ou en interrogeant directement sys.dm_exec_query_profiles ou sys.dm_exec_query_statistics_xml.

Vous pouvez utiliser un profilage de requête léger pour examiner le plan de requête et l’état d’exécution d’une requête active. Il s’agit d’une fonctionnalité clé qui permet de déboguer les performances de requête pour les instructions lorsqu’elles sont en cours d’exécution. Cette fonctionnalité permet de résoudre les problèmes de performance plus rapidement qu’en recourant à des outils comme les événements étendus pour suivre les performances des requêtes. Vous pouvez accéder au profilage de requête léger via les vues de gestion dynamique (DMV) qui sont activées par défaut dans Azure SQL, car elles sont destinée à SQL Server 2019 et versions ultérieures.

Fonctionnalités de débogage du plan de requête

Dans certains cas, vous pouvez avoir besoin de détails supplémentaires sur les performances des requêtes pour une instruction T-SQL individuelle. Les instructions T-SQL SET, comme SHOWPLAN et STATISTICS, peuvent fournir ces détails et sont entièrement prises en charge dans Azure SQL, comme c’est le cas dans SQL Server.

Magasin des requêtes

Le Magasin des requêtes est un enregistrement historique de l’exécution des performances pour les requêtes stockées dans la base de données utilisateur. Le Magasin des requêtes est activé par défaut pour Azure SQL. Il fournit des fonctionnalités telles que la correction de plan automatique et le réglage automatique. Les rapports SQL Server Management Studio (SSMS) pour le magasin sont disponibles pour Azure SQL. Utilisez ces rapports pour examiner les principales requêtes consommatrices de ressources, les différences entre les plans de requête et les principaux types d’attente dans le cadre de scénarios d’attente de ressources.

Visualisations des performances

Pour Azure SQL Database, vous pouvez voir les informations de performances du Magasin des requêtes intégré dans le portail Azure au moyen de visualisations. Certaines des informations liées au Magasin des requêtes sont ainsi les mêmes que celles fournies par un outil client comme SSMS. Utilisez les options Vue d’ensemble des performances et Query Performance Insight du Portail Azure.

Détails des DMV

Les DMV jouent un rôle moteur dans la supervision et la résolution des problèmes de performances depuis de nombreuses années avec SQL Server. Les DMV couramment utilisées avec SQL Server sont disponibles avec Azure SQL, mais d’autres sont spécifiques à Azure.

Azure SQL Managed Instance

Toutes les DMV pour SQL Server sont disponibles pour SQL Managed Instance. Les DMV clés telles que sys.dm_exec_requests et sys.dm_os_wait_stats sont couramment utilisées pour examiner les performances des requêtes.

L’affichage système sys.server_resource_stats est spécifique à Azure SQL Managed Instance et affiche l’utilisation historique des ressources. Il s’agit d’un outil important pour voir l’utilisation des ressources, car vous n’avez pas d’accès direct aux outils du système d’exploitation tels que l’Analyseur de performances.

Azure SQL Database

La plupart des DMV courantes dont vous avez besoin pour les performances, notamment sys.dm_exec_requests et sys.dm_os_wait_stats, sont disponibles. Notez que ces DMV fournissent uniquement des informations spécifiques à la base de données, et non à toutes les bases de données pour un serveur logique.

La DMV sys.dm_db_resource_stats est spécifique à Azure SQL Database et vous pouvez l’utiliser afin d’afficher un historique de l’utilisation des ressources pour la base de données. Utilisez cette DMV de la même façon que vous utilisez sys.server_resource_stats pour une instance managée.

La DMV sys.elastic_pool_resource_stats est similaire à sys.dm_db_resource_stats, mais vous pouvez l’utiliser afin d’afficher l’utilisation des ressources pour des bases de données de pool élastique.

DMV dont vous avez besoin

Vous avez besoin des DMV suivantes pour résoudre certains problèmes de performances pour Azure SQL :

• sys.dm_io_virtual_file_stats est une DMV importante, car vous n’avez pas d’accès direct aux métriques du système d’exploitation pour les performances des E/S par fichier.
• Disponible à la fois pour Azure SQL Database et SQL Managed Instance, sys.dm_os_performance_counters permet de voir des métriques de performances courantes de SQL Server. Utilisez-la pour afficher des informations sur les compteurs de performances SQL Server qui sont généralement disponibles dans l’Analyseur de performances.
• sys.dm_instance_resource_governance vous permet d’afficher les limites en termes de ressources d’une instance managée. Vous pouvez consulter ces informations pour connaître les limites attendues de vos ressources sans utiliser le Portail Azure.
• Utilisez sys.dm_user_db_resource_governance pour voir les limites communes en termes de ressources en fonction de l’option de déploiement, du niveau de service et de la taille de votre déploiement Azure SQL Database. Vous pouvez consulter ces informations pour connaître les limites attendues de vos ressources sans utiliser le Portail Azure.

Des DMV pour des aperçus plus approfondis

Ces DMV fournissent des informations plus approfondies sur les limites de ressources et la gouvernance des ressources pour Azure SQL. Elles ne sont pas destinées à être utilisées dans des scénarios courants, mais peuvent être utiles pour examiner en détail des problèmes de performances complexes. Consultez la documentation pour obtenir tous les détails de ces DMV :

• sys.dm_user_db_resource_governance_internal (SQL Managed Instance uniquement)
• sys.dm_resource_governor_resource_pools_history_ex
• sys.dm_resource_governor_workload_groups_history_ex

Détails sur les événements étendus

La fonctionnalité d’événements étendus est le mécanisme de suivi pour SQL Server. Les événements étendus pour Azure SQL sont basés sur le moteur SQL Server et sont donc quasiment les mêmes pour Azure SQL, avec toutefois quelques différences notables. Les sections suivantes traitent de ces différences.

Événements étendus pour Azure SQL Database

Vous pouvez utiliser des événements étendus pour Azure SQL Database, tout comme SQL Server, en créant des sessions et en utilisant des événements, des actions et des cibles. Gardez à l’esprit les points importants suivants lors de la création de sessions d’événements étendus :

• Les événements et les actions les plus couramment utilisés sont pris en charge.
• Les cibles de type fichier, ring_buffer et compteur sont prises en charge.
• Les cibles de type fichier sont prises en charge avec le stockage d’objets blob Azure, car vous n’avez pas accès aux disques du système d’exploitation sous-jacent.

Vous pouvez utiliser SSMS ou T-SQL pour créer et démarrer des sessions. Vous pouvez utiliser SSMS pour afficher des données cibles de la session d’événements étendue ou la fonction système sys.fn_xe_file_target_read_file.

Remarque

Il n’est pas possible d’utiliser SSMS afin d’afficher des données actives pour Azure SQL Database.

Il est important de savoir que tout événement étendu déclenché pour vos sessions est spécifique à votre base de données et qu’il ne s’applique pas au serveur logique.

Événements étendus pour Azure SQL Managed Instance

Comme pour SQL Server, vous pouvez utiliser des événements étendus pour SQL Managed Instance en créant des sessions et en utilisant des événements, des actions et des cibles. Gardez à l’esprit les points importants suivants lors de la création de sessions d’événements étendus :

• Tous les événements, cibles et actions sont pris en charge.
• Les cibles de type fichier sont prises en charge avec le stockage d’objets blob Azure, car vous n’avez pas accès aux disques du système d’exploitation sous-jacent.
• Certains événements spécifiques sont ajoutés pour SQL Managed Instance afin de suivre les événements spécifiques à la gestion et à l’exécution de l’instance.

Vous pouvez utiliser SSMS ou T-SQL pour créer et démarrer des sessions. Vous pouvez utiliser SSMS pour afficher des données cibles de la session d’événements étendue ou la fonction système sys.fn_xe_file_target_read_file. La capacité de SSMS à voir des données actives est prise en charge pour SQL Managed et Azure SQL Managed Instance.

Scénarios de performances pour Azure SQL

Pour déterminer comment appliquer les outils et fonctionnalités de supervision et de dépannage des performances, il est important d’examiner les performances d’Azure SQL à travers différents scénarios.

Scénarios de niveau de performances courants

Une technique courante pour la résolution des problèmes de performances SQL Server consiste à déterminer si un problème de performances est Exécuter (UC élevée) ou En attente (en attente d’une ressource). Le diagramme suivant montre un arbre de décision pour savoir si un problème de performances SQL Server est en cours d’exécution ou en attente et déterminer comment utiliser les outils de performances pour identifier la cause et la solution.

Nous allons étudier plus en détail chaque aspect du diagramme.

Exécution et attente

Examinons pour commencer l’utilisation globale des ressources. Pour un déploiement SQL Server standard, vous pouvez utiliser des outils tels que l’Analyseur de performances dans Windows ou la commande top dans Linux. Vous pouvez utiliser les méthodes suivantes pour Azure SQL :

• Portail Azure/PowerShell/alertes

  Azure Monitor a intégré des mesures pour afficher l’utilisation des ressources pour Azure SQL. Vous pouvez également configurer des alertes pour rechercher des conditions d’utilisation des ressources.

• sys.dm_db_resource_stats

  Pour Azure SQL Database, vous pouvez consulter cette DMV pour voir l’utilisation des ressources d’UC, de mémoire et d’e/s pour le déploiement de la base de données. Cette DMV prend un instantané de ces données toutes les 15 secondes.

• sys.server_resource_stats

  Cette DMV se comporte comme sys.dm_db_resource_stats, mais elle est permet de voir l’utilisation des ressources de SQL Managed Instance au niveau du processeur, de la mémoire et des E/S. Cette DMV prend également un instantané toutes les 15 secondes.

• sys.dm_user_db_resource_governance

  Pour Azure SQL Database, cette DMV retourne les paramètres de configuration et de capacité réels utilisés par les mécanismes de gouvernance des ressources dans la base de données actuelle ou le pool élastique.

• sys.dm_instance_resource_governance

  Pour Azure SQL Managed Instance, cette DMV retourne des informations semblables à sys.dm_user_db_resource_governance, mais pour la version Managed Instance SQL actuelle.

En cours d’exécution

Si vous avez déterminé que le problème est lié à une utilisation élevée du processeur, on parle de scénario en cours d’exécution. Un scénario en cours d’exécution peut impliquer des requêtes qui consomment des ressources via la compilation ou l’exécution. Utilisez les outils suivants pour effectuer une analyse plus poussée :

• Magasin des requêtes

  Utilisez les rapports de ressources les plus gourmandes dans SSMS, les affichages catalogue du Magasin des requêtes , ou Query Performance Insight dans le Portail Azure (Azure SQL Database uniquement) pour rechercher les requêtes qui consomment le plus de ressources UC.

• sys.dm_exec_requests

  Utilisez cette vue de gestion dynamique (DMV) dans SQL Azure pour obtenir un instantané de l’état des requêtes actives. Recherchez les requêtes avec l’état RUNNABLE et le type d’attente SOS_SCHEDULER_YIELD pour voir si la capacité de votre processeur est suffisante.

• sys.dm_exec_query_stats

  Vous pouvez utiliser cette DMV de la même façon que le Magasin des requêtes pour rechercher les principales requêtes consommatrices de ressources. N’oubliez pas qu’elle n’est disponible que pour les plans de requête mis en cache, là où le Magasin des requêtes fournit un enregistrement historique persistant des performances. Cette DMV vous permet également de rechercher le plan de requête pour une requête mise en cache.

• sys.dm_exec_procedure_stats

  Cette DMV fournit des informations très similaires à celles de sys.dm_exec_query_stats, à l’exception des informations de performance qui peuvent être affichées au niveau de la procédure stockée.

  Une fois que vous avez déterminé la ou les requêtes qui consomment le plus de ressources, vous devrez peut-être examiner si vous disposez de suffisamment de ressources processeur pour votre charge de travail. Vous pouvez déboguer des plans de requête avec des outils tels que le profilage des requêtes léger, les instructions SET, le Magasin des requêtes ou le suivi des événements étendus.

En attente

Si votre problème ne semble pas être une utilisation élevée des ressources du processeur, il peut s’agir d’un problème de performances lié à l’attente d’une ressource. Les scénarios impliquant l’attente de ressources sont les suivants :

• Attente d’E/S
• Attente de verrouillage
• Attente de verrou
• Limites du pool de tampons
• Allocations de mémoire
• Éviction du cache du plan

Pour effectuer une analyse sur des scénarios en attente, vous devez généralement examiner les outils suivants :

• sys.dm_os_wait_stats

  Utilisez cette DMV pour voir les principaux types d’attente pour la base de données ou l’instance. Cela peut vous aider à déterminer l’action à effectuer ensuite, en fonction des types d’attente les plus importants.

• sys.dm_exec_requests

  Utilisez cette DMV dans le but de rechercher des types d’attente spécifiques pour des requêtes actives afin d’identifier les ressources qu’elles attendent. Il peut s’agir d’un scénario de blocage standard qui attend les verrous d’autres utilisateurs.

• sys.dm_os_waiting_tasks

  Vous pouvez utiliser cette DMV pour rechercher des types d’attente pour une tâche particulière d’une requête spécifique en cours d’exécution, peut-être pour voir pourquoi cela prend plus de temps que la normale. sys.dm_os_waiting_tasks contient les statistiques d’attente actives que sys.dm_os_wait_stats agrège au fil du temps.

• Magasin des requêtes

  Le Magasin des requêtes fournit des rapports et des affichages catalogue qui affichent une agrégation des premières attentes en matière d’exécution du plan de requête. Il est important de savoir qu’une attente de processeur équivaut à un problème d’exécution.

Conseil

Vous pouvez utiliser des événements étendus pour tout scénario en cours d’exécution ou en attente. Pour ce faire, vous devez configurer une session d’événements étendus pour tracer les requêtes. Cette méthode pour déboguer un problème de performances est plus avancée et peut renvoyer beaucoup d’informations en échange d’une surcharge de performances supérieure à celle des DMV.

Scénarios spécifiques à Azure SQL

Certains scénarios de performance, en cours d’exécution et en attente, sont spécifiques à Azure SQL. Il s’agit notamment de la gouvernance des journaux, des limites des workers, des attentes rencontrées aux niveaux de service Critique pour l’entreprise et des attentes spécifiques à un déploiement Hyperscale.

Gouvernance du journal

Azure SQL peut utiliser la gouvernance du taux de journalisation pour imposer des limites en termes de ressources à l’utilisation du journal des transactions. Vous en aurez peut-être besoin pour garantir des limites de ressources et répondre aux contrats SLA promis. La gouvernance des journaux peut être vue à partir des types d’attente suivants :

• LOG_RATE_GOVERNOR : attentes pour Azure SQL Database
• POOL_LOG_RATE_GOVERNOR : attentes pour les pools élastiques
• INSTANCE_LOG_GOVERNOR : attentes pour Azure SQL Managed Instance
• HADR_THROTTLE_LOG_RATE* : attente pour la latence critique pour l’entreprise et de la géoréplication

Limites du rôle de travail

SQL Server utilise un pool de threads de travail, mais a des limites sur le nombre maximal de rôles de travail. Les applications avec un grand nombre d’utilisateurs simultanés peuvent approcher les limites du Worker appliquées pour Azure SQL Database et SQL Managed Instance :

• Azure SQL Database a des limites basées sur le niveau de service et la taille. Si vous dépassez cette limite, une nouvelle requête reçoit une erreur.
• À l’heure actuelle, SQL Managed Instance utilise max worker threads pour que les Workers se trouvant au-delà de cette limite puissent voir les attentes THREADPOOL.

Attentes HADR critiques pour l’entreprise

Si vous utilisez un niveau de service Critique pour l’entreprise, vous pouvez voir de manière inattendue les types d’attente suivants :

• HADR_SYNC_COMMIT
• HADR_DATABASE_FLOW_CONTROL
• HADR_THROTTLE_LOG_RATE_SEND_RECV

Même si ces attentes peuvent ne pas ralentir votre application, vous ne vous attendez peut-être pas à les voir. En effet, elles sont normalement spécifiques à l’utilisation d’un groupe de disponibilité Always On. Le niveau Critique pour l’entreprise utilise la technologie des groupes de disponibilité pour implémenter les fonctionnalités SLA et de disponibilité de ce niveau de service. Ces types d’attente sont donc attendus. Notez que des temps d’attente longs peuvent indiquer un goulot d’étranglement, par exemple une latence des E/S ou un réplica en arrière-plan.

Hyperscale

L’architecture hyperscale peut entraîner des types d’attente uniques précédés de RBIO (une indication possible de la gouvernance des journaux). Les DMV, affichages catalogue et événements étendus ont également été améliorés pour afficher les métriques des lectures du serveur de pages.

Dans l’exercice suivant, vous découvrirez comment monitorer et résoudre un problème de performances dans Azure SQL en utilisant des outils et des connaissances que vous avez obtenus dans cette unité.