cdc performance turning and monitor

来源：这里教程网时间：2026-03-02 11:12:19 作者：

https://msdn.microsoft.com/en-us/library/dd266396.aspx
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/administer-and-monitor-change-data-capture-sql-server#Capture

USE msdb;

DECLARE @schedule_uid uniqueidentifier;
DECLARE @collection_set_id int;

DECLARE @cid int;

DECLARE @pp xml = CONVERT(xml,
N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>
<OutputTable>cdc_log_scan_data</OutputTable>
</Query>
</ns:TSQLQueryCollector>');

-- Collect and upload data every 5 minutes
SELECT @schedule_uid = (
SELECT schedule_uid from sysschedules_localserver_view
WHERE name = N'CollectorSchedule_Every_5min')

EXEC dbo.sp_syscollector_create_collection_set
@name = N' CDC Performance Data Collector',
@schedule_uid = @schedule_uid,
@collection_mode = 0,
@days_until_expiration = 30,
@description = N'This collection set collects CDC metadata',
@collection_set_id = @collection_set_id output;

print @collection_set_id ;

-- Create a collection item using statistics from
-- the change data capture dynamic management view.

EXEC dbo.sp_syscollector_create_collection_item
@collection_set_id = @collection_set_id,
@collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',
@name = ' CDC Performance Data Collector',
@frequency = 5,
@parameters = @pp,
@collection_item_id = @cid output;
print @cid
GO

--sp_syscollector_delete_collection_item @name = N' CDC Performance Data Collector'
--sp_syscollector_delete_collection_set @name = N' CDC Performance Data Collector'