Sdílet prostřednictvím

Použití SQL Server Profileru k vytvoření sady kolekcí trasování SQL

  • Článek

platí pro:SQL Server

Na SQL Serveru můžete využít možnosti trasování na straně serveru SQL Server Profiler k exportu definice trasování, kterou můžete použít k vytvoření sady kolekcí, která používá typ obecného kolektoru trasování SQL. Tento proces má dvě části:

  1. Vytvořte a exportujte trasování SQL Server Profiler
  2. Vytvořte skript nové sady sbírek na základě exportovaného trasování.

Scénář pro následující postupy zahrnuje shromažďování dat o všech uložených procedurách, které vyžadují dokončení 80 milisekund nebo déle. Abyste mohli provést tyto postupy, měli byste být schopni:

  • K vytvoření a konfiguraci trasování použijte SQL Server Profiler.
  • Pomocí aplikace SQL Server Management Studio otevřete, upravte a spusťte dotaz.

Vytvořte a exportujte trasování SQL Server Profiler

  1. V aplikaci SQL Server Management Studio otevřete SQL Server Profiler. (V nabídce Tools vyberte SQL Server Profiler.)

  2. V dialogovém okně Připojit k serveru vyberte Zrušit.

  3. V tomto scénáři se ujistěte, že jsou hodnoty doby trvání nakonfigurované tak, aby se zobrazovaly v milisekundách (výchozí hodnota). Postupujte takto:

    1. V nabídce Nástroje vyberte Možnosti.

    2. V oblasti Možnosti zobrazení se ujistěte, že políčko Zobrazit hodnoty ve sloupci Doba trvání v mikrosekundách není zaškrtnuté.

    3. Výběrem OK zavřete dialogové okno Obecné možnosti.

  4. V nabídce Soubor vyberte Nové sledování.

  5. V dialogovém okně Připojit k serveru vyberte server, ke kterému se chcete připojit, a pak vyberte Připojit.

    Zobrazí se dialogové okno s názvem Vlastnosti trasování.

  6. Na kartě Obecné postupujte takto:

    1. Do pole Název trasování zadejte název, který chcete pro trasování použít. V tomto příkladu je název stopy SPgt140.

    2. V seznamu šablonvyberte šablonu, kterou chcete použít pro trasování. V tomto příkladu vyberte TSQL_SPs.

  7. Na kartě Výběr událostí proveďte následující kroky:

    1. Identifikujte události, které se mají použít pro trasování. V tomto příkladu zrušte zaškrtnutí všech políček ve sloupci Události s výjimkou Stávající spojení a SP:Completed.

    2. V pravém dolním rohu zaškrtněte políčko Zobrazit všechny sloupce.

    3. Vyberte řádek SP:Completed.

    4. Posuňte se přes řádek ke sloupci Doba trvání a zaškrtněte políčko Doba trvání.

  8. V pravém dolním rohu vyberte Filtry sloupců k otevření dialogového okna Upravit filtr. V dialogovém okně Upravit filtr postupujte takto:

    1. V seznamu filtrů vyberte Délka trvání.

    2. V okně logického operátoru rozbalte uzel Větší než nebo roven, zadejte 80 jako hodnotu a pak vyberte OK.

  9. Vyberte Spustit pro zahájení trasování.

  10. Na panelu nástrojů vyberte Zastavit vybrané trasování nebo Pozastavit vybrané trasování.

  11. V nabídce File přejděte na Export, přejděte na Definice trasování skriptu a pak vyberte Pro sadu kolekcí trasování SQL.

  12. V dialogovém okně Uložit jako zadejte název, který chcete použít pro definici trasování, do pole Název souboru a uložte ho do požadovaného umístění. V tomto příkladu je název souboru stejný jako název trasování (SPgt140).

  13. Vyberte OK, když se zobrazí zpráva, že soubor byl úspěšně uložen, a zavřete SQL Server Profiler.

Skriptování nové sady kolekcí z trasování SQL Server Profileru

  1. V aplikaci SQL Server Management Studio v nabídce File přejděte na Otevřít, a pak vyberte Soubor.

  2. V dialogovém okně Otevřít soubor vyhledejte a otevřete soubor, který jste vytvořili v předchozím postupu (SPgt140).

    Informace o trasování, které jste uložili, se otevřou v okně dotazu a sloučí se do skriptu, který můžete spustit a vytvořit novou sadu kolekcí.

  3. Projděte skript a proveďte následující nahrazení, které jsou uvedeny v textu komentáře skriptu:

    • Nahraďte název sady kolekcí SQLTrace zde názvem, který chcete použít pro sadu kolekcí. V tomto příkladu pojmenujte sadu kolekcí SPROC_CollectionSet.

    • Nahraďte název položky kolekce SQLTrace zde názvem, který chcete použít pro položku kolekce. V tomto příkladu pojmenujte položku kolekce SPROC_Collection_Item.

  4. Výběrem Spustit proveďte dotaz a vytvořte kolekci.

  5. V Průzkumníku objektů ověřte, že byla vytvořena sada kolekcí. Postupujte takto:

    1. Klepněte pravým tlačítkem myši na Managementa potom vyberte Aktualizovat.

    2. Rozbalte Managementa potom rozbalte Shromažďování dat.

    Kolekční sada SPROC_CollectionSet se zobrazí na stejné úrovni jako uzel systémové sady sběru dat. Ve výchozím nastavení je sada kolekcí zakázaná.

  6. Pomocí Průzkumníka objektů můžete upravovat vlastnosti SPROC_CollectionSet, jako je režim kolekce a plán nahrávání. Postupujte podle stejných postupů jako u sad shromažďování systémových dat, které jsou k dispozici se shromažďovačem dat.

Příklady

Následující ukázka kódu je konečný skript, který je výsledkem kroků popsaných v předchozích postupech.

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