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:
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';
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.