Share via

Usare SQL Server Profiler per creare un set di raccolta Analisi SQL

  • Articolo

Si applica a:SQL Server

In SQL Server puoi sfruttare le funzionalità di analisi sul lato server di SQL Server Profiler per esportare una definizione di traccia da usare per creare un set di raccolta che usa il tipo di agente di raccolta tracce SQL generico. Questo processo è costituito da due operazioni:

  1. Creazione ed esportazione di una traccia di SQL Server Profiler.
  2. Creazione di uno script per un nuovo set di raccolta basato su una traccia esportata.

Lo scenario per le procedure seguenti comporta la raccolta di dati su tutte le stored procedure il cui completamento richiede almeno 80 millisecondi. Per completare queste procedure è necessario essere in grado di effettuare le operazioni seguenti:

  • Usare SQL Server Profiler per creare e configurare una traccia.
  • Usare SQL Server Management Studio per aprire, modificare ed eseguire una query.

Creazione ed esportazione di una traccia di SQL Server Profiler

  1. In SQL Server Management Studio, apri SQL Server Profiler. (Seleziona SQL Server Profiler dal menu Strumenti.)

  2. Nella finestra di dialogo Connetti al server seleziona Annulla.

  3. Per questo scenario, assicurarsi che i valori di durata siano configurati per essere visualizzati in millisecondi (impostazione predefinita). A tale scopo, effettuare i passaggi seguenti:

    1. Scegliere Options (Opzioni) dal menu Tools (Strumenti).

    2. Nell'area Opzioni di visualizzazione assicurarsi che la casella di controllo Mostra i valori nella colonna Durata in microsecondi sia deselezionata.

    3. Seleziona OK per chiudere la finestra di dialogo Opzioni generali.

  4. Seleziona Nuova Traccia dal menu File.

  5. Nella finestra di dialogo Connetti al server seleziona il server con cui stabilire la connessione, quindi seleziona Connetti.

    Verrà visualizzata la finestra di dialogo Proprietà traccia .

  6. Nella scheda Generale, procedi come segue:

    1. Nella casella Nome traccia digitare il nome da usare per la traccia. Per questo esempio, il nome della traccia è SPgt140.

    2. Nell'elenco Modelloselezionare il modello da usare per la traccia. Per questo esempio, seleziona TSQL_SPs.

  7. Nella scheda Selezione eventi eseguire queste operazioni:

    1. Identificare gli eventi da utilizzare per la traccia. Per questo esempio, deselezionare tutte le caselle di controllo nella colonna Eventi , tranne ExistingConnection e SP:Completed.

    2. Nell'angolo in basso a destra selezionare la casella di controllo Mostra tutte le colonne .

    3. Seleziona la riga SP:Completed.

    4. Scorrere la riga fino alla colonna Durata , quindi selezionare la casella di controllo Durata .

  8. Nell'angolo in basso a destra seleziona Filtri colonne per aprire la finestra di dialogo Modifica filtro. Nella finestra di dialogo Modifica filtro eseguire queste operazioni:

    1. Nell'elenco di filtri seleziona Durata.

    2. Nella finestra di operatori booleani espandi il nodo Maggiore o uguale a, digita 80 come valore, quindi seleziona OK.

  9. Seleziona Esegui per avviare la traccia.

  10. Sulla barra degli strumenti seleziona Arresta traccia selezionata o Sospendi traccia selezionata.

  11. Scegli Esporta dal menu File, poi Crea script per definizione di traccia, quindi seleziona Per set di raccolta Traccia SQL.

  12. Nella casella Nome file della finestra di dialogo Salva con nome digitare il nome da usare per la definizione della traccia, quindi salvarlo nel percorso desiderato. Per questo esempio, il nome file è identico al nome della traccia (SPgt140).

  13. Seleziona OK quando ricevi un messaggio indicante che il file è stato salvato correttamente, quindi chiudi SQL Server Profiler.

Creazione di uno script per un nuovo set di raccolta da una traccia di SQL Server Profiler

  1. In SQL Server Management Studio, scegli Apri dal menu File e quindi seleziona File.

  2. Nella finestra di dialogo Apri file ricerca e apri il file creato nella procedura precedente (SPgt140).

    Le informazioni sulla traccia salvate in precedenza verranno aperte in una finestra di query e unite in uno script che è possibile eseguire per creare il nuovo set di raccolta.

  3. Scorrere lo script ed eseguire le sostituzioni seguenti, indicate nel testo di commento dello script:

    • Sostituire SQLTrace Collection Set Name Here con il nome che si vuole usare per il set di raccolta. Per questo esempio, assegnare al set di raccolta il nome SPROC_CollectionSet.

    • Sostituire SQLTrace Collection Item Name Here con il nome che si vuole usare per l'elemento della raccolta. Per questo esempio, assegnare all'elemento della raccolta il nome SPROC_Collection_Item.

  4. Seleziona Esegui per eseguire la query e creare il set di raccolta.

  5. In Esplora oggetti verificare che il set di raccolta sia stato creato. A tale scopo, effettuare i passaggi seguenti:

    1. Fai clic con il pulsante destro del mouse su Gestione, quindi seleziona Aggiorna.

    2. Espandere Gestione, quindi Raccolta dati.

    Il set di raccolta SPROC_CollectionSet viene visualizzato allo stesso livello del nodo Set di raccolta dati di sistema . Per impostazione predefinita, il set di raccolta è disabilitato.

  6. Utilizzare Esplora oggetti per modificare le proprietà di SPROC_CollectionSet, ad esempio la modalità di raccolta e la pianificazione di caricamento. Seguire le stesse procedure utilizzate per i set di raccolta dati di sistema forniti con l'agente di raccolta dati.

Esempi

L'esempio di codice seguente rappresenta lo script finale risultante dai passaggi descritti nelle procedure precedenti.

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