• 项目

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

在 SQL Server 2008 中，可以利用 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. 在**“显示选项”区域中，确保清除“在‘持续时间’列中以微秒为单位显示值(仅限 SQL Server 2005 或更高版本)”**复选框。

    3. 单击**“确定”关闭“常规选项”**对话框。

  4. 在**“文件”菜单上，单击“新建跟踪”**。

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

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

  6. 在**“常规”**选项卡上，请执行下列操作：

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

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

  7. 在**“事件选择”**选项卡上，请执行下列操作：

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

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

    3. 单击 SP:Completed 行。

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

  8. 在右下角，单击**“列筛选器”以打开“编辑筛选器”对话框。在“编辑筛选器”**对话框中，请执行下列操作：

    1. 在筛选器列表中，单击**“持续时间”**。

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

  9. 单击**“运行”**以启动跟踪。

  10. 在工具栏上，单击**“停止所选跟踪”或“暂停所选跟踪”**。

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

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

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

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

  1. 在 SQL Server Management Studio 的**“文件”菜单上，指向“打开”，然后单击“文件”**。

  2. 在**“打开文件”**对话框中，找到并打开您在前面的过程中创建的文件 (SPgt80)。

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

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