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

项目
01/02/2024

适用于：SQL Server

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

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

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

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

创建和导出 SQL Server Profiler 跟踪

在 SQL Server Management Studio 中，打开 SQL Server Profiler。（在“工具”菜单上，选择“SQL Server Profiler”。）
在“连接到服务器”对话框中，选择“取消”。
对于此应用场景，请确保将持续时间值配置为以毫秒为单位显示（默认设置）。为此，请按照下列步骤进行操作：
1. 在“工具”菜单上选择“选项” 。
2. 在 “显示选项” 区域中，确保清除“在‘持续时间’列中以微秒为单位显示值”复选框。
3. 选择“确定”，关闭“一般选项”对话框。
在“文件”菜单上，选择“新建跟踪”。
在“连接到服务器”对话框中，选择要连接到的服务器，然后选择“连接”。

此时，将显示 “跟踪属性” 对话框。
在常规选项卡上，执行以下操作：
1. 在 “跟踪名称” 框中，键入该跟踪要使用的名称。在本示例中，跟踪名称为 SPgt140。
2. 在 “使用模板”列表中，选择要用于该跟踪的模板。在本示例中，请选择“TSQL_SPs”。
在“事件选择”选项卡上，请执行下列操作：
1. 标识要用于该跟踪的事件。在本示例中，清除 “事件” 列中除 ExistingConnection 和 SP:Completed以外的所有复选框。
2. 在右下角，选中“显示所有列”复选框。
3. 选择“SP:Completed”行。
4. 在这一行中滚动到 “持续时间” 列，然后选中 “持续时间” 复选框。
在右下角，选择“列筛选器”以打开“编辑筛选器”对话框。在 “编辑筛选器” 对话框中，请执行下列操作：
1. 在筛选器列表中，选择“持续时间”。
2. 在布尔运算符窗口中，展开“大于或等于”节点，键入 80 作为值，然后选择“确定”。
选择“运行”以启动跟踪。
在工具栏上，选择“停止所选跟踪”或“暂停所选跟踪”。
在“文件”菜单上，依次指向“导出”和“编写跟踪定义的脚本”，然后选择“用于 SQL 跟踪收集组”。
在 “另存为” 对话框中，在 “文件名” 框中键入要为该跟踪定义使用的名称，然后将跟踪定义保存到所需位置。在本示例中，文件名与跟踪名称 SPgt140 相同。
收到文件已经成功保存的消息后，请选择“确定”，然后关闭 SQL Server Profiler。

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

在 SQL Server Management Studio 的“文件”菜单上指向“打开”，然后选择“文件”。
在“打开文件”对话框中，找到并打开你在前面的过程中创建的文件 (SPgt140)。

将在“查询”窗口中打开保存的跟踪信息，并将其合并到可运行以创建新收集组的脚本中。
滚动查看脚本并进行以下替换（在脚本注释文本中注明）：
- 将 SQLTrace Collection Set Name Here 替换为要为收集组使用的名称。在本示例中，将收集组命名为 SPROC_CollectionSet。
- 将 SQLTrace Collection Item Name Here 替换为要为收集项使用的名称。在本示例中，将收集项命名为 SPROC_Collection_Item。
选择“执行”运行查询并创建收集组。
在对象资源管理器中，验证该收集组是否已经创建。为此，请按照下列步骤进行操作：
1. 右键单击“管理”，然后选择“刷新”。
2. 展开 “管理”，然后展开 “数据收集”。
SPROC_CollectionSet 收集组与“系统数据收集组”节点显示在同一级。默认情况下，将禁用该收集组。
使用对象资源管理器编辑 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

通过

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

创建和导出 SQL Server Profiler 跟踪

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

示例

反馈

其他资源