cdc performance turning and monitor

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'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16131092/viewspace-2143563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16131092/viewspace-2143563/