SQL Server

Optimisation des performances des processeurs sous SQL Server

Zach Nichter

Vue d'ensemble:

• Dépannage des problèmes de performances des bases de données
• Examen des causes matérielles
• Utilisation de PerfMon pour traquer les goulots d'étranglement des bases de données
• Évaluation des performances des requêtes

Le dépannage des problèmes de performances sur un système de base de données peut apparaître comme une tâche écrasante. Savoir où chercher les problèmes est essentiel, mais il est encore plus important de savoir pourquoi votre système réagit comme il le fait suite à une requête donnée. Un certain nombre de facteurs peuvent affecter l'utilisation du processeur

sur un serveur de base de données : compilation et recompilation d'instructions SQL, index manquants, opérations multi-thread, goulots d'étranglement de disque, goulots d'étranglement de mémoire, maintenance de routine, opérations d'extraction, de transformation et de chargement (ETL), entre autres. Le fait que le processeur soit très sollicité n'est pas un problème en soi, après tout les processeurs ont été créés pour servir. La clé d'une bonne utilisation des processeurs sur votre serveur de base de données consiste à s'assurer que le processeur passe son temps à traiter ce que vous lui demandez de traiter et non à gaspiller des cycles sur du code mal optimisé ou du matériel lent.

Deux voies convergentes

Partant d'un niveau hiérarchique élevé, deux grandes voies s'ouvrent à vous pour identifier les problèmes de performances du processeur. La première consiste à examiner les performances matérielles du système, un exercice qui permet de s'orienter lorsque l'on aborde la deuxième voie, à savoir l'analyse de l'efficacité de traitement des requêtes du serveur. Cette deuxième voie est généralement plus efficace pour l'identification des problèmes de performances de SQL Server™. À moins de savoir exactement où résident les problèmes de performances des requêtes, vous devez toujours commencer par une évaluation des performances système. Au final, vous explorez généralement les deux voies. Posons quelques principes fondamentaux avant d'examiner ces deux voies.

Poser les bases

Hyper-threading

L'hyper-threading est un sujet qui mérite d'être abordé plus avant pour la façon dont il affecte SQL Server. L'hyper-threading présente en fait deux processeurs logiques au système d'exploitation pour chaque processeur physique. Cette technologie consiste à réserver des créneaux d'utilisation sur les processeurs physiques, de façon à mieux les exploiter. Le site Web d'Intel (intel.com/technology/platform-technology/hyper-threading/index.htm) donne une description plus complète de cette technologie.

Sur les systèmes SQL Server, le SGBD (système de gestion de base de données) contrôle ses propres files d'attente et threads de façon très efficace, si bien que l'hyper-threading vient surcharger les processeurs physiques de systèmes qui les sollicitent déjà beaucoup. Lorsque SQL Server met en attente plusieurs requêtes destinées à différents planificateurs, le système d'exploitation doit effectuer de nombreuses commutations de contexte des threads sur les processeurs physiques pour satisfaire les requêtes en cours, même si les deux processeurs logiques reposent sur le même processeur physique. Si vous constatez un taux de commutation de contexte supérieur à 5 000 par processeur physique, il est fortement recommandé de désactiver l'hyper-threading sur votre système et de tester à nouveau les performances.

Dans de rares cas, des applications qui sollicitent intensément le processeur sous SQL Server peuvent utiliser l'hyper-threading de façon efficace. Testez toujours vos applications sous SQL Server avec l'hyper-threading activé et désactivé avant d'implémenter des modifications sur vos systèmes de production.

Un processeur double cœur haut de gamme est beaucoup plus performant que la mémoire vive d'un ordinateur, qui est elle-même plus rapide qu'un périphérique externe. Un bon processeur peut contrôler environ six fois le débit des mémoires DDR2 actuelles les plus performantes, et environ deux fois le débit des meilleures mémoires DDR3. Le débit mémoire type est plus de 10 fois supérieur à celui des lecteurs Fibre Channel les plus rapides. En outre, les disques durs peuvent uniquement exécuter un nombre fini d'opérations d'entrée/sortie par seconde, une valeur qui est entièrement limitée par le nombre de recherches par seconde pouvant être exécutées par un lecteur. Pour être honnête, il est relativement rare qu'un seul périphérique de stockage soit utilisé pour gérer tous les besoins de stockage des systèmes de base de données d'entreprise. La plupart des configurations actuelles sont constituées de réseaux de stockage (SAN) sur les serveurs de base de données d'entreprise ou de grands ensembles RAID qui peuvent annuler ou tout au moins réduire les problèmes liés à l'entrée/sortie du disque sur les processeurs. Rappelez-vous que quelle que soit votre configuration, les goulots d'étranglement de disque et mémoire peuvent affecter les performances de vos processeurs.

En raison des différences de vitesse d'E/S, l'extraction de données de disques est beaucoup plus coûteuse que l'extraction de données de la mémoire. Une page de données dans SQL Server fait 8 Ko. Dans le contexte de SQL Server, une extension est composée de 8 pages de 8 Ko, soit 64 Ko. Il est important de bien comprendre ceci, car lorsque SQL Server demande une page de données particulière sur le disque, ce n'est pas seulement la page de données qui est récupérée mais toute l'extension qui héberge la page de données. Ce mode de récupération est plus efficace pour SQL Server pour plusieurs raisons, mais je n'entrerai pas dans les détails ici. Á performances optimales, l'extraction d'une page de données déjà présente dans la mémoire cache à partir du pool de mémoires tampons doit prendre moins d'une demi-milliseconde. La récupération d'une extension à partir d'un disque doit prendre entre 2 et 4 millisecondes dans un environnement optimal. Selon moi, une lecture dans un sous-système de disques performant et sain devrait prendre entre 4 et 10 ms. La récupération d'une page de données à partir de la mémoire est généralement entre 4 et 20 fois plus rapide que la récupération d'une page de données à partir du disque.

Lorsque SQL Server demande une page de données, il vérifie le cache des tampons en mémoire avant de chercher la page de données sur le sous-système de disque. Si la page de données est trouvée dans le pool de mémoires tampons, le processeur récupère les données et exécute la tâche requise. Ceci est appelé défaut de page logiciel. Les défauts de page logiciels sont idéaux pour SQL Server dans la mesure où les données qui sont récupérées dans le cadre d'une requête doivent figurer dans le cache des mémoires tampons avant d'être utilisées. Une page de données qui ne figure pas dans le cache des mémoires tampons doit être récupérée à partir du sous-système de disques du serveur. Lorsque le système d'exploitation doit extraire la page de données du disque, il s'agit d'un défaut de page matériel.

Lors de la corrélation des performances mémoire, des performances disque et des performances processeur, un dénominateur commun permet de tout mettre en perspective : le débit. En termes peu scientifiques, le débit indique la quantité de données que vous pouvez faire passer par un conduit fini.

Voie 1 : Performances système

Seules quelques méthodes permettent de déterminer si un serveur souffre d'un goulot d'étranglement au niveau du processeur, et si c'est le cas, les causes potentielles sont relativement limitées. Certains de ces problèmes peuvent être suivis à l'aide de PerfMon ou tout autre outil de surveillance de système semblable, tandis que les autres sont suivis à l'aide du Générateur de profils SQL Server ou d'autres outils similaires. Une autre méthode consiste à utiliser les commandes SQL via Query Analyser ou SSMS (SQL Server Management Studio).

Lorsque j'évalue les performances système, j'applique la philosophie suivante : « Commencer large, puis se concentrer sur les détails ». Évidemment, il est impossible d'analyser les zones à problèmes tant qu'elles n'ont pas été identifiées. Après avoir évalué l'utilisation globale du processeur avec des outils tels que PerfMon, vous pouvez utiliser ce dernier pour analyser des compteurs de performances très simples et faciles à comprendre.

L'un des compteurs de performances les plus connus indique le pourcentage de temps processeur. Lorsque vous exécutez PerfMon, il est sélectionné dès que vous ouvrez la fenêtre Ajouter des compteurs. Le compteur Pourcentage de temps processeur indique le temps pendant lequel les processeurs restent actifs. L'utilisation des processeurs est généralement considérée comme élevée lorsque cette valeur est supérieure ou égale à 80 pour cent pendant la majorité de votre période d'exécution maximale. Il est normal et prévisible que des pics de 100 % se produisent de temps en temps, même si le taux d'utilisation du serveur reste inférieur à 80 %.

Un autre compteur important est Longueur de la file d'attente du processeur, qui figure sous l'objet de performances System de PerfMon. Longueur de la file d'attente du processeur indique le nombre de threads qui sont en attente d'utilisation sur le processeur. SQL Server gère ses tâches à l'aide de planificateurs dans le moteur de base de données, où il place en file d'attente et traite ses propres requêtes. Dans la mesure où SQL Server gère son propre travail, il utilise un seul thread de processeur pour chaque processeur logique. Cela signifie qu'il doit y avoir un nombre minimal de threads prêts à travailler dans la file d'attente du processeur sur un système dédié à SQL Server. En général, vous ne devez pas avoir plus de cinq fois le nombre de processeurs physiques sur un système SQL Server dédié, mais je considère que plus de deux fois est déjà problématique. Sur les serveurs où le SGBD partage un système avec les autres applications, utilisez le compteur de performances Pourcentage de temps processeur en parallèle avec le compteur Changements de contexte/seconde (j'aborderai les commutateurs de contexte plus bas) pour déterminer si vos autres applications ou le SGBD doivent être transférés vers un serveur différent.

Lorsque je vois des files d'attente de processeur en conjonction avec une forte sollicitation du processeur, je me tourne vers les compteurs Compilations/seconde et Recompilations/seconde de SQL Server : Objet de performances SQL Statistics (voir la figure 1). La compilation et la recompilation des plans de requête viennent alourdir la charge du processeur d'un système. Vous devez voir des valeurs proches de zéro pour les recompilations, mais gardez un œil sur les tendances de vos systèmes pour déterminer comment votre serveur se comporte normalement et le nombre de compilations considéré comme normal. Les recompilations ne peuvent pas toujours être évitées, mais les requêtes et les procédures stockées peuvent être optimisées pour réduire les recompilations et recycler les plans de requête. Comparez ces valeurs aux véritables instructions SQL qui parviennent au système par l'intermédiaire de Nombre de requêtes de lots/seconde, qui figure également dans SQL Server : Objet de performances SQL Statistics. Si les compilations et recompilations par seconde comprennent un pourcentage élevé de requêtes de lots qui parviennent au système, cette zone doit être vérifiée. Dans certaines situations, les développeurs de code SQL peuvent ne pas comprendre comment ou pourquoi leur code contribue à ces types de problèmes de ressources système. Plus loin dans cet article, je fournirai quelques références pour réduire ce type d'activité.

Figure 1** Sélection des compteurs à surveiller **(Cliquer sur l'image pour l'agrandir)

Pendant que vous êtes dans PerfMon, consultez le compteur de performances Changements de contexte/seconde (voir la figure 2). Ce compteur indique combien de threads doivent être retirés des planificateurs du système d'exploitation (et non pas des planificateurs SQL) pour exécuter les threads en attente. Les commutateurs de contexte sont beaucoup plus fréquents sur les systèmes de base de données qui sont partagés avec d'autres applications, telles qu'IIS ou d'autres composants de serveur d'applications de fournisseurs tiers. Le seuil que j'utilise pour Changements de contexte/seconde est d'environ 5 000 fois le nombre de processeurs du serveur. Cette valeur peut également être élevée sur les systèmes où la technologie hyper-threading est activée et dont le processeur est sollicité de façon modérée. Lorsque la sollicitation du processeur et les changements de contexte par seconde dépassent tous les deux leur seuil de façon régulière, cela indique un goulot d'étranglement au niveau du processeur. S'il s'agit d'un phénomène régulier, vous devez envisager l'achat de processeurs plus rapides ou plus nombreux si votre système est obsolète. Pour de plus amples renseignements, voir l'encadré « Hyper-threading ».

Figure 2 Compteurs de performances à surveiller

L'écriture différée (SQL Server 2000) ou le moniteur de ressource (SQL Server 2005) de SQL Server constituent une autre zone à surveiller lorsque le processeur est fortement sollicité. Le vidage des caches de mémoire tampon et de procédure peut augmenter le temps processeur à l'aide du thread de ressources appelé Moniteur de ressource. Le Moniteur de ressource est un processus SQL Server qui détermine les pages à conserver et les pages à vider du pool de mémoires tampons dans le disque. Chaque page contenue dans les caches de mémoire tampon et de procédure est associée à un coût qui représente les ressources qui sont consommées lorsque cette page est placée dans le cache. Ce coût est réduit à chaque fois que Moniteur de ressource l'analyse. Lorsqu'une requête nécessite de l'espace de cache, les pages sont supprimées de la mémoire en fonction du coût associé à chaque page. Les pages avec les valeurs les plus basses sont éliminées en premier. L'activité de Moniteur de ressource peut être suivie avec le compteur de performances Écritures différées/seconde sous SQL Server : Objet de perfomances Buffer Manager dans PerfMon. Vous devez analyser la tendance de cette valeur pour déterminer le seuil type sur votre système. Ce compteur est généralement consulté conjointement avec les compteurs Espérance de vie d'une page et Points de vérification/seconde pour déterminer si la mémoire est soumise à une pression.

Le compteur Espérance de vie d'une page permet de mesurer la pression sur la mémoire. Le compteur Espérance de vie d'une page affiche le temps de séjour d'une page de données dans le cache de mémoires tampons. 300 secondes est un seuil considéré comme normal par les professionnels pour ce compteur. Toute valeur inférieure à une moyenne de 300 secondes pendant une période prolongée indique que les pages de données sont vidées de la mémoire trop fréquemment. Dans ce cas, le Moniteur de ressource est plus sollicité, ce qui en retour entraîne davantage d'activité au niveau des processeurs. Le compteur Espérance de vie d'une page doit être évalué en parallèle avec le compteur Pages de points de contrôle/seconde. Lorsqu'un point de contrôle figure dans le système, les pages de données déjà consultées dans le cache de mémoires tampons sont déplacées vers le disque, ce qui fait baisser la valeur d'espérance de vie d'une page. Le processus Moniteur de ressource fournit le mécanisme permettant de vider ces pages sur le disque. Par conséquent, pendant ces points de contrôle vous devez également vous attendre à voir la valeur Écritures différées/seconde augmenter. Si la valeur d'espérance de vie d'une page augmente tout de suite après la fin d'un point de contrôle, vous pouvez ignorer ce symptôme temporaire. Par contre, si vous constatez que la une valeur est régulièrement inférieure au seuil d'espérance de vie d'une page, il y a de très bonnes chances que de la mémoire supplémentaire résolve vos problèmes et restitue certaines ressources au processeur. Tous ces compteurs figurent dans SQL Server : Objet de performances Buffer Manager.

Voie 2 : Performances des requêtes

Suivi SP

Lors du suivi de votre application SQL Server, il est intéressant de se familiariser avec les procédures stockées utilisées pour le suivi. L'utilisation de l'interface graphique (Générateur de profils SQL Server) pour le suivi peut accroître la charge de système de 15 à 25 %. Si vous pouvez utiliser des procédures stockées lors du suivi, cette charge peut diminuer de moitié.

Lorsque je sais que mon système est soumis à un goulot d'étranglement quelque part et que je veux déterminer les instructions SQL qui posent problème sur mon serveur, j'exécute la requête ci-dessous. Cette requête permet d'examiner en détail les instructions et les ressources en cours d'utilisation, ainsi que les instructions qui doivent faire l'objet d'une amélioration des performances. Pour plus d'informations sur le suivi du code SQL, consultez l'article msdn2.microsoft.com/ms191006.aspx.

SELECT 
    substring(text,qs.statement_start_offset/2
        ,(CASE    
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) 
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r 
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

Les plans de requête sont évalués, optimisés, compilés et placés dans le cache de procédure lorsqu'une nouvelle requête est soumise à SQL Server. Lorsqu'une requête est soumise au serveur, le cache de procédure est consulté pour tenter d'associer un plan à une requête. Si aucun plan n'est trouvé, SQL Server crée nouveau un plan à cet effet, ce qui peut être une opération coûteuse.

Points à prendre en compte pour l'optimisation de processeur T-SQL :

• Réutilisation des plans de requête
• Réduction des compilations et recompilations
• Tri des opérations
• Jointures incorrectes
• Index manquants
• Analyses de tables/index
• Usage de fonctions dans les clauses SELECT et WHERE
• Opérations multi-thread

Essayons de prendre un peu de recul. SQL Server extrait généralement les données de la mémoire et du disque, et vous travaillez généralement avec plusieurs pages de données. Le plus souvent, plusieurs sections d'une application exploitent un enregistrement, exécutant plusieurs petites requêtes ou jointures de tables pour offrir une vue complète des données pertinentes. Dans les environnements OLAP, vos applications peuvent par exemple extraire des millions de lignes d'une ou deux tables de sorte que vous puissiez consolider, cumuler et résumer des données pour un rapport de ventes régional. Dans ce genre de situation, les données renvoyées peuvent être mesurées en millisecondes si elles sont en mémoire, mais ces millisecondes peuvent se transformer en minutes lors de l'extraction de ces mêmes données à partir d'un disque plutôt que de la mémoire.

Le premier exemple est une situation avec un volume élevé de transactions et la réutilisation des plans dépend de l'application. Une faible réutilisation des plans entraîne un grand nombre de compilations d'instructions SQL qui, à leur tour, sollicitent lourdement le processeur. Dans le deuxième exemple, la forte sollicitation des ressources système entraîne une suractivité du processeur du système car les données existantes doivent constamment être vidées du cache de mémoires tampons pour faire de la place pour le gros volume de nouvelles pages de données.

Imaginez un système hautement transactionnel, où une instruction SQL, telle que celle qui est indiquée ci-dessous, est exécutée 2 000 fois pendant une période de 15 minutes afin de récupérer des informations sur le carton d'expédition. Sans recyclage des plans de requête, vous pourriez avoir un temps d'exécution individuel d'environ 450 ms par instruction. Si le même plan de requête est utilisé après l'exécution initiale, chaque requête suivante pourrait s'exécuter en environ 2 ms, ce qui ramène le temps d'exécution total à environ 5 secondes.

USE SHIPPING_DIST01;
SELECT 
    Container_ID
    ,Carton_ID
    ,Product_ID
    ,ProductCount
    ,ModifiedDate
FROM Container.Carton
WHERE Carton_ID = 982350144;

La réutilisation des plans de requête est essentielle pour atteindre des performances optimales sur les systèmes hautement transactionnels, et s'obtient généralement par le paramétrage des requêtes ou des procédures enregistrées. Voici quelques ressources d'excellente qualité pour plus d'informations sur la réutilisation de plans de requêtes :

• Problèmes de la compilation par lot, de la recompilation et de la mise en cache des plans dans SQL Server 2005 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
• Optimisation des procédures stockées de SQL Server pour éviter les recompilations (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
• Recompilation des requêtes dans SQL Server 2000 (msdn2.microsoft.com/aa902682.aspx)

Vous trouverez une foule d'informations utiles dans les vues de gestion dynamique de SQL Server 2005. Lorsque le processeur est très sollicité, j'utilise une ou deux vues de gestion dynamique pour m'aider à déterminer si le processeur est utilisé de façon correcte ou non.

L'une de ces vues est sys.dm_os_wait_stats, qui permet aux administrateurs de base de données de déterminer chaque type de ressource ou fonction que SQL Server doit utiliser et de mesurer le temps d'attente du système lié à cette ressource. Les compteurs de ces vues de gestion dynamique sont cumulatifs. Cela signifie que pour bien identifier les ressources pouvant affecter les différentes zones du système, vous devez d'abord émettre une commande DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) pour réinitialiser tous les compteurs après avoir examiné les données des problèmes non résolus. La vue de gestion dynamique sys.dm_os_wait_stats est l'équivalent de la commande de vérification de la cohérence de la base de données DBCC SQLPERF(WAITSTATS) dans SQL Server 2000. Vous trouverez plus de détails sur les différents types d'attente dans la documentation en ligne de SQL Server à l'adresse msdn2.microsoft.com/ ms179984.aspx.

Il est important de comprendre que les temps d'attente sont normaux dans un système même si tout s'exécute de façon optimale. Vous devez simplement déterminer si ces temps morts sont affectés par des goulots d'étranglement au niveau du processeur. Les attentes de signal doivent être aussi réduites que possible par rapport au temps d'attente global. Le temps d'accès d'une ressource donnée à une ressource de type processeur peut être déterminé simplement en soustrayant le temps d'attente de signal du temps d'attente total. Cette valeur ne doit pas être supérieure à environ 20 % du temps d'attente total.

La vue de gestion dynamique sys. dm_exec_sessions affiche toutes les sessions ouvertes sur SQL Server. Cette vue offre un aperçu de haut niveau des performances de chaque session et des tâches que chaque session a exécutées depuis son ouverture. Elle inclut le temps total d'attente de la session, l'utilisation totale du processeur, l'utilisation de la mémoire et un décompte des lectures et des écritures. La vue de gestion dynamique indique également la connexion, l'heure de connexion, l'ordinateur hôte et la dernière fois où la session a effectué une requête SQL Server.

Avec la vue de gestion dynamique sys.dm_exec_sessions, vous ne pourrez déterminer que les sessions actives. Par conséquent, en cas de forte sollicitation du processeur, il s'agit de l'un des premiers endroits à vérifier. Analysez les sessions qui présentent une forte sollicitation du processeur. Déterminez l'application et l'utilisateur qui ont exécuté la tâche, puis approfondissez vos recherches. L'association des vues de gestion dynamique sys.dm_exec_sessions et sys.dm_exec_requests permet d'obtenir la plupart des informations disponibles via les procédures stockées sp_who et sp_who2. Si vous reliez ces données, ainsi que la fonction de gestion dynamique sys.exec_sql_text via la colonne sql_handle, vous pouvez obtenir la requête en cours d'exécution de la session. Les fragments de code de la figure 3 indiquent comment extraire ces données en même temps pour déterminer ce qui se passe actuellement sur un serveur.

Figure 3 Identification de l'activité du serveur

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec 
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

J'ai remarqué que cette instruction permet de déterminer les applications qui ont besoin d'attention. Lorsque je compare le processeur, la mémoire, les lectures, les écritures et les lectures logiques des sessions d'une application et que je constate que la ressource processeur est beaucoup plus sollicitée que les autres ressources utilisées, je porte toute mon attention sur ces instructions SQL.

Pour suivre les instructions SQL d'une application dans l'ordre chronologique, j'ai recours aux informations de suivi de SQL Server. Vous pouvez les obtenir par l'intermédiaire de l'outil Générateur de profils de SQL Server ou des informations de suivi des procédures stockées du système pour faciliter l'évaluation de l'activité en cours. (Voir l'encadré « Suivi SP » pour plus d'informations à ce sujet). Recherchez les instructions à forte sollicitation du processeur dans le Générateur de profils, ainsi que les hachages et les avertissements de tri, les échecs de cache et autres indicateurs rouges. Cela permet d'identifier des instructions SQL ou une période spécifiques ayant entraîné une forte sollicitation des ressources. Le Générateur de profils peut suivre le texte des instructions SQL, les plans d'exécution, l'utilisation du processeur, l'utilisation de la mémoire, les lectures logiques, les écritures, la mise en cache des plans de requête, les recompilations, l'éjection des plans de requête du cache, les absences dans le cache, les analyses de tables et d'index, les statistiques manquantes et bien d'autres événements.

Après avoir réuni les données collectées à partir des procédures stockées sp_trace ou du Générateur de profils SQL Server, j'utilise généralement une base de données, remplie avec les données de suivi soit après les faits, soit après avoir configuré l'écriture des informations de suivi dans la base de données. Le remplissage de la base de données a posteriori peut se faire à l'aide de la fonction système de SQL Server appelée fn_trace_getinfo. L'avantage de cette approche est que je peux interroger et trier les données de plusieurs façons pour voir quelles instructions SQL ont le plus sollicité le processeur ou ont présenté le moins de lectures, le nombre de recompilations et de nombreuses autres choses. Voici un exemple de la façon dont cette fonction est utilisée pour charger une table avec un fichier de suivi du Générateur de profils. La valeur par défaut spécifie que les fichiers de suivi seront chargés dans leur ordre de création :

SELECT * INTO trc_20070401
FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default);
GO

Conclusion

Comme vous l'avez vu, une forte sollicitation du processeur n'indique par forcément un goulot d'étranglement au niveau du processeur. Cet usage intensif du processeur peut masquer plusieurs autres goulots d'étranglement aussi bien au niveau du matériel que des logiciels. Si vous diagnostiquez une forte utilisation du processeur alors que vos autres compteurs semblent normaux, vous pouvez commencer à rechercher la cause dans le système et isoler une solution (qu'il s'agisse d'acheter des processeurs supplémentaires ou d'optimiser votre code SQL). Quoi qu'il arrive, ne renoncez pas ! Avec les conseils fournis dans cet article et un peu de pratique et de recherche, l'optimisation de l'utilisation des processeurs sous SQL Server est un plan d'exécution réalisable.

Zach Nichter est un spécialiste de SQL Server bénéficiant de plus de 10 années d’expérience. Il a occupé plusieurs postes de support SQL Server, notamment en tant qu’administrateur de base de données, chef d’équipe, responsable et consultant. Actuellement, Zach est employé par Levi Strauss & Co. comme architecte administrateur de base de données ; son travail porte principalement sur les performances, la surveillance, l’architecture et d’autres initiatives stratégiques de SQL Server. Zach tient en outre un blog vidéo à l'adresse www.sqlcatch.com.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.