SQL Server performance optimization custom dynamic performance collection (iv)

To configure the Data collector:

1. Create a login and map roles

2. Configuring the Management Data Warehouse

3. Create collection sets, collection items----msdb data store sp_syscollector_create ...

4. Automatically configure the related job

Specific steps:

After creating the diagram:

To configure Data collection:

The refresh looks like this:

The method created:

Use msdbgodeclare @collectionsetid int   --there is a return value of exec sp_syscollector_create_collection_set @name = ' performance Collection set ',--collection of patterns  buffer mode @collection_mode=0,--Invalid date @days_until_expiration=7,--scheduled time @schedule_name= ' Collectorschedule_every_ 15min ', @description = ' The main collection of dynamic management view performance data with counter correlation energy data ',--collection set of Id@[email protected] output--storage interval Table SELECT * FROM Sysschedules

Specific usage reference:

Https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=ZH-CN&k=k (Sp_syscollector_create_ Collection_set_tsql); K (SQL12. SWB. Tsqlresults. F1); K (SQL12. SWB. Tsqlquery. F1); K (miscellaneousfilesproject); K (Devlang-tsql) &rd=true

SQL Server performance optimization custom dynamic performance collection (iv)