Create a Custom Collection Set That Uses the Generic T-SQL Query Collector Type (Transact-SQL)

You can create a custom collection set with collection items that use the Generic T-SQL Query collector type by using the stored procedures that are provided with the data collector. Accomplishing this task involves using Query Editor in SQL Server Management Studio to carry out the following procedures:

• Configure upload schedules.

• Define and create the collection set.

• Define and create a collection item.

• Verify that the collection set and collection items exist.

Define and create the collection set

• Define a new collection set using the sp_syscollector_create_collection_set stored procedure.

  USE msdb;
  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;
  

  The collection mode can be set to either 0 (cached) or to 1 (non-cached).

  The logging level can be set to 0, 1 or 2.

  The following preconfigured schedules are provided with the data collector:

  • CollectorSchedule_Every_5min

  • CollectorSchedule_Every_10min

  • CollectorSchedule_Every_15min

  • CollectorSchedule_Every_30min

  • CollectorSchedule_Every_60min

  • CollectorSchedule_Every_6h

  If you do not want to use one of the schedules that are provided, you can create a new schedule and use it for the collection set. For more information, see Create and Attach Schedules to Jobs.

Define and create a collection item

1. Because the new collection item is based on a generic collector type that is already installed, you can run the following code to set the GUID to correspond to the Generic T-SQL Query collector type.

   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. Use the sp_syscollector_create_collection_item stored procedure to create the collection item. Declare the schema for the collection item so it maps to the schema that is required for the Generic T-SQL Query collector type.

   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;
   

Verify that the new collection set and collection item exist

• Before starting the new collection set, run the following query to verify that the new collection set and its collection item have been created.

  USE msdb;
  SELECT * FROM syscollector_collection_sets;
  SELECT * FROM syscollector_collection_items;
  GO
  

  You can also do a visual check in SQL Server Management Studio. In Object Explorer, expand the Management node, and then expand Data Collection. The new collection set will be displayed. The red circle icon for the collection set indicates that the collection set is stopped.

Example

The following code sample combines the examples that are documented in the previous steps. Note that the collection frequency that is set for the collection item (5 seconds) is ignored because the collection set collection mode is set to 0, which is cached mode. For more information, see Data Collection.

USE msdb;

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

See Also

Reference

Data Collector Stored Procedures (Transact-SQL)

Manage Schedules

Concepts

Start or Stop a Collection Set