SQLDIAG (épisode 1)

Vendredi, février 5, 2010
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

Début d’une assez longue série de posts sur l’utilitaire SQLDIAG et ses petits camarades de jeu PSSDIAG, SQLNexus et autres RML Utilities…

SQLDIAG est un utilitaire en mode ligne de commandes qui est livré avec SQL Server depuis plus de 10 ans maintenant. Il est bien caché, peu de gens l’utilisent mais il peut être d’une puissance redoutable. Il est capable de lancer en même temps:

  • Une collecte perfmon.
  • Une trace type SQL Trace.
  • Une collecte des journaux d’évènements.
  • Un inventaire complet  de la machine basé sur msinfo32.
  • Un inventaire complet de l’instance.

Il permettra même de corréler des traces de compteurs perfmon et le traçage de sessions SQL dans Profiler. Il se base sur un fichier de configuration au format XML. Il en existe un par défaut (SQLDiag.xml) qui se trouve sous ~Tools\Binn, mais il est très exhaustif  et trace tous les compteurs et évènements disponibles. L’inconvénient de l’utiliser est qu’il créé des traces pénalisantes et très encombrantes, plusieurs dizaines de Gb de fichiers textes, blg, trc, etc… Donc on a intérêt à se créer son petit fichier XML pour ne capturer que ce dont on aura besoin.

SQLDiag.xml

Dans ce premier épisode, nous allons donc parler de la structure de ce fichier XML. Elle ressemble vu d’en haut à ceci:

<Collection>
<Machines>
        <Machine name=".">
                <MachineCollectors>
                        <EventlogCollector />
                        <PerfmonCollector />
                </MachineCollectors>
                <Instances>
                       <Instance>
                              <Collectors>
                                    <SqldiagCollector />
                                    <BlockingCollector />
                                    <ProfilerCollector />
                                    <CustomDiagnostics />
                             </Collectors>
                     </Instance>
              </Instances>
      </Machine>
</Machines>
</Collection>

Chaque balise %Collector délimite la zone de paramétrage de chaque collecteur de trace. Pour activer / désactiver un collecteur, passer son attribut enabled à true ou false:

  • EventlogCollector pour collecter les journaux d’évènements.
  • PerfmonCollector pour lancer la trace perfmon.
  • SQLdiagCollector pour récupérer toute la configuration de l’instance: paramètres, contenu de tous les errorlogs, connexions, verrous, liste des bases et options, xp_msver, etc…
  • BlockingCollector pour l’évènement BlockedProcessReport de SQL Trace, rapporte les processus bloqués si le paramètre ‘blocked process threshold (s)‘ est configuré.
  • ProfilerCollector pour la trace SQL Trace complète avec les évènements que l’on aura indiqué.
  • CustomDiagnostics pour lancer des exécutables supplémentaires, en l’occurence msinfo32 dans notre cas, et récupérer la configuration complète de la machine.

PerfmonCollector

C’est le collecteur de compteurs perfmon. Ses éléments fils sont <PerfmonObject /> qui désigne la famille de compteurs comme « PhysicalDisk(*) » par exemple, et <PerfmonCounter /> qui désigne le compteur associé comme « \Avg. Disk sec/Read« . On aura besoin d’activer chaque compteur en passant son attribut enabled à true. Une zone PerfmonCollector peut ressembler à ceci, avec le nom des compteurs en vert:

<PerfmonCollector enabled="true" pollinginterval="30" maxfilesize="256">
    <PerfmonCounters>
        <PerfmonObject name="\PhysicalDisk(*)" enabled="true">
             <PerfmonCounter name="\Avg. Disk sec/Read" enabled="true" />
             <PerfmonCounter name="\Avg. Disk sec/Write" enabled="true" />
             <PerfmonCounter name="\Disk Read Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Disk Write Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Avg. Disk Bytes/Transfer" enabled="true" />
        </PerfmonObject>
        <PerfmonObject name="\Processor(*)" enabled="true">
            <PerfmonCounter name="\% Processor Time" enabled="true" />
            <PerfmonCounter name="\% User Time" enabled="true" />
            <PerfmonCounter name="\% Privileged Time" enabled="true" />
        </PerfmonObject>
    </PerfmonCounters>
</PerfmonCollector>

L’attribut pollinginterval indique la fréquence d’échantillonnage des compteurs. Le fichier de sortie de ce collecteur sera un fichier BLG intitulé tout simplement SQLDIAG.blg. Il pourra être corrélé plus tard avec la trace qui sera générée par le collecteur ProfilerCollector.

SQLdiagCollector

C’est le collecteur de données de l’instance. Il n’est pas paramétrable est composé d’une seule ligne:

<SqldiagCollector enabled="true" startup="false" shutdown="true" /> 

On l’activera donc en passant son attribut enabled à true là encore. Les paramètres startup et shutdown permettent d’indiquer si la collecte se fait au démarrage ou à l’arrêt de la trace SQLDIAG.

BlockingCollector:

C’est le collecteur des processus bloqués en attente de libération de ressources (verrous). Lui aussi n’est constitué  que d’un seul élément:

<BlockingCollector enabled="true" pollinginterval="5" maxfilesize="350" /> 

L’attribut pollinginterval désigne la fréquence d’échantillonnage  de l’évènement, et maxfilesize la taille maximale du fichier en sortie. Ce collecteur se base sur le paramètre d’instance ‘blocked process threshold‘ qui définit la durée en secondes au delà de laquelle on considère que le blocage est anormalement long. Il faudra définir ce paramètre car sa valeur par défaut est à zéro, ce qui signifie qu’il n’est pas activé. Par exemple pour mettre le seuil à 20 secondes:

sp_configure 'blocked process threshold', 20
go
reconfigure
go

Ensuite, le collecteur va créer une trace SQL Trace avec le seul évènement ‘Blocked Process Report‘, et capturer les sessions qui auront été bloquées pendant plus de 20 secondes. Le fichier de sortie de ce collecteur est un fichier trc classique nommé <INSTANCE>_SQLDIAG_sp_trace_blk.

ProfilerCollector:

Ensuite, ProfilerCollector est le collecteur de trace SQL Trace classique. Comme pour PerfmonCollector, il va disposer d’éléments fils comme <EventType /> et <Event /> dans lesquels on va bien entendu retrouver nos classes d’évènements:

<ProfilerCollector enabled="true" template="_GeneralPerformance100.xml" pollinginterval="5" maxfilesize="350">
     <Events>
         <EventType name="Stored Procedures">
             <Event id="10" name="RPC:Completed" enabled="true"/>
             <Event id="11" name="RPC:Starting" enabled="true" />
             <Event id="43" name="SP:Completed" enabled="true" />
             <Event id="42" name="SP:Starting" enabled="true" />
             <Event id="45" name="SP:StmtCompleted" enabled="true" />
             <Event id="44" name="SP:StmtStarting" enabled="true" />
        </EventType>
       <EventType name="TSQL">
             <Event id="40" name="SQL:StmtStarting" enabled="true"/>
             <Event id="41" name="SQL:StmtCompleted" enabled="true" />
       </EventType>
     </Events>
 </ProfilerCollector>

Le fichier de sortie de ce collecteur sera aussi un fichier trace classique nommé <INSTANCE>_SQLDIAG_sp_trace.

CustomDiagnostics

Enfin, CustomDiagnostics permet de pousser encore plus loin la personnalisation du rapport, en nous permettant d’utiliser nos propres outils de collecte. L’outil qui est utilisé par défaut est msinfo32 qui est utilisé pour afficher de manière graphique (Démarrer -> Exécuter -> msinfo32) ou en ligne de commande toute la configuration de la machine hôte:

<CustomDiagnostics>
 <CustomGroup name="msinfo" enabled="true" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get MSINFO32" type="Utility" point="Startup" wait="OnlyOnShutdown" cmd="start /B /WAIT MSInfo32.exe /computer %server% /report
         &quot;%output_path%%server%_MSINFO32.TXT&quot; /categories +SystemSummary+ResourcesConflicts+ResourcesIRQS+ComponentsNetwork+ComponentsStorage+ComponentsProblemDevices+SWEnvEnvVars+
         SWEnvNetConn+SWEnvServices+SWEnvProgramGroup+SWEnvStartupPrograms" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get default traces" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\log*.trc&quot; &quot;%output_path%&quot;" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get SQLDumper log" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\SQLDUMPER_ERRORLOG.log&quot;
          &quot;%output_path%%server%_%instance%_SQLDUMPER_ERRORLOG.log&quot;" />
 </CustomDiagnostics>

Mais en plus de récupérer le contenu d’une trace msinfo32, il peut également récupérer le contenu des traces SQL Server par défaut *, et les stacktraces générées par SQL Server lorsqu’il rencontre une erreur fatale sur un module. Il va copier le contenu de ces traces dans le répertoire passé à l’exécution de SQLDIAG.

Exécution d’une trace

Une fois le fichier XML défini, lancer une trace devient un jeu d’enfant:

$ sqldiag /I MyXMLfile.xml /O e:/SQLDIAG/TRACE1
2010/02/04 18:58:10.28 SQLDIAG Collector version
2010/02/04 18:58:10.29 SQLDIAG

IMPORTANT:  Please wait until you see "Collection started" before attempting to reproduce your issue

2010/02/04 18:58:10.29 SQLDIAG Output path: e:\SQLDIAG\TRACE1\
2010/02/04 18:58:10.33 SQLDIAG Collecting from 1 logical machine(s)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 MS2K8-WIN2008-1\* SQL Server version: 10
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Machine name: MS2K8-WIN2008-1 (this machine)
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Target machine is not a cluster
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Instance: (Default) (32-bit)
2010/02/04 18:58:12.40 SQLDIAG Initialization starting...
2010/02/04 18:58:13.98 MS2K8-WIN2008-1\* Starting Profiler trace
2010/02/04 18:58:14.94 MS2K8-WIN2008-1\* Starting Blocking script
2010/02/04 18:58:16.88 MS2K8-WIN2008-1\* MsInfo: Get MSINFO32
2010/02/04 18:58:17.26 MS2K8-WIN2008-1\* MsInfo: Get default traces
2010/02/04 18:58:17.47 MS2K8-WIN2008-1\* MsInfo: Get SQLDumper log
2010/02/04 18:58:17.71 MS2K8-WIN2008-1\* Adding Perfmon counters...
2010/02/04 18:58:20.73 MS2K8-WIN2008-1\* Starting Perfmon
2010/02/04 18:58:20.73 SQLDIAG Initialization complete
2010/02/04 18:58:21.53 MS2K8-WIN2008-1\* Perfmon started
2010/02/04 18:58:21.58 MS2K8-WIN2008-1\* Collecting diagnostic data

2010/02/04 18:58:22.28 SQLDIAG Collection started.  Press Ctrl+C to stop.

La trace est lancée, un simple Ctrl-C suffira à la stopper. Les commutateurs /I et /O permettent d’indiquer le fichier de configuration XML choisi et le répertoire où va se loger le contenu de la trace complète.

Il existe aussi des commutateurs pour indiquer à SQLDIAG de programmer son exécution à des heures déterminées. Il est même possible de l’enregistrer en tant que service dans windows.

Dans le prochain post sur SQLDIAG, on verra comment dépouiller les fichiers de résultat.

A+ David B.

(avec la collaboration de Martial LUCAS. )

* Depuis la version SQL Server 2005, un paramètre instance ‘default trace enabled’ avec une valeur de 1 (valeur par défaut) génère une trace systématiquement au démarrage de l’instance. Cette trace contient les classes évènements Database, Errors and Warnings, Full text, Objects, Performance, Server et Security Audit. Chaque trace vient se loger dans le répertoire des ERRORLOG par défaut.


Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: , , , ,

11 Responses to “SQLDIAG (épisode 1)”

  1. vincent

    Article très intéressant. J’attends impatiemment le prochain sur l’analyse des resultats.

    #318
  2. ZINZINDOHOUE ETIENNE

    Article intéressant.
    Mais je constate que avec SQL SERVER 2008, les choses ne sont si simple !
    La preuve :
    Dans un premier temps, j’ai testé le SQLDIAG avec SQL SERVER 2005 SP2 et ça marche nickel ! j’arrive donc à mettre en perspective les infos du performance monotor (fichier .BLG) et les traces SQLTRACE (fichier …__sp_trace.trc). jusque là aucun problème.
    J’ai donc copié ces 2 fichiers pour les exécuter sur mon instance SQLSERVER 2008 SP0. J’ai pris soins de modifier dans mon fichier de config (mySQLDIAG.xml) la balise
    pour prendre en compte la version 10 (SQLSERVER 2008) au lieu de 9 (SQLSERVER2005) Et là j’ai cette erreur :

    2010/03/29 16:50:33.18 TESTBED_ZINZINDOHOUE\* Error updating perfmon log . Function result: -2147481643. Message: Aucune donnée à renvoyer.

    Auriez-vous une idée sur cette erreur ?

    D’avance merci.

    #328
  3. ZINZINDOHOUE ETIENNE

    Une petite précision :
    L’erreur s’affiche lors du lancement de la commande :
    sqldiag /I E:\traceSQLDIAG\mySQLDIAG.xml /O E:\TraceSQLDIAG\testToday

    Erreur :
    2010/03/29 16:50:33.18 TESTBED_ZINZINDOHOUE\* Error updating perfmon log . Function result: -2147481643. Message: Aucune donnée à renvoyer.

    Auriez-vous une idée sur cette erreur ?

    #329
  4. Hello Etienne,

    Comme indiqué sur ton post developpez (http://www.developpez.net/forums/d899497/bases-donnees/ms-sql-server/administration/sqlserver-2008-sqldiag/)

    J’arrive à reproduire ton problème et en attachant windbg à l’image de sqldiag, je vois un SHARED VIOLATION au moment où sqldiag réouvre le fichier BLG pour stocker la trace perfmon. Le test a été réalisé en 9.00.3042, je vais passer en SP3+CU8 d’ici demain pour valider le problème, et ensuite j’ouvrirai un call sur connect.

    En attendant, tu peux ajouter un PerfmonObject bidon avant ou après les objets PhysicalDisk et Processor. Je ne peux pas poster des balises XML dans les commentaires, mais l’exemple se trouve en réponse au post sur developpez.

    Il semble que sqldiag attende au moins un compteur SQLServer. A creuser…

    A+ David B.

    #335
  5. ZINZINDOHOUE ETIENNE

    David,
    Avec SQL SERVER 2005 SP2 ça marche Nickel chez moi. Il faut simplement remplacer dans le fichier mySQLDIAG.xml 10 par 9 dans la balise INSTANCE name= »* » windowsauth= »true » ssver= »10″ user= »"

    Par ailleurs avec SQLSERVER 2008 (version 10).Il me semble que l’anomalie provient des compteurs [B]PerfmonCounters[/B].
    car lorsque [B]TOUS [/B]les [B]PerfmonCounters [/B]sont Disenabled c’est à dire Enabled=false
    ou simplement [B]PerfmonCollector enabled=false[/B] SQLDIAG se lance sans erreur mais rien n’est collecté du côté des compteurs Perfmon. Je vais

    #337
  6. Hello,

    En fait les compteurs type ‘\PhysicalDisk(*)’ n’existent pas sur ta machine car ton windows est probablement en français. Si tu remplace par ‘\Disque physique(*)’, ça devrait mieux fonctionner.

    Tu peux trouver cette information dans la log de SQLDIAG (##SQLDIAG.log):

    2010/03/31 11:07:48.00 MS2K-2K5-WIN200\* Could not add counter: \\MS2K-2K5-WIN200\PhysicalDisk(*)\Current Disk Queue Length . Function result: -1073738824. Message: L’objet spécifié n’a pas été trouvé sur le système.

    Pour retrouver le nom exact du compteur, utilise typeperf -qx | findstr « physique » | more

    Merci encore pour tes commentaires,

    J’ai également posté la réponse sur developpez. A+ David B.

    #339
  7. ZINZINDOHOUE ETIENNE

    Hello David,
    Il me semble que l’explication de l’erreur renvoyée n’est pas uniquement à ce niveau là.
    Dans mon fichier de config qui marche d’ailleurs très bien, j’ai la balise
    «  »
    Et pourtant je suis sous Windows XP Pro (FR) SP3
    Je continue de chercher l’explication de cette erreur ! C’est vraiment étonnant !

    #341
  8. Salut David,
    je reviens sur ce sujet juste pour apporter une petite précision (petite mais ça m’a fait perdre des heures ;-) ]. En fait lorsqu’on a plusieurs instances (instance par défaut + instances nommées) sur un serveur SQL et qu’on veut collecter uniquement les infos de l’instance par défaut par exemple, il faut modifier la balise Instance du fichier de configuration de sqldiag.
    Dans mon cas voici ce que j’avais initialement.

    le name= »* » signifie toutes les instances du serveur.
    et comme je veux collecter uniquement les infos de l’instance par défaut, je remplace la balise précédente par celle-ci

    Et tout marche comme je le souhaite.

    Mais je pense quand qu’il ya une petite confusion à propos du nom de cette balise.

    Pourquoi ? parce que le nom de l’instance par défaut n’est pas MSSQLSERVER !
    MSSQLSERVER c’est le nom du service du moteur SQL de l’instance par défaut.
    le nom de l’instance s’obtient en faisant EXEC sp_helpserver
    Et le résultat de EXEC sp_helpserver n’est pas MSSQLSERVER !

    Le terme juste pour cette balise devrait être :

    Qu’en penses-tu ?

    A+

    Etienne

    #1212
  9. Aaaaaaah, mes balises xml ne s’affiche pas sur le blog ?!! comment faut-il faire pour faire apparaître les balises ?
    Bon en attendant, je reprend ce que j’ai écrit cette fois-ci sans les balises pour être un peu plus claire.
    Donc je disais que :
    je reviens sur ce sujet juste pour apporter une petite précision (petite mais ça m’a fait perdre des heures ]. En fait lorsqu’on a plusieurs instances (instance par défaut + instances nommées) sur un serveur SQL et qu’on veut collecter uniquement les infos de l’instance par défaut par exemple, il faut modifier la balise Instance du fichier de configuration de sqldiag.
    Dans mon cas voici ce que j’avais initialement.

    Instance name= »* » windowsauth= »true » ssver= »9″ user= »"

    le name= « * » signifie toutes les instances du serveur.
    et comme je veux collecter uniquement les infos de l’instance par défaut, je remplace la balise précédente par celle-ci

    Instance name= »MSSQLSERVER » windowsauth= »true » ssver= »9″ user= »"

    Et tout marche comme je le souhaite.

    Mais je pense quand qu’il ya une petite confusion à propos du nom de cette balise.
    Pourquoi ? parce que le nom de l’instance par défaut n’est pas MSSQLSERVER !
    MSSQLSERVER c’est le nom du service du moteur SQL de l’instance par défaut.
    le nom de l’instance s’obtient en faisant EXEC sp_helpserver
    Et le résultat de EXEC sp_helpserver n’est pas MSSQLSERVER !

    Le terme juste pour cette balise devrait être servicename :
    Instance servicename= »MSSQLSERVER » windowsauth= »true » ssver= »9″ user= »">

    Qu’en penses-tu ?

    #1213
  10. Attention les balises ne sont pas interprétées par WP. Je ne vois pas où est la confusion celà dit. Merci pour ton commentaire.

    David B.

    #1214
  11. la confusion c’est que le nom d’une instance (instance name) est différent du nom du service de l’instance (instance servicename).
    or le fichier de configuration de SQLDIAG attend le « instance servicename » mais demande l’instance name !
    Exemple : le nom de mon instance par défaut est DBZINZIN et le nom du service est MSSQLSERVER.

    Donc logiquement dans le fichier de config SQLDIAG je dois mettre

    Instance name= »DBZINZIN» windowsauth= »true » ssver= »9″ user= » »

    ce qui renvoie une erreur ! c’est pourquoi je pense que au lieu de Instance name (comme c’est écrit dans le fichier de conf de SQLDIAG) c’est plus logique de parler de Instance servicename

    A+

    #1215

Leave a Reply