Compartir a través de

Usar SQL Server Profiler para crear un conjunto de recopilación de Seguimiento de SQL

  • Artículo

Se aplica a: SQL Server

En SQL Server puede explotar las capacidades de rastreo del lado del servidor de SQL Server Profiler para exportar una definición de rastreo que puede utilizar para crear un conjunto de recopilación que utilice el tipo de Recopilador de seguimiento SQL genérico. En este proceso hay dos partes:

  1. Crear y exportar un seguimiento de perfil de SQL Server.
  2. Crear un script de un nuevo conjunto de recopilación basado en un seguimiento exportado.

El escenario para los procedimientos siguientes implica recopilar datos sobre cualquier procedimiento almacenado que requiera 80 milisegundos o más en completarse. Para completar estos procedimientos debería ser capaz de:

  • Usar SQL Server Profiler para crear y configurar un seguimiento.
  • Use SQL Server Management Studio para abrir, editar y ejecutar una consulta.

Crear y exportar un seguimiento de SQL Server Profiler

  1. En SQL Server Management Studio, abra SQL Server Profiler. (En el menú Herramientas , haga clic en SQL Server Profiler).

  2. En el cuadro de diálogo Conectar con el servidor, seleccione Cancelar.

  3. Para este escenario, asegúrese de que los valores de duración estén configurados para mostrarse en milisegundos (valor predeterminado). Para ello, siga estos pasos:

    1. En el menú Herramientas, seleccione Opciones.

    2. En el área Opciones de visualización , asegúrese de que esté desactivada la casilla Mostrar valores en la columna Duración en microsegundos.

    3. Seleccione Aceptar para cerrar el cuadro de diálogo Opciones generales.

  4. En el menú Archivo, seleccione Nuevo seguimiento.

  5. En el cuadro de diálogo Conectar con el servidor , seleccione el servidor con el que desea conectarse y, a continuación, seleccione Conectar.

    Aparecerá el cuadro de diálogo Propiedades de seguimiento .

  6. En la pestaña General, puede realizar las siguientes acciones:

    1. En el cuadro Nombre de seguimiento , escriba el nombre que desee usar para el seguimiento. En este ejemplo, el nombre del seguimiento es SPgt140.

    2. En la lista Usar la plantilla, seleccione la plantilla que desea usar para el seguimiento. Para este ejemplo, haga clic en TSQL_SPs.

  7. En la pestaña Selección de eventos , haga lo siguiente:

    1. Identifique los eventos que se van a usar para el seguimiento. En este ejemplo, desactive todas las casillas de la columna Eventos excepto ExistingConnection y SP:Completed.

    2. En la esquina inferior derecha, active la casilla Mostrar todas las columnas .

    3. Seleccione la fila SP:Completed .

    4. Desplácese por la fila hasta la columna Duración y, a continuación, active la casilla Duración .

  8. En la esquina inferior derecha, seleccione Filtros de columna para abrir el cuadro de diálogo Editar filtro . En el cuadro de diálogo Editar filtro , realice las siguientes operaciones:

    1. En la lista de filtros, seleccione Duración.

    2. En la ventana de operadores booleanos, expanda el nodo Mayor o igual que, escriba 80 como valor y, a continuación, haga clic en Aceptar.

  9. Seleccione Ejecutar para iniciar el seguimiento.

  10. En la barra de herramientas, seleccione Detener seguimiento seleccionado o Pausar seguimiento seleccionado.

  11. En el menú Archivo , seleccione Exportar, Definición de seguimiento de scriptsy, a continuación, seleccione Para el conjunto de recopilación de Seguimiento de SQL.

  12. En el cuadro Nombre de archivo del cuadro de diálogo Guardar como , escriba el nombre que desea usar para la definición de seguimiento y, a continuación, guárdela en la ubicación que desee. En este ejemplo, el nombre de archivo es igual que el nombre de seguimiento (SPgt140).

  13. Seleccione Aceptar cuando aparezca el mensaje que indica que el archivo se guardó correctamente y, a continuación, cierre SQL Server Profiler.

Crear un script de un nuevo conjunto de recopilación a partir de un seguimiento de SQL Server Profiler

  1. En el menú Archivo de SQL Server Management Studio, seleccione Abrir y, a continuación, seleccione Archivo.

  2. En el cuadro de diálogo Abrir archivo, localice el archivo que creó en el procedimiento anterior (SPgt140) y ábralo.

    La información de seguimiento que guardó se abre en una ventana Consulta y se combina en un script que puede ejecutar para crear el nuevo conjunto de recopilación.

  3. Desplácese a través del script y realice las sustituciones siguientes, que están anotadas en el texto de los comentarios del script:

    • Reemplace SQLTrace Collection Set Name Here por el nombre que desea usar para el conjunto de recopilación. En este ejemplo, asigne el nombre SPROC_CollectionSetal conjunto de recopilación.

    • Reemplace SQLTrace Collection Item Name Here por el nombre que desea usar para el elemento de recopilación. En este ejemplo, asigne el nombre SPROC_Collection_Itemal elemento de recopilación.

  4. Seleccione Ejecutar para ejecutar la consulta y crear el conjunto de recopilación.

  5. En el Explorador de objetos, compruebe que se ha creado el conjunto de recopilación. Para ello, siga estos pasos:

    1. Haga clic con el botón derecho en Administracióny, después, seleccione Actualizar.

    2. Expanda Administracióny, a continuación, expanda Recopilación de datos.

    El conjunto de recopilación SPROC_CollectionSet aparece en el mismo nivel que el nodo Conjuntos de recopilación de datos del sistema . De manera predeterminada, el conjunto de recopilación está deshabilitado.

  6. Use el Explorador de objetos para editar las propiedades de SPROC_CollectionSet, como el modo de recopilación y la programación de carga. Realice los mismos procedimientos que usaría para los conjuntos de recopilación de datos del sistema que se proporcionan con el recopilador de datos.

Ejemplos

El ejemplo de código siguiente es el script final que resulta de los pasos que se documentan en los procedimientos anteriores.

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