INF : Résolution des problèmes de performance d'applications sous SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 224587 - Voir les produits auxquels s'applique cet article
Ancien nº de publication de cet article : F224587
Agrandir tout | Réduire tout

Sommaire

Résumé

La résolution des problèmes de performance implique l'utilisation d'une série de procédures pour isoler et déterminer la cause du ralentissement d'une application. Les causes possibles incluent le blocage, l'encombrement des ressources système, les problèmes de conception d'applications ou un ensemble particulier de requêtes ou de procédures stockées présentant de longs temps d'exécution. Cet article décrit la procédure à suivre pas à pas pour déterminer l'origine d'un problème de performance. Il renvoie également à d'autres articles de la Base de connaissances Microsoft, qui couvrent les détails de problèmes de performance spécifiques afin d'affiner la résolution des problèmes.

Des informations générales sur la résolution des problèmes de performance sont également disponibles dans le guide des résolutions des problèmes SQL Server 7.0 : Performance des requêtes accessible à l'adresse suivante : http://support.microsoft.com/?scid=ph;en-us;2862.

Plus d'informations

SQL Server Profiler est un outil puissant pour résoudre les problèmes de performance d'applications sous SQL Server 7.0. Profiler vous permet de capturer facilement tous les événements se produisant sur le serveur dans des conditions de charge types et d'obtenir des informations les concernant. Pour identifier les situations éventuelles de blocage, l'utilisation de Profiler en association avec l'Analyseur de performances Windows NT et des requêtes simples, vous fournira les informations nécessaires à la résolution de la grande majorité des problèmes de performance.

Éléments à analyser

  1. Configurez SQL Server Profiler pour capturer une trace.

    1. Ouvrez SQL Server Profiler.
    2. Dans le menu Outils, cliquez sur Options.
    3. Vérifiez que les cases d'option Toutes les catégories d'événements et Toutes les colonnes de données sont activées.
    4. Cliquez sur OK.
    5. Créez une nouvelle trace.
    6. Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Trace.
    7. Dans l'onglet Général, spécifiez un nom de trace et un fichier dans lequel capturer les données.
    8. Dans l'onglet Événements, ajoutez les types d'événements suivants à votre trace :

      Réduire ce tableauAgrandir ce tableau
      Curseurs CursorPrepare Cet événement indique qu'un curseur sur une instruction SQL a été préparé à l'aide de ODBC, OLEDB ou DB-Library.
      Erreur et avertissement Missing Column Statistics Cet événement indique que des statistiques de colonnes qui auraient pu s'avérer utiles pour l'optimiseur ne sont pas disponibles. La colonne Texte affiche la liste des colonnes auxquelles manquent des statistiques.

      Cet événement, en association avec un événement Divers : Auto-UpdateStats, indique que l'option Créer automatiquement des statistiques a été déclenchée.
      Divers Attention Cet événement indique qu'un signal d'alerte a été envoyé par un client.
      Divers Auto-UpdateStats Cet événement indique que l'option Mettre à jour automatiquement les statistiques a été déclenchée.
      Divers Exec Prepared SQL Cet événement indique que ODBC, OLE DB ou DB-Library a exécuté une ou plusieurs instructions Transact-SQL préparées.
      Divers Execution Plan Cet événement affiche une arborescence de plan de l'instruction Transact-SQL exécutée.
      Divers Prepare SQL Cet événement indique qu'une application ODBC, OLE DB ou DB-Library a préparé une ou plusieurs instructions Transact-SQL en vue de leur utilisation.
      Divers Unprepare SQL Cet événement indique qu'une application ODBC, OLE DB ou DB-Library a " dépréparé " une ou plusieurs instructions Transact-SQL en vue d'une utilisation future.
      Sessions Connect Cet événement indique qu'une nouvelle connexion a été établie.
      Sessions Disconnect Cet événement indique qu'un client s'est déconnecté.
      Sessions Existing Connection Cet événement indique qu'une connexion était établie au moment où la trace SQL Server Profiler a été démarrée.
      Procédures stockées SP: Completed Cet événement indique quand une procédure stockée a terminé son exécution.
      Procédures stockées SP: Recompile Cet événement indique qu'une procédure stockée a été recompilée au cours de son exécution.
      Procédures stockées SP: Starting Cet événement indique quand une procédure stockée a démarré son exécution.
      Procédures stockées SP: StmtCompleted Cet événement indique quand une instruction d'une procédure stockée a terminé son exécution.
      TSQL : SQL:BatchCompleted Cet événement indique la fin d'exécution d'une série d'instructions Transact-SQL. La colonne Texte indique l'instruction qui a été exécutée.
      TSQL : SQL:StmtCompleted Cet événement indique la fin d'exécution d'une instruction Transact-SQL. La colonne Texte indique l'instruction qui a été exécutée.
      TSQL : RPC:Completed Cet événement indique la fin d'exécution d'un appel de procédure distant (RPC, Remote Procedure Call).
    9. Si votre application reçoit des erreurs de dépassement de délai, si elle cesse de répondre (se bloque) ou rencontre d'autres événements qui empêchent la fin d'exécution des instructions, ajoutez également les événements suivants :

      Réduire ce tableauAgrandir ce tableau
      TSQL : SQL:BatchStarting Cet événement indique le début d'exécution d'une série d'instructions Transact-SQL. La colonne Texte indique l'instruction exécutée.
      TSQL : SQL:StmtStarting Cet événement indique le début d'exécution d'une instruction Transact-SQL. La colonne Texte indique l'instruction exécutée.
      TSQL : RPC:Starting Cet événement indique le début d'exécution d'un appel de procédure distant (RPC).
      Procédures stockées SP: StmtStarting Cet événement indique quand une instruction d'une procédure stockée débute son exécution.

      Cela vous assure de voir l'instruction qui était en cours d'exécution lors du dépassement de délai.
    10. Dans l'onglet Colonnes de données, assurez-vous que les colonnes suivantes sont incluses :

      Heure de début, Heure de fin, Code connexion, Code SP, Catégorie d'événements, Texte, Données entières, Données binaires, Durée, UCT, Lectures, Écritures, Nom d'application, Nom d'utilisateur NT, Nom d'utilisateur SQL
    Pour des informations sur l'utilisation de Profiler, consultez les guides en ligne sur SQL Server 7.0.
  2. Utilisez l'Analyseur de performances pour capturer les compteurs Windows NT et SQL Server.

    1. Démarrez l'Analyseur de performances Windows NT.
    2. Dans le menu Affichage, cliquez sur Journal.
    3. Dans le menu Options, cliquez sur Journal.
    4. Spécifiez un nom de fichier et un emplacement pour enregistrer les compteurs de performances. Vous pouvez régler la fréquence de mise à jour selon vos souhaits.
    5. Dans le menu Edition, cliquez sur Ajouter au journal.
    6. Ajoutez tous les objets (objets Windows NT et SQL Server).
    7. Pour démarrer le journal, dans le menu Options, cliquez sur Journal, puis cliquez sur le bouton Démarrer le journal.
    Pour des informations supplémentaires sur la création d'un journal de l'Analyseur de performances, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
    150934 Procédures pour créer un journal d'analyse de performances pour résoudre des problèmes NT
  3. Contrôlez les blocages éventuels.

    Pour contrôler les blocages éventuels, exécutez la procédure système stockée sp_who :

    exec sp_who
    Cette sortie contiendra une colonne blk. Recherchez les entrées différentes de zéro dans la sortie, ce qui indique un blocage en cours. Exécutez cette procédure périodiquement sur toute la période au cours de laquelle le ralentissement de performances survient.

    REMARQUE : L'exécution de la procédure système stockée sp_who vérifie simplement l'existence d'un blocage. Dans la plupart des cas, elle ne fournit pas assez d'informations pour résoudre entièrement un problème de blocage. Pour des informations supplémentaires et des scripts pour recueillir des informations en cas de blocage, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
    251004 INF : Procédures pour analyser les blocages sous SQL Server 7.0

Exécutez l'application en condition de charge type

Dans l'idéal, il est recommandé de capturer la sortie de SQL Server Profiler, de l'Analyseur de performances et de blocage pour un même intervalle de temps. Cet intervalle de temps doit couvrir une période au cours de laquelle le niveau de performances de l'application passe de satisfaisant à insuffisant. La combinaison de ces informations vous permettra de vous faire une idée claire quant au moment précis où se produit un ralentissement de performance.


Interprétez les résultats

  1. Contrôlez les blocages éventuels.

    Si la colonne blk dans la sortie sp_who est différente de zéro, cela indique un blocage sur votre système. Si des processus se bloquent l'un l'autre, les processus bloqués peuvent être ralentis dans leur exécution. Pour des informations supplémentaires sur la résolution des problèmes de blocage sous SQL Server, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
    224453 INF : Compréhension et résolution des problèmes de blocage sous SQL Server 7.0
  2. Examinez la sortie du Profiler.Un examen efficace des données produites en sortie par le Profiler est extrêmement utile pour résoudre les problèmes de performances. Il est essentiel de réaliser que vous n'avez pas à examiner tout ce qui a été capturé : soyez sélectif. Le Profiler offre des fonctionnalités vous aidant à examiner de manière efficace les données capturées. Dans l'onglet Propriétés (cliquez sur Propriétés dans le menu Fichier), Profiler vous permet de limiter les données affichées en supprimant des colonnes de données ou des événements, en groupant (triant) les données par colonne et en appliquant des filtres. Vous pouvez rechercher des valeurs spécifiques dans toute la trace ou seulement dans une colonne spécifique (dans le menu Edition, cliquez sur Rechercher). Vous pouvez aussi enregistrer les données du Profiler dans une table SQL Server (dans le menu Fichier, pointez sur Enregistrer sous, puis cliquez sur Table de trace) et exécutez des requêtes SQL.

    Veillez bien à exécuter un filtrage uniquement sur un fichier de trace précédemment enregistré. Si vous appliquez cette procédure à une trace active, vous risquez de perdre des données qui ont été capturées depuis le lancement de la trace. Enregistrez d'abord une trace active dans un fichier ou une table (dans le menu Fichier, cliquez sur Enregistrer sous), puis réouvrez-la (dans le menu Fichier, cliquez sur Ouvrir) avant de continuer. Lorsque vous travaillez sur un fichier de trace enregistré, le filtrage ne supprime pas de façon permanente les données éliminées par filtrage : il se contente de ne pas les afficher. Vous pouvez ajouter et supprimer des événements et des colonnes de données selon vos besoins pour vous aider à affiner vos recherches.

    La première étape lors de la recherche de problèmes de performances dans des fichiers de trace Profiler, consiste à déterminer où se produisent les différents types d'événements sur le serveur.

    Groupez la trace par catégorie d'événements :

    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Dans l'onglet Colonnes de données, utilisez le bouton HAUT pour déplacer Catégorie d'événements sous le titre Groupes et le bouton BAS pour supprimer toutes les autres colonnes sous le titre Groupes.
    3. Cliquez sur OK.
    Le groupement par colonne de catégorie d'événements montre quels types d'événements se produisent dans SQL Server, et à quelle fréquence. Recherchez les événements suivants dans cette colonne :

    • SP : RECOMPILE

      Cet événement indique qu'une procédure stockée a été recompilée au cours de son exécution. Un grand nombre d'événements de recompilation indique que SQL Server utilise des ressources pour la compilation de requêtes plutôt que pour l'exécution de requêtes. Pour des informations supplémentaires sur la résolution des problèmes de recompilation des procédures stockées, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
      243586 INF : Résolution des problèmes de recompilation des procédures stockées
    • Attention

      Un signal d'alerte indique qu'une requête a été annulée par un client. Cela est généralement dû à l'une des causes suivantes : l'utilisateur a annulé explicitement la requête ou a mis fin à l'application ; ou, le délai d'attente d'une requête a été dépassé. Si vous détectez des signaux d'alerte, cela peut indiquer que certaines requêtes sont ralenties dans leur exécution. Pour des informations supplémentaires et procédures à suivre pour identifier et optimiser les performances de cette requête, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
      243589 INF : Résolution des problèmes de ralentissement d'exécution des requêtes
      Pour faciliter l'identification de la requête qui a été reçue avec ce signal d'alerte, modifiez la trace de sorte qu'elle ne soit pas groupée par colonne de données, et effectue un filtre sur le code de procédure système (SPID) qui l'a reçue (dans l'onglet Filtres, définissez SPID = x). L'événement SQL : StmtStarting, SQL : BatchStarting ou SP : StmtStarting qui précède immédiatement le signal d'alerte est la requête qui a reçu un dépassement de délai ou une annulation. Vous pouvez rechercher l'événement Attention dans la colonne Catégorie d'événements pour le trouver plus facilement (dans le menu Edition, cliquez sur Rechercher).
    • PREPARE SQL et EXEC PREPARED SQL

      L'événement Prepare SQL indique qu'une application ODBC, OLE DB ou DB-Library a préparé une ou plusieurs instructions Transact-SQL en vue d'une utilisation future. L'événement Exec Prepared SQL indique que l'application a utilisé une instruction préparée existante pour exécuter une commande.

      Comparez les nombres d'occurrences de ces deux événements. Dans l'idéal, une application devrait préparer une instruction SQL une seule fois et l'exécuter plusieurs fois. Cela évite à l'optimiseur d'avoir à compiler un nouveau plan à chaque fois que cette instruction est exécutée. En conséquence, le nombre d'événements Exec Prepared SQL devrait être largement supérieur au nombre d'événements Prepare SQL. Si le nombre d'événements Prepare SQL équivaut approximativement au nombre d'événements Exec Prepared SQL, cela peut indiquer que l'application ne fait pas un usage efficace du modèle préparer/exécuter. Il n'est pas recommandé de préparer une instruction qui ne doit être exécutée qu'une seule fois. Pour plus d'informations sur la préparation d'instructions SQL, consultez la rubrique " Préparation d'instructions SQL " dans les guides en ligne sur SQL Server 7.0.

      Si le nombre d'événements Exec Prepared SQL n'est pas trois à cinq fois supérieur au nombre d'événements Prepare SQL, cela peut indiquer que l'application ne fait pas un usage efficace du modèle préparer/exécuter. Pour des informations supplémentaires, cliquez sur le numéro ci-dessous afin d'afficher l'article correspondant dans la Base de connaissances Microsoft :
      243588 INF : Résolution des problèmes de performance des requêtes ad hoc
      Dans SQL Server 2000, les allers et retours excessifs par préparer/exécuter sont éliminés, ce qui rend le rapport de 3 à 5 moins draconien. Toutefois, il peut servir de règle empirique, puisqu'il est recommandé de toujours essayer de réutiliser plus d'une fois le plan préparé.
    • Missing Column Statistics

      Cet événement indique que les informations statistiques que l'optimiseur aurait pu utiliser pour générer un plan de requête plus efficace ne sont pas disponibles. Cela indique que la requête ne dispose pas d'index utiles sur au moins l'une des tables concernées. Outre l'absence d'un index utile, SQL Server ne dispose même pas de données statistiques sur la ou les colonnes concernées pour prendre une décision fondée quant à un plan de requête. En conséquence, il se peut que le plan de requête généré ne soit pas optimal. Si vous constatez ces événements, examinez le plan de requête et d'exécution généré, et consultez l'article ci-dessous dans la Base de connaissances Microsoft quant aux procédures à suivre pour optimiser les performances de cette requête :

      243589 INF : Résolution des problèmes de ralentissement d'exécution des requêtes
      Lorsque vous examinez des événements Missing Column Statistics, concentrez-vous d'abord sur ceux qui se produisent avec des requêtes à exécution longue. Certains événements peuvent être générés et résolus automatiquement par SQL Server avec la fonctionnalité de statistiques automatiques, et peuvent ne pas nécessiter d'intervention de l'utilisateur. La meilleure stratégie en la matière consiste donc à se concentrer d'abord sur les requêtes de longue durée, comme indiqué ci-dessous, et à noter si des événements Missing Column Statistics leur sont associés.
    Si vous ne constatez aucune instance des catégories d'événements ci-dessus, l'étape suivante consiste à déterminer à quoi correspondent les dépenses de temps.

    Groupez la trace par durée :

    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Dans l'onglet Colonnes de données, utilisez le bouton HAUT pour déplacer Durée sous le titre Groupes et le bouton BAS pour supprimer toutes les autres colonnes sous le titre Groupes.
    3. Dans l'onglet Événements, supprimez tous les groupes sauf TSQL et Procédures stockées.
    4. Cliquez sur OK.
    Grâce au groupement par durée, vous pouvez voir facilement quels instructions, lots ou instructions SQL s'exécutent le plus lentement. Il est essentiel de rechercher non seulement le moment où le problème se produit, mais aussi d'obtenir une ligne de base du moment où le niveau de performances est satisfaisant à des fins de comparaison. Vous pouvez effectuer un filtre sur une heure de début pour ventiler la trace en sections pour le moment où le niveau de performances était satisfaisant, et dans une section distincte pour le moment où le niveau de performances était insuffisant. Recherchez les requêtes ayant les longues durées au moment où le niveau de performances est satisfaisant. Il est plus que probable qu'il s'agit de la cause première du problème. Si le niveau de performance global du système se dégrade, même les requêtes efficaces peuvent afficher des durées d'exécution importantes, car elles doivent attendre les ressources système.

    Si vous constatez qu'un petit nombre de requêtes présente des durées importantes, consultez l'article suivant dans la Base de connaissances Microsoft :
    243589 INF : Résolution des problèmes de ralentissement d'exécution des requêtes
    Si vous constatez que la durée des requêtes individuelles est basse, mais qu'elles sont nombreuses et que la sortie du compteur SQL Compilations/sec de l'Analyseur de performances (voir plus bas) est élevée, consultez l'article suivant dans la Base de connaissances Microsoft :
    243588 INF : Résolution des problèmes de performance liés aux requêtes ad hoc
    Examinez les colonnes de données restantes :

    Il est possible d'approfondir la nature d'un problème de performance en examinant les autres colonnes de données dans les résultats de trace. Les points suivants sont à prendre en compte :

    • Si l'utilisation de l'unité centrale (UCT) est importante, groupez par UCT pour voir quelles requêtes sont les plus grosses consommatrices de temps machine. Recherchez les termes " hash " ou " merge " dans la colonne Texte pour trouver quel plan d'exécution de requête utilise ces types de jointure. Ces plans sont plus gourmands en temps machine et en mémoire qu'une jointure par boucle imbriquée, qui tend à être gourmande en E/S.
    • Si les entrées/sorties sur disque constituent le goulot d'étranglement, groupez par lectures et écritures. Examinez les champs Nom d'application, Nom d'utilisateur NT et Nom d'utilisateur SQL pour isoler plus facilement l'origine d'une requête à exécution longue.
    • La colonne des données entières de l'événement d'erreur indiquera les messages d'erreur éventuels qui ont été retournés au client. Vous pouvez trouver le texte du message d'erreur en recherchant le numéro correspondant dans les guides en ligne sur SQL Server 7.0.
    • Le champ Code connexion vous permet de vous assurer que vous examinez les mêmes sessions pour un client spécifique. Un SPID ne peut pas apporter ce type de garantie, puisqu'un utilisateur peut s'être déconnecté et un nouvel utilisateur peut s'être connecté et recevoir le même SPID.
    L'avantage que représente l'examen de ces champs peut varier en fonction du scénario, mais il est recommandé de toujours les examiner si les champs évidents décrits plus haut ne fournissent pas de réponse.

  3. Un examen efficace des données produites en sortie par le Profiler est extrêmement utile pour résoudre les problèmes de performances. Il est essentiel de réaliser que vous n'avez pas à examiner tout ce qui a été capturé : soyez sélectif. Le Profiler offre des fonctionnalités vous aidant à examiner de manière efficace les données capturées. Dans l'onglet Examinez la sortie de l'Analyseur de performances.L'Analyseur de performances montre les goulots d'étranglement du système global. Il se peut que SQL Server et l'application aient le niveau de performances attendu, mais que l'ordinateur soit sous-alimenté ou en manque de mémoire ou d'autres ressources. Ou bien, certains compteurs peuvent indiquer des problèmes quant au mode d'exécution de l'application ou de SQL Server. Vérifiez au moins les compteurs suivants :

    • Objet : Processus
      Compteur : Processeur
      Instance : SQL Server
    • Objet : Processeur
      Compteur : Pourcentage de temps machine
      Instance : Vérifier chaque instance de processeur
    • Objet : Disque physique
      Compteur : Longueur moyenne de file d'attente de disques
      Instance : Vérifier chaque instance de disque physique
    • Objet : SQL Server : Statistiques SQL
      Compteur : Compilations SQL/sec
    Recherchez une tendance dans l'intervalle de temps au cours duquel le niveau de performances est passé de satisfaisant à insuffisant : qu'est-ce qui a augmenté en premier ? L'ordinateur est-il tributaire de l'unité centrale ou des entrées/sorties sur disque ? Ces informations, associées à la sortie du Profiler décrite plus haut, devraient vous aider à isoler les secteurs à problèmes. Des problèmes de temps machine important peuvent suggérer de grand nombres de recompilations de procédures stockées ou de compilations de requêtes ad hoc, ou une utilisation intensive de jointures par hachage et fusion. Il est recommandé de suivre les instructions des articles susmentionnés pour déterminer les procédures à suivre. D'importantes files d'attente de disques peuvent indiquer la nécessité d'augmenter la mémoire système ou d'optimiser le sous-système de disques.

Propriétés

Numéro d'article: 224587 - Dernière mise à jour: vendredi 26 octobre 2007 - Version: 5.2
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Standard
Mots-clés : 
kbproductlink kbhowto kbinfo KB224587
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com