Utiliser SQL Server Profiler pour créer un jeu de collections trace SQL
S’applique à :
SQL Server
Dans SQL Server, vous pouvez exploiter les fonctionnalités de trace côté serveur de SQL Server Profiler pour exporter une définition de trace que vous pouvez utiliser pour créer un jeu de collections qui utilise le type collecteur de trace SQL générique. Ce processus se décompose en deux parties :
- Créez et exportez 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 :
- Utilisez 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. (Sur 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 Connecter au serveur, sélectionnez le serveur auquel vous souhaitez vous connecter, puis sélectionnez 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 le coin 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
80la valeur, 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, pointez sur Définition de trace de script, puis sélectionnez Jeu de collections de traces 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 de fichier est identique au nom de la trace (
SPgt140).Sélectionnez OK lorsque vous recevez un message indiquant que le fichier a été correctement enregistré, 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, dans 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éé dans 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 de collections.
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
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour