使用 SQL Server Profiler 创建 SQL 跟踪收集组

适用于SQL Server

在 SQL Server 中,可以利用 SQL Server Profiler 的服务器端跟踪功能导出跟踪定义,然后用来创建将使用通用 SQL 跟踪收集器类型的收集组。 此过程分为两个部分:

  1. 创建和导出 SQL Server Profiler 跟踪。
  2. 根据导出的跟踪编写新的收集组脚本。

以下过程的应用场景包括收集任何存储过程的相关数据,此过程需要 80 毫秒或更长的时间才能完成。 若要完成这些过程,您应该能够:

  • 使用 SQL Server Profiler 创建并配置跟踪。
  • 使用 SQL Server Management Studio 打开、编辑和执行查询。

创建和导出 SQL Server Profiler 跟踪

  1. 在 SQL Server Management Studio 中,打开 SQL Server Profiler。 (在“工具”菜单上,选择“SQL Server Profiler”。)

  2. 在“连接到服务器”对话框中,选择“取消”。

  3. 对于此应用场景,请确保将持续时间值配置为以毫秒为单位显示(默认设置)。 为此,请按照下列步骤进行操作:

    1. 在“工具”菜单上选择“选项” 。

    2. “显示选项” 区域中,确保清除“在‘持续时间’列中以微秒为单位显示值”复选框。

    3. 选择“确定”,关闭“一般选项”对话框 。

  4. 在“文件”菜单上,选择“新建跟踪”。

  5. 在“连接到服务器”对话框中,选择要连接到的服务器,然后选择“连接”。

    此时,将显示 “跟踪属性” 对话框。

  6. 常规选项卡上,执行以下操作:

    1. “跟踪名称” 框中,键入该跟踪要使用的名称。 在本示例中,跟踪名称为 SPgt140

    2. “使用模板”列表中,选择要用于该跟踪的模板。 在本示例中,请选择“TSQL_SPs”。

  7. 在“事件选择”选项卡上,请执行下列操作:

    1. 标识要用于该跟踪的事件。 在本示例中,清除 “事件” 列中除 ExistingConnectionSP:Completed以外的所有复选框。

    2. 在右下角,选中“显示所有列”复选框。

    3. 选择“SP:Completed”行。

    4. 在这一行中滚动到 “持续时间” 列,然后选中 “持续时间” 复选框。

  8. 在右下角,选择“列筛选器”以打开“编辑筛选器”对话框。 在 “编辑筛选器” 对话框中,请执行下列操作:

    1. 在筛选器列表中,选择“持续时间”。

    2. 在布尔运算符窗口中,展开“大于或等于”节点,键入 80 作为值,然后选择“确定”。

  9. 选择“运行”以启动跟踪。

  10. 在工具栏上,选择“停止所选跟踪”或“暂停所选跟踪”。

  11. 在“文件”菜单上,依次指向“导出”和“编写跟踪定义的脚本”,然后选择“用于 SQL 跟踪收集组”。

  12. “另存为” 对话框中,在 “文件名” 框中键入要为该跟踪定义使用的名称,然后将跟踪定义保存到所需位置。 在本示例中,文件名与跟踪名称 SPgt140 相同。

  13. 收到文件已经成功保存的消息后,请选择“确定”,然后关闭 SQL Server Profiler。

通过 SQL Server Profiler 跟踪编写新收集组的脚本

  1. 在 SQL Server Management Studio 的“文件”菜单上指向“打开”,然后选择“文件”。

  2. 在“打开文件”对话框中,找到并打开你在前面的过程中创建的文件 (SPgt140)。

    将在“查询”窗口中打开保存的跟踪信息,并将其合并到可运行以创建新收集组的脚本中。

  3. 滚动查看脚本并进行以下替换(在脚本注释文本中注明):

    • SQLTrace Collection Set Name Here 替换为要为收集组使用的名称。 在本示例中,将收集组命名为 SPROC_CollectionSet

    • SQLTrace Collection Item Name Here 替换为要为收集项使用的名称。 在本示例中,将收集项命名为 SPROC_Collection_Item

  4. 选择“执行”运行查询并创建收集组。

  5. 在对象资源管理器中,验证该收集组是否已经创建。 为此,请按照下列步骤进行操作:

    1. 右键单击“管理”,然后选择“刷新”。

    2. 展开 “管理”,然后展开 “数据收集”

    SPROC_CollectionSet 收集组与“系统数据收集组”节点显示在同一级。 默认情况下,将禁用该收集组。

  6. 使用对象资源管理器编辑 SPROC_CollectionSet 的属性,如收集模式和上载计划。 按照针对与数据收集器一起提供的系统数据收集组的过程进行操作。

示例

下面的代码示例是以上过程中所述的步骤生成的最终脚本。

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