Précédent

Suivant

Expliquer les statistiques d’attente

Effectué

L’une des méthodes holistiques qui permet de superviser les performances du serveur consiste à évaluer ce que le serveur attend. Les statistiques d’attente sont complexes et SQL Server est instrumenté avec des centaines de types d’attentes, qui surveillent chaque thread en cours d’exécution et consigne ce que le thread attend.

La détection et la résolution des problèmes de performances SQL Server nécessitent de comprendre le fonctionnement des statistiques d’attente et la façon dont le moteur de base de données les utilise lors du traitement d’une requête.

Les statistiques d’attente sont divisées selon trois catégories : les attentes de ressources, les attentes de file d’attente et les attentes externes.

• Les attentes de ressource se produisent lorsqu’un thread de travail dans SQL Server demande l’accès à une ressource qui est déjà utilisée par un thread. Ces attentes sont par exemple des attentes de verrous, de verrous internes ou d’E/S de disque.
• Les attentes de file d’attente se produisent lorsqu’un thread de travail est inactif et qu’il attend qu’un travail lui soit affecté. Exemples d’attente de file d’attente : supervision des blocages et nettoyage des enregistrements supprimés.
• Les attentes externes se produisent lorsque SQL Server attend la fin d’un processus externe comme l’exécution d’une requête de serveur lié. Un exemple d’attente externe est une attente réseau liée au renvoi d’un jeu de résultats volumineux vers une application cliente.

Vous pouvez vérifier la vue système sys.dm_os_wait_stats pour explorer toutes les attentes rencontrées par les threads qui ont été exécutés et sys.dm_db_wait_stats pour Azure SQL Database. La vue système sys.dm_exec_session_wait_stats répertorie les sessions d’attente actives.

Ces vues système permettent à l’administrateur de base de données d’avoir une vue d’ensemble des performances du serveur, et d’identifier facilement les problèmes de configuration ou de matériel. Ces données sont conservées dès le démarrage de l’instance. Cependant, elles peuvent être effacées si nécessaire pour identifier des modifications.

Pour chaque type d’attente, les statistiques sont évaluées sous la forme d’un pourcentage par rapport au nombre total d’attentes du serveur.

Les résultats de cette requête issus de sys.dm_os_wait_stats indiquent le type d’attente, l’agrégation des temps d’attente en pourcentage (colonne Pourcentage d’attente) et le temps d’attente moyen pour chaque type d’attente (en secondes).

Dans ce cas, le serveur comprend des groupes de disponibilité AlwaysOn, comme l’indiquent les types d’attentes REDO_THREAD_PENDING_WORK et PARALLEL_REDO_TRAN_TURN. Le pourcentage relativement élevé des attentes CXPACKET et SOS_SCHEDULER_YIELD indique que ce serveur subit une sursollicitation de son processeur.

Étant donné que les DMV fournissent une liste des types d’attentes avec le temps cumulé le plus élevé depuis le dernier démarrage de SQL Server, la collecte et le stockage périodiques des données statistiques d’attente peuvent vous aider à comprendre et à mettre en corrélation les problèmes de performances avec d’autres événements de base de données.

En partant du principe que les DMV vous fournissent une liste des types d’attentes avec le temps cumulé le plus élevé depuis le dernier démarrage de SQL Server, la collecte et le stockage périodiques des statistiques d’attente peuvent vous aider à comprendre et à mettre en corrélation les problèmes de performances avec d’autres événements de base de données.

Plusieurs types d’attentes sont disponibles dans SQL Server, mais certains d’entre eux sont courants.

• RESOURCE_SEMAPHORE : ce type d’attente indique que des requêtes attendent que de la mémoire soit disponible, et peut indiquer des allocations de mémoire excessives pour certaines requêtes. Ce problème se produit généralement lorsque l’exécution d’une requête est trop longue, voire qu’elle expire. Ces types d’attente peuvent être dus à des statistiques obsolètes, à des index manquants ou à une simultanéité excessive des requêtes.

• LCK_M_X : des occurrences fréquentes de ce type d’attente peuvent indiquer un blocage, qui peut être résolu en remplaçant le niveau d’isolement par READ COMMITTED SNAPSHOT ou en apportant des modifications à l’indexation en vue de réduire les temps de transaction, voire de mieux gérer les transactions dans le code T-SQL.

• PAGEIOLATCH_SH : ce type d’attente peut indiquer un problème avec les index (ou un manque d’index utiles), qui force SQL Server à analyser trop de données. Si le nombre d’attentes est faible, mais que le temps d’attente est élevé, cela peut indiquer des problèmes de performances au niveau du stockage. Vous pouvez observer ce comportement en analysant les données dans les colonnes waiting_tasks_count et wait_time_ms de la vue système sys.dm_os_wait_stats afin de calculer un temps d’attente moyen pour un type d’attente donné.

• SOS_SCHEDULER_YIELD : ce type d’attente peut indiquer une utilisation élevée du processeur due à un nombre important d’analyses volumineuses ou à des index manquants, et souvent à un grand nombre d’attentes CXPACKET.

• CXPACKET : un nombre important d’attentes de ce type peut indiquer une configuration incorrecte. Dans les versions antérieures à SQL Server 2019, le paramètre par défaut de degré maximum de parallélisme vise à utiliser tous les processeurs disponibles pour les requêtes. En outre, le paramètre de seuil de coût pour le parallélisme est configuré sur la valeur 5 par défaut, ce qui peut entraîner l’exécution de petites requêtes en parallèle et donc, limiter le débit. Le fait de réduire la valeur de MAXDOP et d’augmenter le seuil de coût pour le parallélisme peut permettre de réduire les occurrences de type d’attente. Toutefois, le type d’attente CXPACKET peut également indiquer une utilisation élevée du processeur, ce qui peut se résoudre généralement en paramétrant les index.

• PAGEIOLATCH_UP : des occurrences de ce type d’attente sur des pages de données 2:1:1 peuvent indiquer une contention de la TempDB sur les pages de données PFS. Chaque fichier de données comporte une page PFS pour chaque groupe de données équivalant à 64 Mo. Cette attente est généralement causée par le fait de n’avoir qu’un seul fichier TempDB, car le comportement par défaut avant SQL Server 2016 consistait à n’utiliser qu’un seul fichier de données pour TempDB. Il est recommandé d’utiliser entre 1 et 8 fichiers par cœur de processeur. Il est également important que vos fichiers de données TempDB aient la même taille et qu’ils aient les mêmes paramètres de croissance automatique, ceci pour qu’ils soient utilisés équitablement. SQL Server 2016 et les versions plus récentes contrôlent la croissance des fichiers de données TempDB afin de garantir une croissance cohérente et simultanée.

Outre les DMV mentionnées plus haut, le Magasin des requêtes effectue également le suivi des attentes qui sont associées à une requête donnée. Les données d’attente suivies par le Magasin des requêtes n’ont toutefois pas le même niveau de précision que les données DMV, mais elles peuvent fournir une vue d’ensemble intéressante de ce qu’une requête attend.

Continuer