Utilizzare SQL Server Profiler per creare un set di raccolta Traccia SQLUse SQL Server Profiler to Create a SQL Trace Collection Set

In SQL Server 2017SQL Server 2017 è possibile sfruttare le funzionalità di traccia sul lato server di SQL Server ProfilerSQL Server Profiler per esportare una definizione della traccia da usare per creare un set di raccolta che usa il tipo di agente di raccolta Traccia SQL generico.In SQL Server 2017SQL Server 2017 you can exploit the server-side trace capabilities of SQL Server ProfilerSQL Server Profiler to export a trace definition that you can use to create a collection set that uses the Generic SQL Trace collector type. Questo processo è costituito da due operazioni:There are two parts to this process:

  1. Creazione ed esportazione di una traccia di SQL Server ProfilerSQL Server Profiler .Create and export a SQL Server ProfilerSQL Server Profiler trace.

  2. Creazione di uno script per un nuovo set di raccolta basato su una traccia esportata.Script a new collection set based on an exported trace.

    Lo scenario per le procedure seguenti comporta la raccolta di dati su tutte le stored procedure il cui completamento richiede almeno 80 millisecondi.The scenario for the following procedures involves collecting data about any stored procedure that requires 80 milliseconds or longer to complete. Per completare queste procedure è necessario essere in grado di effettuare le operazioni seguenti:In order to complete these procedures you should be able to:

  • Utilizzare SQL Server ProfilerSQL Server Profiler per creare e configurare una traccia.Use SQL Server ProfilerSQL Server Profiler to create and configure a trace.

  • Utilizzare SQL Server Management StudioSQL Server Management Studio per aprire, modificare ed eseguire una query.Use SQL Server Management StudioSQL Server Management Studio to open, edit, and execute a query.

Creazione ed esportazione di una traccia di SQL Server ProfilerCreate and export a SQL Server Profiler trace

  1. In SQL Server Management StudioSQL Server Management Studioaprire SQL Server ProfilerSQL Server Profiler.In SQL Server Management StudioSQL Server Management Studio, open SQL Server ProfilerSQL Server Profiler. Scegliere SQL Server Profiler dal menu Strumenti.(On the Tools menu, click SQL Server Profiler.)

  2. Nella finestra di dialogo Connetti al server fare clic su Annulla.In the Connect to Server dialog box, click Cancel.

  3. Per questo scenario, assicurarsi che i valori di durata siano configurati per essere visualizzati in millisecondi (impostazione predefinita).For this scenario, ensure that duration values are configured to display in milliseconds (the default). A tale scopo, eseguire le operazioni seguenti:To do this, follow these steps:

    1. Scegliere Opzioni dal menu Strumenti.On the Tools menu, click Options.

    2. Nell'area Opzioni di visualizzazione assicurarsi che la casella di controllo Mostra i valori nella colonna Durata in microsecondi sia deselezionata.In the Display Options area, ensure that the Show values in Duration column in microseconds check box is cleared.

    3. Fare clic su OK per chiudere la finestra di dialogo Opzioni generali .Click OK to close the General Options dialog box.

  4. Scegliere Nuova traccia dal menu File.On the File menu, click New Trace.

  5. Nella finestra di dialogo Connetti al server selezionare il server con cui stabilire la connessione, quindi fare clic su Connetti.In the Connect to Server dialog box, select the server that you want to connect to, and then click Connect.

    Verrà visualizzata la finestra di dialogo Proprietà traccia .The Trace Properties dialog box appears.

  6. Nella scheda Generale eseguire queste operazioni:On the General tab, do the following:

    1. Nella casella Nome traccia digitare il nome da usare per la traccia.In the Trace name box, type the name that you want to use for the trace. Per questo esempio, il nome della traccia è SPgt80.For this example, the trace name is SPgt80.

    2. Nell'elenco Modelloselezionare il modello da usare per la traccia.In the Use the template list, select the template to use for the trace. Per questo esempio, fare clic su TSQL_SPs.For this example, click TSQL_SPs.

  7. Nella scheda Selezione eventi eseguire queste operazioni:On the Events Selection tab, do the following:

    1. Identificare gli eventi da utilizzare per la traccia.Identify the events to use for the trace. Per questo esempio, deselezionare tutte le caselle di controllo nella colonna Eventi , tranne ExistingConnection e SP:Completed.For this example, clear all check boxes in the Events column, except for ExistingConnection and SP:Completed.

    2. Nell'angolo in basso a destra selezionare la casella di controllo Mostra tutte le colonne .In the lower-right corner, select the Show all columns check box.

    3. Fare clic sulla riga SP:Completed .Click the SP:Completed row.

    4. Scorrere la riga fino alla colonna Durata , quindi selezionare la casella di controllo Durata .Scroll across the row to the Duration column, and then select the Duration check box.

  8. Nell'angolo in basso a destra fare clic su Filtri colonne per aprire la finestra di dialogo Modifica filtro .In the lower-right corner, click Column Filters to open the Edit Filter dialog box. Nella finestra di dialogo Modifica filtro eseguire queste operazioni:In the Edit Filter dialog box, do the following:

    1. Nell'elenco di filtri fare clic su Durata.In the filter list, click Duration.

    2. Nella finestra di operatori booleani espandere il nodo Maggiore o uguale a , digitare 80 come valore, quindi fare clic su OK.In the Boolean operator window, expand the Greater than or equal node, type 80 as the value, and then click OK.

  9. Fare clic su Esegui per avviare la traccia.Click Run to start the trace.

  10. Sulla barra degli strumenti fare clic su Arresta traccia selezionata o Sospendi traccia selezionata.On the toolbar, click Stop Selected Trace or Pause Selected Trace.

  11. Scegliere Esporta dal menu File, fare clic su Crea script per definizione traccia, quindi su Per set di raccolta Traccia SQL.On the File menu, point to Export, point to Script Trace Definition, and then click For SQL Trace Collection Set.

  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.In the Save As dialog box, type the name that you want to use for the trace definition in the File name box, and then save it in the location that you want. Per questo esempio, il nome del file è identico al nome della traccia, ovvero SPgt80.For this example, the file name is the same as the trace name (SPgt80).

  13. Fare clic su OK quando si riceve un messaggio indicante che il file è stato salvato correttamente, quindi chiudere SQL Server ProfilerSQL Server Profiler.Click OK when you receive a message that the file was successfully saved, and then close SQL Server ProfilerSQL Server Profiler.

Creazione di uno script per un nuovo set di raccolta da una traccia di SQL Server ProfilerScript a new collection set from a SQL Server Profiler trace

  1. In SQL Server Management StudioSQL Server Management Studioscegliere Apri dal menu File , quindi fare clic su File.In SQL Server Management StudioSQL Server Management Studio, on the File menu, point to Open, and then click File.

  2. Nella finestra di dialogo Apri file trovare e aprire il file creato nella procedura precedente (SPgt80).In the Open File dialog box, locate and then open the file that you created in the previous procedure (SPgt80).

    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.The trace information that you saved is opened in a Query window and merged into a script that you can run to create the new collection set.

  3. Scorrere lo script ed eseguire le sostituzioni seguenti, indicate nel testo di commento dello script:Scroll through the script and make the following replacements, which are noted in the script comment text:

    • Sostituire SQLTrace Collection Set Name Here con il nome che si vuole usare per il set di raccolta.Replace SQLTrace Collection Set Name Here with the name that you want to use for the collection set. Per questo esempio, assegnare al set di raccolta il nome SPROC_CollectionSet.For this example, name the collection set SPROC_CollectionSet.

    • Sostituire SQLTrace Collection Item Name Here con il nome che si vuole usare per l'elemento della raccolta.Replace SQLTrace Collection Item Name Here with the name that you want to use for the collection item. Per questo esempio, assegnare all'elemento della raccolta il nome SPROC_Collection_Item.For this example, name the collection item SPROC_Collection_Item.

  4. Fare clic su Esegui per eseguire la query e creare il set di raccolta.Click Execute to run the query and to create the collection set.

  5. In Esplora oggetti verificare che il set di raccolta sia stato creato.In Object Explorer, verify that the collection set was created. A tale scopo, eseguire le operazioni seguenti:To do this, follow these steps:

    1. Fare clic con il pulsante destro del mouse su Gestione, quindi scegliere Aggiorna.Right-click Management, and then click Refresh.

    2. Espandere Gestione, quindi Raccolta dati.Expand Management, and then expand Data Collection.

      Il set di raccolta SPROC_CollectionSet viene visualizzato allo stesso livello del nodo Set di raccolta dati di sistema .The SPROC_CollectionSet collection set appears at the same level as the System Data Collection Sets node. Per impostazione predefinita, il set di raccolta è disabilitato.By default, the collection set is disabled.

  6. Utilizzare Esplora oggetti per modificare le proprietà di SPROC_CollectionSet, ad esempio la modalità di raccolta e la pianificazione di caricamento.Use Object Explorer to edit the properties of SPROC_CollectionSet, such as the collection mode and upload schedule. Seguire le stesse procedure utilizzate per i set di raccolta dati di sistema forniti con l'agente di raccolta dati.Follow the same procedures that you would for the System Data collection sets that are provided with the data collector.

EsempioExample

L'esempio di codice seguente rappresenta lo script finale risultante dai passaggi descritti nelle procedure precedenti.The following code sample is the final script resulting from the steps documented in the preceding procedures.

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