Type de collecteur SQL Trace générique
Le type de collecteur SQL Trace générique utilise SQL Trace pour surveiller le moteur relationnel SQL Server. Les données de trace peuvent provenir d'une trace par défaut ou d'une ou de plusieurs traces personnalisées. Ce type de collecteur est enregistré dans la vue core.supported_collector_types.
La trace par défaut, contrôlée par un paramètre à l'échelle du serveur, s'exécute continuellement sur le serveur et capture des événements généraux dignes d'intérêt. Ceux-ci ne sont pas liés à l'exécution de lots individuels. Il s'agit d'une trace à faible impact. Les traces personnalisées peuvent collecter tout événement et peuvent avoir un impact élevé ou faible en fonction des événements sélectionnés et de l'activité du serveur au moment où la trace s'exécute. Dans la plupart des cas, les traces personnalisées ne s'exécutent pas de façon continue.
Le type de collecteur SQL Trace générique exécute une trace côté serveur qui stocke des données dans un fichier ou dans un ensemble de fichiers. Les données de trace sont obtenues à partir de fichiers de trace à l'aide de la fonction système fn_trace_gettable(). S'il est configuré pour cela, le collecteur traite les données, puis les télécharge vers l'entrepôt de données de gestion.
Le type de collecteur SQL Trace générique est configuré pour supprimer les fichiers inutilisés et conserver une quantité définie d'espace pour les données de trace stockées.
Schéma d'entrée SQL Trace générique
Le type de collecteur SQL Trace générique utilise le schéma ci-dessous pour les paramètres d'entrée.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
<xs:element name="SqlTraceCollector">
<xs:complexType>
<xs:sequence>
<xs:element name="Events">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="EventType">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Event">
<xs:complexType>
<xs:attribute name="id" type="xs:unsignedByte" use="required" />
<xs:attribute name="name" type="xs:string" use="required" />
<xs:attribute name="columnslist" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="optional" />
<xs:attribute name="name" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Filters">
<xs:complexType>
<xs:sequence>
<xs:element name="Filter" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="columnid" type="xs:unsignedByte" use="required" />
<xs:attribute name="columnname" type="xs:string" use="required" />
<xs:attribute name="logical_operator" type="xs:string" use="required" />
<xs:attribute name="comparison_operator" type="xs:string" use="required" />
<xs:attribute name="value" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="use_default" type="xs:boolean" />
</xs:complexType>
</xs:element>
</xs:schema>
Comme le montre le schéma, le type de collecteur contient des éléments clés, des paramètres de procédure stockée et des colonnes spéciales.
Éléments clés
La définition de trace d'entrée contient une liste d'événements et une liste de filtres qui définissent la trace.
Les événements sont groupés dans un nœud EventType, qui correspond à la catégorie d'événement dans SQL Server Profiler.
Pour le nœud EventType, l'attribut ID doit correspondre à la valeur issue de la vue système sys.trace_category. L'attribut name est facultatif.
Pour le nœud Event, l'attribut ID doit correspondre à la valeur issue de la vue système sys.trace_events. L'attribut name est facultatif.
Les ID sont utilisés lors de la génération du script de démarrage de trace. Les noms sont utilisés à des fins de lisibilité et pour le rendu de la définition d'entrée dans l'interface utilisateur.
Le nœud Event contient les attributs suivants :
ID, name. Expliqué précédemment.
columnslist. Liste séparée par des virgules d'ID de colonne à sélectionner pour l'événement. Si columnslist n'est pas spécifié, toutes les colonnes seront incluses pour cet événement.
Le nœud Filter définit un filtre appliqué à la trace. Les attributs ont les significations suivantes :
columnid. ID d'une colonne à laquelle le filtre s'applique.
columnname. Nom de la colonne identifiée par columnid. Il est utilisé pour le rendu.
logical_operator. Opérateur à appliquer entre plusieurs filtres sur la même colonne. Les valeurs autorisées sont AND et OR.
comparison_operator. Opérateur à appliquer entre la colonne et la valeur de filtre. Les valeurs autorisées sont : EQ, NE, GT, GE, LT, LE, LIKE et NOTLIKE.
value. Valeur à utiliser pour la comparaison. Notez que la valeur du filtre et l'opérateur de comparaison doivent correspondre au type de la colonne à laquelle le filtre est appliqué. Par exemple, si le type de colonne est string, seuls les opérateurs "LIKE" et "NOTLIKE" peuvent être utilisés, et seules des valeurs de chaîne sont acceptées comme valeur de filtre.
Paramètres de procédure stockée
Les paramètres suivants de la procédure stockée sp_trace_create sont définis en fonction du jeu de collections ou des options du collecteur.
@options. La trace est toujours démarrée avec l'option de substitution définie (TRACE_FILE_ROLLOVER).
@tracefile. L'emplacement des fichiers de trace est déterminé par la variable CollectorTempDir. Le nom du fichier de trace est généré comme une concaténation de : "DataCollector_" + nom_instance + UID_jeu_éléments_collecte + ID_élément_collecte + ".trc".
@maxfilesize. Sa valeur est toujours de 5 mégaoctets (Mo).
@stoptime. Non utilisé.
@filecount. Défini pour s'ajuster au stockage temporaire maximal autorisé par jeu d'éléments de collecte (en Mo). @filecount = limite de stockage/5.
Colonnes spéciales
Des colonnes supplémentaires sont fournies pour chaque événement, même si elles ne sont pas présélectionnées dans les paramètres d'entrée pour cet événement. Ces colonnes sont :
StartTime
EndTime
EventSequence
SPID
Les colonnes précédentes identifient l'origine des événements et activent la corrélation des événements.
L'exemple de code suivant illustre l'utilisation du schéma d'entrée utilisé par le type de collecteur SQL Trace générique.
<?xml version="1.0" encoding="utf-8"?>
<ns:SqlTraceCollector xmlns:ns="DataCollectorType" normalize_sql="0" normalize_plans="0" normalize_procedures="0" normalize_connections="0"
normalize_default="1">
<Events>
<EventType id ="6" name="Performance">
<Event id="58" name="Auto Stats"/>
<Event id="165" name="Performance statistics"/>
<Event id="146" name="Showplan XML Statistics Profile"/>
</EventType>
<EventType id="13" name="TSQL">
<Event id="12" name="SQL:BatchCompleted" columnslist="1, 3, 9, 10, 13, 16, 17, 18"/>
<Event id="13" name="SQL:BatchStarting"/>
<Event id="41" name="SQL:StmtCompleted"/>
<Event id="166" name="SQL:StmtRecompile"/>
</EventType>
<EventType id="20" name="CLR">
<Event id="196" name="Assembly Load"/>
</EventType>
<EventType id="1" name="Cursors">
<Event id="53" name="CursorOpen"/>
<Event id="75" name="CursorRecompile"/>
<Event id="76" name="CursorImplicitConversion"/>
<Event id="78" name="CursorClose"/>
</EventType>
</Events>
<Filters>
<Filter columnid="13" columnname="Duration" logical_operator="AND" comparison_operator="GE" value="1000L"/>
<Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="LIKE" value="Data Collector"/>
<Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="EQ" value="NULL"/>
<Filter columnid="18" columnname="CPU" logical_operator="AND" comparison_operator="EQ" value="20"/>
<Filter columnid="14" columnname="StartTime" logical_operator="AND" comparison_operator="GT" value="2007-02-09 13:40:00"/>
</Filters>
</ns:SqlTraceCollector>
Traitement et résultat
Cette version du type de collecteur SQL Trace générique prend en charge une charge des données de trace complète qui traite les événements capturés dans la trace par défaut du serveur.
Charge des données de trace complète
Avec ce type de charge de données, les données de trace sont chargées sans traitement dans une table individuelle qui contient toutes les colonnes de trace possibles. Les données provenant de plusieurs traces peuvent être chargées dans la même table, ce qui simplifie la fusion des données. Outre les données de trace, un snapshot_id est ajouté à chaque ligne, ce qui permet d'identifier la source des données de trace et le moment de la trace.
Les avantages fournis par une charge des données de trace complète sont les suivants :
Il existe une manière simple d'obtenir les données de trace du serveur dans une base de données où elles peuvent facilement être interrogées et faire l'objet d'un traitement supplémentaire sans avoir à ouvrir la trace dans SQL Server Profiler.
Les données provenant de plusieurs traces peuvent être fusionnées et corrélées ensemble.
Aucune perte de données ne se produit par rapport à la trace d'origine. Tout ce qui a été capturé est conservé.
Des outils existants, tels que SQL Server Profiler, peuvent être utilisés pour parcourir les données.
Schéma de destination
Le schéma de destination est défini sous la forme d'une table qui capture des détails sur les traces stockées dans l'entrepôt de données de gestion et d'une table utilisée pour stocker tous les événements de trace provenant des traces. Les données de trace sont stockées dans les tables de l'entrepôt de données de gestion suivantes :
snapshots.trace_info. Cette table contient des informations sur toutes les traces qui ont été téléchargées vers l'instance de l'entrepôt de données.
snapshots.trace_data. Cette table contient les données capturées par toutes les traces. Elle définit une colonne pour chaque colonne de trace possible. Stocker les données de trace de cette façon permet au collecteur de données d'insérer des données dans la table sous la même forme que lorsqu'elles proviennent de la fonction système fn_trace_gettable(). Cela permet également à la table d'être chargée directement dans SQL Server Profiler.
Pour plus d'informations sur ces tables, consultez Entrepôt de données de gestion.