15.4. Custom collection sets

You can define additional collection sets that draw information from a number of different sources, using one of three collector types: T-SQL Query, SQL Trace, and Performance Counter. A fourth collector type, Query Activity, collects the same information as the Query Statistics system collection set.

Creating custom collection sets requires the use of a number of stored procedures including sp_syscollector_create_collection_set and sp_syscollector_create_collection _item. Unfortunately, you cannot create custom collection sets using the Management Studio interface. However, once they are created, you can manage their upload configuration and schedule using Management Studio as with the system collection sets.

You can create custom collection sets for a variety of purposes, including the following:

• Longer-term storage of DMVs such as the missing index DMVs. As we covered in chapter 13, these DMVs are limited to the last 500 missing indexes reported by the query optimizer, so storing a regular snapshot of these DMVs in the MDW can overcome this limitation.

• Creating a customized compliance solution with policy-based management, for example, uploading the results of policy checks from multiple servers to the central MDW for a centralized compliance-reporting solution.

• Collecting customized Performance Monitor counters as part of a broader baseline. Despite the Server Activity system collection set including a number of performance counters, additional counters not included in this set may be required.

A full description of the process and procedures for creating a custom collection set is found in SQL Server Books Online. A brief example in listing 15.1 demonstrates the T-SQL code for creating a custom set called Performance Baseline, which includes a number of performance counters.

Example 15.1. Creating a custom collection set for performance counters

-- Create a Custom Collection Set containing three Performance Counters
use msdb;

declare @collection_set_id_1 int
declare @collection_set_uid_2 uniqueidentifier

exec [dbo].[sp_syscollector_create_collection_set]
 @name=N'Performance Baseline'
 , @collection_mode=0
 , @description=N'Custom Performance Counters'
 , @target=N''
 , @logging_level=0
 , @days_until_expiration=5
 , @proxy_name=N''
 , @schedule_name=N'CollectorSchedule_Every_5min'
 , @collection_set_id=@collection_set_id_1 OUTPUT
 , @collection_set_uid=@collection_set_uid_2 OUTPUT

declare @collector_type_uid_3 uniqueidentifier
select @collector_type_uid_3 = collector_type_uid
from [dbo].[syscollector_collector_types]
Where name = N'Performance Counters Collector Type'

declare @collection_item_id_4 int
exec [dbo].[sp_syscollector_create_collection_item]
 @name=N'PerfCounters'
 , @parameters=

'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">

  <PerformanceCounters Objects="$(INSTANCE):Buffer Manager" Counters="Page
   
 life expectancy" />

  <PerformanceCounters Objects="$(INSTANCE):General Statistics"
   
 Counters="User Connections" />

  <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Read"
   
 Instances="*" />

</ns:PerformanceCountersCollector>'
 , @collection_item_id=@collection_item_id_4 OUTPUT
 , @frequency=5
 , @collection_set_id=@collection_set_id_1
 , @collector_type_uid=@collector_type_uid_3

Once the custom set is created using the code in listing 15.1, it will appear in Management Studio as a new collection set, at which point you can configure its upload settings in the same manner as we covered earlier for system data collection sets.

Finally, the benefit of collecting and uploading data is, of course, to enable reporting on it, and in that regard a number of powerful reports are included to work with the system collection sets.