Compteurs de performance SQL et administration proactive 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Document 

Auteur Olivier Philippe

Email olivier.philippe@bewise.fr

Web www.bewise.fr

Version 1.0

Nombre de pages 14

Date 08/01/2006 

1. Introduction

Une fois l’application développée, testée, déboguée, et livrée, bien souvent le développeur ne se soucie plus de l’environnement de production. 

Il existe cependant un certain nombre de paramètres à surveiller afin de veiller au bon fonctionnement d’une application, que ceux-ci concernent l’administration ou la conception et l’implémentation de l’application elle-même. 

Un suivi des performances du serveur permet tout autant d’assurer une bonne disponibilité à vos applications que d’optimiser les performances d’une application en particulier. Il existe deux types d’optimisations possibles à traiter en parallèle :

• optimisation du temps de réponse : liée essentiellement à l’applicatif et à son utilisation (temps mis par un utilisateur pour accéder à une donnée)
• optimisation du débit : plutôt liée à la plate-forme matérielle du serveur (nombre de requêtes traitées par seconde par le serveur)

Le principal outil de monitoring reste le moniteur système (ou moniteur de performances). Ce logiciel, intégré à Windows sous la forme d’une console MMC, va permettre de suivre en temps réel une panoplie complète de compteurs de performance installé sur la machine par chaque applicatif : ainsi SQL Serveur installe différents compteurs permettant de suivre son comportement. Vous pourrez également intégrer à votre application développée en .Net vos propres compteurs applicatifs de performance ou de suivi fonctionnel (un compteur peut exposer le nombre de bons de commandes actifs, par exemple).  

Le moniteur système permet également de notifier automatiquement un administrateur ou d’effectuer une action spécifique dès qu’un niveau de performance configuré est atteint ou dépassé. Une bonne configuration vous permettra donc d’agir avant qu’il ne soit trop tard. 
 

Nous allons donc découvrir dans cet article les principaux compteurs qui vont nous intéresser, et étudier comment mettre en place une administration proactive basée sur un suivi de ces compteurs par rapport à un niveau d’alerte donné.

2. Moniteur de performances

Le moniteur de performances est installé par défaut dans les outils d’administration (accessibles par le panneau de configuration) 
 
 

Une fois lancée, la console MMC qui l’héberge va afficher 3 compteurs par défaut. Vous allez pouvoir y ajouter les votre en effectuant un clic droit sur la liste des compteurs en bas de l’écran. 

3. Compteurs de performance

1. Mémoire

Memory – Available Mbytes

Indique le volume de mémoire disponible sur le serveur.

Doit être supérieur à 10 Mo au strict minimum. Augmenter la RAM si nécessaire. 

Memory – Pages/s

Indique le nombre d’accès à la mémoire virtuelle située sur le disque dur (swap). Doit impérativement rester autour de 0. Augmenter la RAM si nécessaire. 

SQLServer : Memory Manager  - Memory Grants Pending

Indique le nombre de process SQL en attente de mémoire. Doit impérativement rester autour de 0. Augmenter la RAM si nécessaire. 

SQLServer : Memory Manager  - Total Server Memory

Indique la mémoire totale allouée à SQL Server. Doit rester en rapport avec la mémoire physique disponible. Augmenter la RAM si nécessaire. 

SQLServer : Memory Manager  - Optimizer Memory

Indique le volume de mémoire allouée à l’optimiseur de requêtes. De grandes variations mettent en évidence un excès de code SQL dynamique.

2. Cache SQL

SQLServer : Buffer Manager  - Buffer Hit Ratio

Indique le pourcentage d’accès aux données présentes dans le cache mémoire (Ram ou Swap) par rapport à des accès direct au disque dur. Doit être autour de 90% en fonctionnement normal.

3. CPU et Threads

Processor - % Processor Time

Indique le pourcentage d’utilisation du processeur. Une valeur basse est optimale, il faut éviter de dépasser 90% en régime continu. Augmenter la puissance processeur ou assouplir la charge applicative en simplifiant certaines requêtes. 

Processor - % Privileged Time

Indique le pourcentage d’utilisation du processeur en mode privilégié (noyau). Doit être autour de 0% sur un système équilibré. 

System – Processor Queue Length

Indique le nombre de threads actifs en attente du processeur. Doit être proche de 0 et inférieur à 10 en général. 

System – Context Switches/s

Le système de threading permet de partager le temps processeur entre les différentes tâches demandées par les processus. Le processeur ne peut traiter qu’un seul thread à la fois et les exécute donc les uns à la suite des autres en leur affectant un quota de temps suivant leur priorité. Un thread inactif (par exemple qui attend une ressource disque) va par ailleurs laisser la place au thread suivant. Le passage d’un thread à l’autre est très gourmand en ressource puisque le processeur doit mettre à jour le contexte d’exécution.

Une valeur élevée du pourcentage d’utilisation du processeur associée à des changements de contexte importants (> 8000/s) place le serveur dans une position critique. Vous pouvez dans ce cas configurer SQL Server pour fonctionner en mode fibre (dans l’onglet « propriétés » du serveur) : un thread peut contenir plusieurs fibres NT, ce qui permet de diminuer les changements de contexte. Par contre ce fonctionnement apporte une petite surcharge au système d’exploitation et ne doit pas s’utiliser sans raison justifiée.

4. Disque

Physical Disk - % Disk Time

Comme pour le processeur, doit être inférieur à 90%. Surveiller conjointement l’utilisation du cache SQL Server. 

Physical Disk – Avg Disk Queue Length

Indique la taille de la file d’attente du disque dur. Doit absolument être proche de 0 (<2). 

Physical Disk –Disk Read/Writes /s

Indique le nombre de lecture et d’écriture vers l’unité de disque. Doit absolument être inférieur aux capacités du système.

5. Accès aux données : verrous, recompilation, index scan

SQLServer : Locks – Average Wait Time (ms)

Temps d’attente moyen pour chaque blocage SQL qui survient sur le serveur. Doit être minimal afin d’optimiser les temps de réponse. 

SQLServer : Locks – Number of Deadlocks /s

Nombre d’interblocages survenant sur SQL Server. Un interblocage traduit le mauvais design d’une application.

Par exemple, les procédures stockées suivantes … 

CREATE PROCEDURE sUpdateOne CREATE PROCEDURE sUpdateTwo

AS AS

    UPDATE TABLE_A SET ...     UPDATE TABLE_B SET ...

    UPDATE TABLE_B SET ...     UPDATE TABLE_A SET ...

GO GO 

… vont se bloquer entre elles car elles accèdent aux même ressources dans un ordre différent : les deadlocks sont inévitables dans ce cas, et SQL Server va annuler l’un des deux appels. 

SQLServer : SQL Statistics – Re-Compilations/s

Indique le nombre de recompilations des plans d’exécutions par seconde. Une valeur basse est généralement attendue mais peut dépendre de l’applicatif concerné. Par exemple, une procédure dont les paramètres ou les données atteintes changent très fréquemment peut être conçue avec l’option « WITH RECOMPILE » afin de la recompiler à chaque exécution afin d’éviter d’utiliser un plan inapproprié. 

SQLServer : Plan Cache – Cache Hit Ratio

Indique le pourcentage de recherches réussies de plan d’exécution en cache mémoire. Une valeur élevée est souhaitée. Comme pour tout cache, on s’attend à une valeur qui avoisine les 80-90% en fonctionnement optimal. 

SQLServer : Access Methods – Full Scans /s

Un scan complet peut concerner une table ou un index (ordonnée ou non) et traduit un manque d’index utiles ou un manque de critères de filtrage. Déterminer quelles requêtes sont en cause et essayer de les optimiser. 

SQLServer : Access Methods – Index Searches /s

Les index permettent d’accéder de façon optimisée aux données. SQL Server décide en fonction du contexte d’exécution si un index sera utile ou non : le choix dépend de la sélectivité des critères de filtrage (clause WHERE) par rapport à la répartition statistique des données dans l’index. La recherche de « DUPONT » dans l’annuaire téléphonique ne sera pas traitée avec un index mais plutôt avec un balayage complet, contrairement à « ZORGLUB » qui n’a qu’une seule entrée et qui tirera parti de l’utilisation d’un index.

4. Administration proactive

Lorsque les valeurs limites de certains de ces compteurs sont atteintes, il est généralement trop tard pour agir : le serveur est saturé, la moindre requête le sature encore plus, et les utilisateurs ne peuvent plus travailler. 

L’analyseur de performance Windows permet de surveiller en tâche de fond vos différents compteurs et de vous alerter dès que l’un d’eux dépasse un seuil fixé , situé bien entendu en-dessous de la zone critique. 

Création d’une alerte : 
 
 

Déplier le nœud des « alertes et logs » afin de créer un nouvelle alerte. 
 
 

Une alerte est un ensemble de compteurs de performance pour lesquels on spécifie une valeur seuil (mini ou maxi) au-delà de laquelle l’alerte va être notifiée. Dans cet exemple, je souhaite surveiller l’utilisation de la mémoire sur le serveur et je regroupe donc les compteurs associés. 
 
 

Dans l’onglet suivant, je spécifie les actions à entreprendre en cas de dépassement d’un seuil :  
 
 

Dans cet exemple, je souhaite :

• tracer l’alerte dans le journal de log Windows,
• envoyer une boite de message de notification sur un poste du réseau (net send)
• démarrer une trace de compteurs (enregistre dans un fichier binaire ou texte les valeurs des compteurs de performance souhaités afin de pouvoir revoir et analyser le comportement de la machine après le déclenchement de l’alerte)
• exécuter l’exécutable NotifyAlert.exe qui prend en argument un descriptif des compteurs en erreur et qui m’enverra un mail d’alerte ou redémarrera une application concernée, par exemple.

Enfin, je peux également décider de planifier l’exécution de l’alerte ou la démarrer et l’arrêter manuellement afin de contrôle l’intervalle de temps pour lequel le suivi des compteurs est actif. 
 
 

Mon alerte est créée, mon système est désormais sous bonne garde … 

5. Pour aller plus loin …

Deux autres outils sont livrés avec SQL Serveur pour vous aidez dans le monitoring d’une application d’accès aux données :

• SQL Server Profiler : permet de capturer toute l’activité qui se passe sur le serveur en surveillant différents événements (exécutions SQL, recompilation procédures stockées, transactions validées/annulées, verrous, plans d’exécution, …) en temps réél. La trace effectuée sert par ailleurs de base à l’assistant d’optimisation d’index (Index Tuning Wizard). On peut également l’utiliser afin de réaliser un audit de sécurité ou d’utilisation d’une base de données.
• Analyseur de Requêtes : permet de consulter et analyser les plans d’exécution calculés par le moteur SQL Server afin de déterminer, par exemple, si un index particulier est correctement utilisé.

Attention, il existe une certaine incompatibilité entre les versions 2000 et 2005 pour les outils d’administration qui risque de provoquer des résultats incohérents. Penser à utiliser une version identique à celle du serveur surveillé.