Freigeben über

  • Artikel

Vorgehensweise: Verwenden von SQL Server Profiler zum Erstellen eines Auflistsatzes für die SQL-Ablaufverfolgung

In SQL Server 2008 können Sie die serverseitigen Ablaufverfolgungsfunktionen von SQL Server Profiler nutzen, um eine Ablaufverfolgungsdefinition für das Erstellen eines Sammlungssatzes zu exportieren, der den generischen Auflistertyp für die SQL-Ablaufverfolgung verwendet. Dieser Vorgang besteht aus zwei Teilen:

  1. Sie erstellen und exportieren eine SQL Server Profiler-Ablaufverfolgung.

  2. Sie schreiben einen neuen Auflistsatz 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 von SQL Server Profiler zum Erstellen und Konfigurieren einer Ablaufverfolgung

  • Verwenden von SQL Server Management Studio zum Öffnen, Bearbeiten und Ausführen einer Abfrage

Erstellen und Exportieren einer SQL Server Profiler-Ablaufverfolgung

  1. Öffnen Sie in SQL Server Management Studio den SQL Server Profiler. (Klicken Sie im Menü Extras auf SQL Server Profiler.)

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

  3. Stellen Sie für dieses Szenario sicher, dass die Dauerwerte in Millisekunden angezeigt werden (Standardeinstellung). Führen Sie hierzu folgende Schritte aus:

    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 (nur SQL Server 2005 oder höher) deaktiviert wurde.

    3. Klicken Sie auf OK, um das Dialogfeld Allgemeine Optionen zu schließen.

  4. Klicken Sie im Menü Datei auf Neue Ablaufverfolgung.

  5. Wählen Sie im Dialogfeld Verbindung mit Server herstellen den Server, zu dem eine Verbindung hergestellt werden soll, und klicken Sie anschließend auf Verbinden.

    Das Dialogfeld Ablaufverfolgungseigenschaften wird angezeigt.

  6. Führen Sie auf der Registerkarte Allgemein die folgenden Schritte aus:

    1. Geben Sie im Feld Ablaufverfolgungsname den Namen für die Ablaufverfolgung ein. In diesem Beispiel lautet der Ablaufverfolgungsname SPgt80.

    2. Wählen Sie in der Liste Vorlage verwenden die für die Ablaufverfolgung zu verwendendeVorlage aus. Klicken Sie in diesem Beispiel auf TSQL_SPs.

  7. Führen Sie auf der Registerkarte Ereignisauswahl folgende Schritte aus:

    1. Geben Sie die für die Ablaufverfolgung zu verwendenden Ereignisse an. Deaktivieren Sie für dieses Beispiel alle Kontrollkästchen in der Spalte Ereignisse mit Außnahme der Kontrollkästchen ExistingConnection und SP:Completed.

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

    3. Klicken Sie auf die Zeile SP:Completed.

    4. Wechseln Sie in der Zeile zur Spalte Dauer, und aktivieren Sie das Kontrollkästchen Dauer.

  8. Klicken Sie in der rechten unteren Ecke auf Spaltenfilter, um das Dialogfeld Filter bearbeiten zu öffnen. Führen Sie im Dialogfeld Filter bearbeiten folgende Schritte aus:

    1. Klicken Sie in der Filterliste auf Dauer.

    2. Erweitern Sie im Fenster für den booleschen Operator den Knoten Größer als oder gleich, geben Sie als Wert 80 ein, und klicken Sie anschließend auf OK.

  9. Klicken Sie auf Ausführen, um die Ablaufverfolgung zu starten.

  10. Klicken Sie auf der Symbolleiste auf Ausgewählte Ablaufverfolgung beenden oder auf Ausgewählte Ablaufverfolgung anhalten.

  11. Zeigen Sie im Menü Datei auf Exportieren, zeigen Sie auf Skript für Ablaufverfolgungsdefinition erstellen, und klicken Sie dann auf Für den Auflistsatz der SQL-Ablaufverfolgung.

  12. Geben Sie im Dialogfeld Speichern unter im Feld Dateinamen den Namen für die Ablaufverfolgungsdefinition ein, und speichern Sie diese anschließend am gewünschten Speicherort. In diesem Beispiel entspricht der Dateiname dem Namen der Ablaufverfolgung (SPgt80).

  13. Klicken Sie auf OK, wenn Sie die Meldung erhalten, dass die Datei erfolgreich gespeichert wurde. Beenden Sie anschließend SQL Server Profiler.

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

  1. Zeigen Sie in SQL Server Management Studio im Menü Datei auf Öffnen, und klicken Sie dann auf Datei.

  2. Suchen und öffnen Sie im Dialogfeld Datei öffnen die Datei, die Sie im vorherigen Schritt (SPgt80) erstellt haben.

    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 Auflistsatz 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 Auflistsatz verwenden möchten. Benennen Sie den Sammlungssatz in diesem Beispiel wie folgt: SPROC_CollectionSet.

    • Ersetzen Sie SQLTrace Collection Item Name Here durch den Namen, den Sie für das Auflistelement verwenden möchten. Benennen Sie für dieses Beispiel das Auflistelement wie folgt: SPROC_Collection_Item.

  4. Klicken Sie auf Ausführen, um die Abfrage auszuführen und den Sammlungssatz zu erstellen.

  5. Überprüfen Sie im Objekt-Explorer, ob der Sammlungssatz erstellt wurde. Führen Sie hierzu folgende Schritte aus:

    1. Klicken Sie mit der rechten Maustaste auf Verwaltung, und klicken Sie anschließend auf Aktualisieren.

    2. Erweitern Sie Verwaltung und anschließend Datensammlung.

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

  6. Verwenden Sie den Objekt-Explorer, um die Eigenschaften von SPROC_CollectionSet zu bearbeiten (z. B. den Auflistmodus und den Uploadzeitplan). Führen Sie die gleichen Schritte wie für die Systemdaten-Sammlungssätze durch, die mit dem Datenauflister bereitgestellt werden.

Beispiel

Beim folgenden Codebeispiel handelt es sich um das abschließende Skript, das das Ergebnis der in den vorherigen Schritten aufgezeigten Anweisungen darstellt.

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2007  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