创建自定义收集组 - 一般 T-SQL 查询收集器类型

  • 项目

适用于：SQL Server

可以使用与数据收集器一起提供的存储过程创建包含使用一般 T-SQL 查询收集器类型的收集项的自定义收集组。 要完成此任务，需使用 SQL Server Management Studio 中的查询编辑器执行以下过程：

  • 配置上载计划
  • 定义和创建收集组
  • 定义和创建收集项
  • 验证该收集组和收集项是否存在

在创建自定义收集组之前，必须配置数据收集参数。 有关详细信息，请参阅 配置数据收集参数 (Transact-SQL)。

定义和创建收集组

  1. 使用 sp_syscollector_create_collection_set 存储过程定义一个新的收集组。

    USE msdb;
GO

DECLARE @collection_set_id INT;
DECLARE @collection_set_uid UNIQUEIDENTIFIER;

EXEC sp_syscollector_create_collection_set @name = N'DMV Test 1',
    @collection_mode = 0,
    @description = N'This is a test collection set',
    @logging_level = 1,
    @days_until_expiration = 14,
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_set_id = @collection_set_id OUTPUT,
    @collection_set_uid = @collection_set_uid OUTPUT;

SELECT @collection_set_id, @collection_set_uid;

    收集模式可以设置为 0（缓存）或 1（非缓存）。

    日志记录级别可以设置为 0、1 或 2。

    以下预配置计划随数据收集器一起提供：

    • CollectorSchedule_Every_5min
    • CollectorSchedule_Every_10min
    • CollectorSchedule_Every_15min
    • CollectorSchedule_Every_30min
    • CollectorSchedule_Every_60min
    • CollectorSchedule_Every_6h

    如果不想使用提供的这些计划，您可以创建一个新计划，然后将其用于收集组。 有关详细信息，请参阅 创建计划并将计划附加到作业。

定义和创建收集项

  1. 由于新的收集项基于已经安装的一般收集器类型，因此可以运行以下代码来设置 GUID，使其与一般 T-SQL 查询收集器类型相对应。

    DECLARE @collector_type_uid UNIQUEIDENTIFIER;

SELECT @collector_type_uid = collector_type_uid
FROM [msdb].[dbo].[syscollector_collector_types]
WHERE name = N'Generic T-SQL Query Collector Type';

DECLARE @collection_item_id INT;

  2. 使用 sp_syscollector_create_collection_item 存储过程创建收集项。 声明收集项的架构，这样它将映射到一般 T-SQL 查询收集器类型所需的架构。

    EXEC sp_syscollector_create_collection_item @name = N'Query Stats - Test 1',
    @parameters = N'
        <ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
        <Query>
        <Value>SELECT * FROM sys.dm_exec_query_stats</Value>
        <OutputTable>dm_exec_query_stats</OutputTable>
        </Query>
        </ns:TSQLQueryCollector>',
    @collection_item_id = @collection_item_id OUTPUT,
    @frequency = 5,
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid;

SELECT @collection_item_id;

验证新的收集组和收集项是否存在

  1. 在启动新的收集组之前，运行以下查询以验证新的收集组及其收集项是否已创建。

    USE msdb;
GO

SELECT * FROM syscollector_collection_sets;
SELECT * FROM syscollector_collection_items;
GO

    还可以在 SQL Server Management Studio 中执行目视检查。 在对象资源管理器中，展开 “管理” 节点，然后展开 “数据收集”。 将显示新的收集组。 收集组的红色圆圈图标指示该收集组已停止。

示例

下面的代码示例汇集了上面步骤中记录的示例。 为收集项设置的收集频率（5 秒钟）将被忽略，因为收集组的收集模式设置为 0，即缓存模式。 有关详细信息，请参阅数据收集。

USE msdb;
GO

DECLARE @collection_set_id INT;
DECLARE @collection_set_uid UNIQUEIDENTIFIER;

EXEC dbo.sp_syscollector_create_collection_set @name = N'DMV Stats Test 1',
    @collection_mode = 0,
    @description = N'This is a test collection set',
    @logging_level = 1,
    @days_until_expiration = 14,
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_set_id = @collection_set_id OUTPUT,
    @collection_set_uid = @collection_set_uid OUTPUT;

SELECT @collection_set_id, @collection_set_uid;

DECLARE @collector_type_uid UNIQUEIDENTIFIER;

SELECT @collector_type_uid = collector_type_uid
FROM syscollector_collector_types
WHERE name = N'Generic T-SQL Query Collector Type';

DECLARE @collection_item_id INT;

EXEC sp_syscollector_create_collection_item @name = N'Query Stats - Test 1',
    @parameters = N'
    <ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
    <Query>
      <Value>select * from sys.dm_exec_query_stats</Value>
      <OutputTable>dm_exec_query_stats</OutputTable>
    </Query>
    </ns:TSQLQueryCollector>',
    @collection_item_id = @collection_item_id OUTPUT,
    @frequency = 5, -- This parameter is ignored in cached mode
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid;

SELECT @collection_item_id;
GO

相关内容