Utiliser SQL Server Profiler pour créer un jeu d’éléments de collecte Trace SQL
S’applique à : 
SQL Server
Dans SQL Server, vous pouvez exploiter les fonctions de trace côté serveur de SQL Server Profiler pour exporter une définition de trace que vous pouvez utiliser afin de créer un jeu d’éléments de collecte qui utilise le type de collecteur Trace SQL générique. Ce processus se décompose en deux parties :
- Créer et exporter une trace SQL Server Profiler
- Écrire un nouveau script de jeu d'éléments de collecte basé sur une trace exportée.
Le scénario des procédures suivantes implique la collecte de données relatives à toute procédure stockée dont l'exécution requiert 80 millisecondes ou plus. Pour effectuer ces procédures, vous devez être en mesure :
- Utilise SQL Server Profiler pour créer et configurer une trace.
- Utilisez SQL Server Management Studio pour ouvrir, modifier et exécuter une requête.
Créer et exporter une trace SQL Server Profiler
Dans SQL Server Management Studio, ouvrez SQL Server Profiler. (Dans le menu Outils, sélectionnez SQL Server Profiler.)
Dans la boîte de dialogue Se connecter au serveur, sélectionnez Annuler.
Pour ce scénario, veillez à ce que les valeurs de durée soient configurées pour s'afficher en millisecondes (la valeur par défaut). Pour ce faire, procédez comme suit :
Dans le menu Tools (Outils), sélectionnez Options.
Dans la zone Options d'affichage , assurez-vous que la case à cocher Affiche les valeurs dans la colonne Durée en microsecondes est désactivée.
Sélectionnez OK pour fermer la boîte de dialogue Options générales.
Dans le menu Fichier, sélectionnez Nouvelle trace.
Dans la boîte de dialogue Se connecter au serveur, sélectionnez le serveur auquel vous souhaitez vous connecter, puis sélectionnez Se connecter.
La boîte de dialogue Propriétés de la trace s'affiche.
Sous l’onglet Général, vous pouvez effectuer les opérations suivantes :
Dans la zone Nom de la trace , tapez le nom que vous souhaitez utiliser pour la trace. Pour cet exemple, le nom de la trace est
SPgt140.Dans la liste Utiliser le modèle, sélectionnez le modèle à utiliser pour la trace. Pour cet exemple, sélectionnez TSQL_SPs.
Sous l'onglet Sélection des événements , procédez comme suit :
Identifiez les événements à utiliser pour la trace. Pour cet exemple, désactivez toutes les cases à cocher de la colonne Événements , sauf ExistingConnection et SP:Completed.
Dans l’angle inférieur droit, cochez la case Afficher toutes les colonnes .
Sélectionnez la ligne SP:Completed.
Faites défiler la ligne jusqu'à la colonne Durée , puis activez la case à cocher Durée .
Dans l’angle inférieur droit, sélectionnez Filtres de colonnes pour ouvrir la boîte de dialogue Modifier le filtre. Dans la boîte de dialogue Modifier le filtre , procédez comme suit :
Dans la liste de filtres, sélectionnez Durée.
Dans la fenêtre d’opérateur booléen, développez le nœud Supérieur ou égal à, tapez la valeur
80, puis sélectionnez OK.
Sélectionnez Exécuter pour démarrer la trace.
Dans la barre d’outils, sélectionnez Arrêter la trace sélectionnée ou Suspendre la trace sélectionnée.
Dans le menu Fichier, pointez sur Exporter, sur Générer un script de la définition de la trace, puis sélectionnez Pour le jeu d’éléments de collecte Trace SQL.
Dans la boîte de dialogue Enregistrer sous , tapez le nom que vous souhaitez utiliser pour la définition de la trace dans la zone Nom de fichier , puis enregistrez-le à l'emplacement de votre choix. Pour cet exemple, le nom du fichier est identique au nom de la trace (
SPgt140).Sélectionnez OK lorsqu’un message indiquant que le fichier a bien été enregistré s’affiche, puis fermez SQL Server Profiler.
Générer un nouveau jeu d'éléments de collecte à partir d'une trace SQL Server Profiler
Dans SQL Server Management Studio, ouvrez le menu Fichier, pointez sur Ouvrir, puis sélectionnez Fichier.
Dans la boîte de dialogue Ouvrir un fichier, recherchez et ouvrez le fichier que vous avez créé au cours de la procédure précédente (
SPgt140).Les informations de trace que vous avez enregistrées sont ouvertes dans une fenêtre de requête et fusionnées dans un script que vous pouvez exécuter pour créer le jeu d'éléments de collecte.
Faites défiler le script et effectuez les remplacements suivants, notés dans le texte de commentaire du script :
Remplacez SQLTrace Collection Set Name Here par le nom que vous souhaitez utiliser pour le jeu d'éléments de collecte. Pour cet exemple, nommez le jeu d’éléments de collecte SPROC_CollectionSet.
Remplacez SQLTrace Collection Item Name Here par le nom que vous souhaitez utiliser pour l'élément de collecte. Pour cet exemple, nommez l’élément de collecte SPROC_Collection_Item.
Sélectionnez Exécuter pour exécuter la requête et créer le jeu d’éléments de collecte.
Dans l'Explorateur d'objets, vérifiez que le jeu d'éléments de collecte a été créé. Pour ce faire, procédez comme suit :
Cliquez avec le bouton droit sur Gestion, puis sélectionnez Actualiser.
Développez Gestion, puis Collecte de données.
Le jeu d’éléments de collecte SPROC_CollectionSet apparaît au même niveau que le nœud Jeux d’éléments de collecte de données système . Par défaut, le jeu d'éléments de collecte est désactivé.
Utilisez l'Explorateur d'objets pour modifier les propriétés de SPROC_CollectionSet, tel que le mode de collecte et la planification de téléchargement. Procédez de la même manière que pour les jeux d'éléments de collecte de données système fournis avec le collecteur de données.
Exemples
L'exemple de code suivant est le script final obtenu après l'exécution des étapes documentées dans les procédures précédentes.
/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2022 12:55:31 AM
/*************************************************************/
USE msdb;
GO
BEGIN TRANSACTION
BEGIN TRY
-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the
-- *** following script with the name you want
-- *** to use for the collection set.
-- ***
DECLARE @collection_set_id INT;
EXEC [dbo].[sp_syscollector_create_collection_set] @name = N'SPROC_CollectionSet',
@schedule_name = N'CollectorSchedule_Every_15min',
@collection_mode = 0, -- cached mode needed for Trace collections
@logging_level = 0, -- minimum logging
@days_until_expiration = 5,
@description = N'Collection set generated by SQL Server Profiler',
@collection_set_id = @collection_set_id OUTPUT;
SELECT @collection_set_id;
-- Define input and output variables for the collection item.
DECLARE @trace_definition XML;
DECLARE @collection_item_id INT;
-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(XML, N'<ns:SqlTraceCollector xmlns:ns"DataCollectorType" use_default="0">
<Events>
<EventType name="Sessions">
<Event id="17" name="ExistingConnection" columnslist="1,2,14,26,3,35,12" />
</EventType>
<EventType name="Stored Procedures">
<Event id="43" name="SP:Completed" columnslist="1,2,26,34,3,35,12,13,14,22" />
</EventType>
</Events>
<Filters>
<Filter columnid="13" columnname="Duration" logical_operator="AND" comparison_operator="GE" value="80000L" />
</Filters>
</ns:SqlTraceCollector>
');
-- Retrieve the collector type GUID for the trace collector type.
DECLARE @collector_type_GUID UNIQUEIDENTIFIER;
SELECT @collector_type_GUID = collector_type_uid
FROM [dbo].[syscollector_collector_types]
WHERE name = N'Generic SQL Trace Collector Type';
-- Create the trace collection item.
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in
-- *** the following script with the name you want to
-- *** use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item] @collection_set_id = @collection_set_id,
@collector_type_uid = @collector_type_GUID,
@name = N'SPROC_Collection_Item',
@frequency = 900, -- specified the frequency for checking to see if trace is still running
@parameters = @trace_definition,
@collection_item_id = @collection_item_id OUTPUT;
SELECT @collection_item_id;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (
14684,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine,
@ErrorMessage
);
END CATCH;
GO