建立使用一般 T-SQL 查詢收集器型別的自訂收集組 (Transact-SQL)

發行項
06/25/2013

您可以使用資料收集器所提供的預存程序，建立包含使用一般 T-SQL 查詢收集器型別之收集項的自訂收集組。完成這項工作需要在 SQL Server Management Studio 中使用查詢編輯器來進行以下程序：

設定上傳排程。
定義和建立收集組。
定義和建立收集項。
確認收集組與收集項確實存在。

[!附註]

在您建立自訂收集組之前，必須先設定資料收集參數。如需詳細資訊，請參閱＜設定資料收集參數 (Transact-SQL)＞。

定義和建立收集組

使用 sp_syscollector_create_collection_set 預存程序定義新的收集組。
```
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;
```
收集模式可以設定為 0 (快取) 或 1 (非快取)。

記錄層級可以設定為 0、1 或 2。

下面是資料收集器所提供的預先設定排程：
- CollectorSchedule_Every_5min
- CollectorSchedule_Every_10min
- CollectorSchedule_Every_15min
- CollectorSchedule_Every_30min
- CollectorSchedule_Every_60min
- CollectorSchedule_Every_6h
如果您不想要使用所提供的其中一個排程，可以建立新的排程，然後將它用於收集組。如需詳細資訊，請參閱＜建立及附加排程至作業＞。

定義和建立收集項

因為新的收集項是以已經安裝的一般收集器型別為基礎，所以您可以執行下列程式碼，將 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;

使用 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;

確認新的收集組與收集項確實存在

在啟動新的收集組之前，請執行下列查詢來確認新的收集組和它的收集項確實已經建立。
```
USE msdb;
SELECT * FROM syscollector_collection_sets;
SELECT * FROM syscollector_collection_items;
GO
```
您也可以在 SQL Server Management Studio 中進行目視檢查。在 [物件總管] 中，展開 [管理] 節點，然後展開 [資料收集]。將會顯示新的收集組。收集組的紅色圓圈圖示是表示此收集組已經停止。

範例

下列程式碼範例會結合上述步驟所列的範例。請注意，這時針對收集項所設定的收集頻率 (5 秒) 將會遭到忽略，因為收集組的收集模式設定為 0，而這是快取模式。如需詳細資訊，請參閱＜資料收集＞。

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

請參閱

參考

資料收集器預存程序 (Transact-SQL)

管理排程

概念

啟動或停止收集組

共用方式為

建立使用一般 T-SQL 查詢收集器型別的自訂收集組 (Transact-SQL)

定義和建立收集組

定義和建立收集項

確認新的收集組與收集項確實存在

範例

請參閱

參考

概念

其他資源