Microsoft® SQL Server® 2012 Bible

989

Chapter 43: Management Data Warehouse

43

Creating Custom Data Collector Sets

The three system data collector sets discussed previously are pre-defi ned data collectors of

some of the key metrics in SQL Server that every DBA should be familiar with. Additional data

collectors can be created and scheduled to collect server activity and performance counters.

For example, a custom data collector could be created to capture clustered index fragmen-

tation percentage levels of all databases in a SQL Server instance at different intervals

throughout the day. This type of information can help database administrators to corre-

late and trend index fragmentation percentage levels with day-to-day data manipulation

operations.

There is no user interface to create custom data collectors but one can be easily defi ned

using T-SQL scripts and executing system stored procedures as follows:

1. Confi gure data collection parameters, including the instance name, database name,
   and cache directory. This step is necessary only if these parameters have not been
   set previously. These parameters were confi gured in the Management Data Warehouse
   Wizard under the section titled “Confi guring MDW” earlier in this chapter.
   To confi gure data collection parameters you use the following T-SQL commands:
   USE msdb;
   EXEC sp_syscollector_set_warehouse_instance_name 'SQL2012RTM';
   EXEC sp_syscollector_set_warehouse_database_name 'MDW';
   EXEC sp_syscollector_set_cache_directory 'C:\temp';


2. Create the collection set using the sp_syscollector_create_collection_set
   system stored procedure as follows:
   USE msdb;
   DECLARE @collection_set_id int;
   DECLARE @collection_set_uid uniqueidentifier;
   EXEC sp_syscollector_create_collection_set
   @name=N'Clustered Index Fragmentation Pct',
   @collection_mode=1,
   @description=N'Collects fragmentation percentages of all Clustered Indexes
   over 10%',
   @logging_level=1,
   @days_until_expiration=14,
   @schedule_name=N'CollectorSchedule_Every_60min',
   @collection_set_id=@collection_set_id OUTPUT,
   @collection_set_uid=@collection_set_uid OUTPUT;
   SELECT @collection_set_id, @collection_set_uid;


3. Create the collection item using the sp_syscollector_createcollection
   item system stored procedure as follows:
   DECLARE @collector_type_uid uniqueidentifier;

c43.indd 989c43.indd 989 7/31/2012 10:18:46 AM7/31/2012 10:18:46 AM

http://www.it-ebooks.info