Microsoft SQL Server 2012 Bible

Creating Custom Data Collector Sets

The three system data collector sets discussed previously are pre-defined 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 fragmentation 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 correlate 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 defined using T-SQL scripts and executing system stored procedures as follows:

1. Configure 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 configured in the Management Data Warehouse Wizard under the section titled “Configuring MDW” earlier in this chapter.

2. To configure 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';

3. Create the collection set using the sp_syscollector_create_collection_set ...