Verwenden von SQL Server Profiler zum Erstellen eines SQL Trace-Sammlungssatzes

  • Artikel

Gilt für:SQL Server

In SQL Server können Sie die serverseitigen Ablaufverfolgungsfunktionen von SQL Server Profiler nutzen, um eine Ablaufverfolgungsdefinition zu exportieren, die Sie verwenden können, um einen Sammlungssatz zu erstellen, der den generischen SQL Trace-Sammeltyp verwendet. Dieser Vorgang besteht aus zwei Teilen:

  1. Erstellen und Exportieren einer SQL Server Profiler-Ablaufverfolgung.
  2. Sie schreiben einen neuen Sammlungssatz auf der Grundlage einer exportierten Ablaufverfolgung.

Das Szenario für die folgenden Verfahren umfasst das Sammeln von Daten zu einer gespeicherten Prozedur, deren Ausführung 80 Millisekunden oder länger dauert. Um diese Verfahren durchzuführen, sollten Sie folgende Aufgaben ausführen können:

  • Verwenden Sie SQL Server Profiler, um eine Ablaufverfolgung zu erstellen und zu konfigurieren.
  • Verwenden Sie SQL Server Management Studio, um eine Abfrage zu öffnen, zu bearbeiten und auszuführen.

Erstellen und Exportieren einer SQL Server Profiler-Ablaufverfolgung

  1. Öffnen Sie in SQL Server Management Studio SQL Server Profiler. (Auf der Menü "Extras", wählen Sie "SQL Server Profiler" aus.)

  2. Klicken Sie im Dialogfeld Verbindung mit Server herstellen auf Abbrechen.

  3. Vergewissern Sie sich bei diesem Szenario, dass die Werte der Dauer für die Anzeige in Millisekunden (Standard) konfiguriert sind. Gehen Sie dazu wie folgt vor:

    1. Klicken Sie im Menü Extras auf Optionen.

    2. Stellen Sie im Bereich Anzeigeoptionen sicher, dass das Kontrollkästchen "Werte in der Spalte 'Dauer' in Mikrosekunden anzeigen" deaktiviert ist.

    3. Wählen Sie 'OK ' aus, um das Dialogfeld "Allgemeine Optionen " zu schließen.

  4. Wählen Sie im Menü "Datei" die Option "Neue Ablaufverfolgung" aus.

  5. Wählen Sie im Dialogfeld Verbinden zu Server den Server aus, mit dem Sie eine Verbindung herstellen möchten, und wählen Sie dann Verbinden aus.

    Das Dialogfeld Ablaufverfolgungseigenschaften wird angezeigt.

  6. Auf der Registerkarte Allgemein führen Sie die folgenden Schritt aus:

    1. Geben Sie im Feld Ablaufverfolgungsname den gewünschten Namen für die Ablaufverfolgung ein. In diesem Beispiel ist der Ablaufverfolgungsname SPgt140.

    2. Wählen Sie in der Liste Vorlage verwendendie Vorlage zur Verwendung für die Ablaufverfolgung aus. Wählen Sie in diesem Beispiel TSQL_SPs aus.

  7. Gehen Sie auf der Registerkarte Ereignisauswahl wie folgt vor:

    1. Geben Sie die Ereignisse zur Verwendung mit der Ablaufverfolgung an. Deaktivieren Sie in diesem Beispiel alle Kontrollkästchen in der Spalte Ereignisse , mit Ausnahme von ExistingConnection und SP:Completed.

    2. Aktivieren Sie in der unteren rechten Ecke das Kontrollkästchen Alle Spalten anzeigen .

    3. Wählen Sie die Zeile "SP:Abgeschlossen" aus .

    4. Führen Sie einen Bildlauf durch die Zeile in der Spalte Dauer aus, und aktivieren Sie das Kontrollkästchen Dauer .

  8. Wählen Sie in der unteren rechten Ecke Spaltenfilter aus, um das Dialogfeld "Filter bearbeiten" zu öffnen. Führen Sie im Dialogfeld Filter bearbeiten Folgendes durch:

    1. Wählen Sie in der Filterliste "Dauer" aus.

    2. Erweitern Sie im Booleschen Operatorfenster den Knoten Größer als oder gleich , geben Sie 80 den Wert ein, und wählen Sie dann OK aus.

  9. Wählen Sie "Ausführen" aus, um die Ablaufverfolgung zu starten.

  10. Wählen Sie auf der Symbolleiste "Ausgewählte Ablaufverfolgung beenden" oder "Ausgewählte Ablaufverfolgung anhalten" aus.

  11. Zeigen Sie im Menü "Datei" auf "Exportieren", zeigen Sie auf "Skriptablaufverfolgungsdefinition", und wählen Sie dann "Für SQL-Ablaufverfolgungssammlungssatz" aus.

  12. Geben Sie im Dialogfeld Speichern unter im Feld Dateiname den gewünschten Namen für die Ablaufverfolgungsdefinition ein, und speichern Sie sie am gewünschten Ort. In diesem Beispiel entspricht der Dateiname dem Namen der Ablaufverfolgung (SPgt140).

  13. Wählen Sie "OK " aus, wenn Sie eine Meldung erhalten, dass die Datei erfolgreich gespeichert wurde, und schließen Sie dann SQL Server Profiler.

Schreiben eines neuen Sammlungssatzes auf der Grundlage einer SQL Server Profiler-Ablaufverfolgung

  1. Zeigen Sie in SQL Server Management Studio im Menü "Datei" auf "Öffnen", und wählen Sie dann "Datei" aus.

  2. Suchen Sie im Dialogfeld "Datei öffnen" die Datei, die Sie im vorherigen VerfahrenSPgt140 () erstellt haben, und öffnen Sie sie dann.

    Die von Ihnen gespeicherte Ablaufverfolgung wird in einem Abfragefenster geöffnet und in einem Skript zusammengeführt, das Sie ausführen können, um den neuen Sammlungssatz zu erstellen.

  3. Führen Sie einen Bildlauf durch das Skript durch, und nehmen Sie die folgenden Ersetzungen vor, wie im Text des Skriptkommentars angegeben:

    • Ersetzen Sie SQLTrace Collection Set Name Here durch den Namen, den Sie für den Sammlungssatz verwenden möchten. Nennen Sie in diesem Beispiel den Sammlungssatz SPROC_CollectionSet.

    • Ersetzen Sie SQLTrace Collection Item Name Here durch den Namen, den Sie für das Sammelelement verwenden möchten. Nennen Sie in diesem Beispiel das Sammelelement SPROC_Collection_Item.

  4. Wählen Sie "Ausführen" aus, um die Abfrage auszuführen und den Sammlungssatz zu erstellen.

  5. Überprüfen Sie im Objekt-Explorer, ob der Sammlungssatz erstellt wurde. Gehen Sie dazu wie folgt vor:

    1. Klicken Sie mit der rechten Maustaste auf "Verwaltung", und wählen Sie dann "Aktualisieren" aus.

    2. Erweitern Sie Verwaltungund anschließend Datensammlung.

    Der Sammlungssatz SPROC_CollectionSet wird auf derselben Ebene wie der Knoten Systemdaten-Sammlungssätze angezeigt. Der Sammlungssatz ist in der Standardeinstellung deaktiviert.

  6. Bearbeiten Sie im Objekt-Explorer die Eigenschaften von SPROC_CollectionSet, wie Sammlungsmodus und Uploadzeitplan. Folgen Sie den gleichen Schritten, die Sie auch für die Systemdaten-Sammlungssätze ausführen würden, die mit dem Datensammler bereitgestellt werden.

Beispiele

Das folgende Codebeispiel ist das abschließende Skript. Dabei handelt es sich um das Ergebnis der in den vorherigen Schritten aufgezeigten Anweisungen.

/*************************************************************/
-- 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