SQL 2008 Data Collector - Custom Performance Counter Set

During my session "Server Monitoring Made Simple with SQL 2008" I covered the new Performance Studio, Management Data Warehouse, and Data Collectors in SQL Server 2008. Someone asked how one would create a custom Performance Counter Collection and I wasn't prepared to answer that particular question. However, I provided my blog link in my slide deck, and I said I would find the answer and post it, so hopefully that person actually reads my blog at some point, but if not, maybe someone else will benefit from this example.

If you were to do a search online, you will find a number of examples that don't work and generate a XML error when you try to run the code. The reason for this, is that almost every example I could find was written for CTP6 of SQL Server 2008. I was able to figure out the proper XML namespace reference that is needed in the RTM of SQL Server 2008 to create a custom collector.

Before you actually go out and create a completely custom data collector for performance counters, you should consider something. The System Activity Collection Set that is one of the System Collection Sets, already includes over 60 performance counters as a collection item. You can see what ones are already included by running the following query:

SELECT name, frequency, parameters
FROM syscollector_collection_items
WHERE name = 'Server Activity - Performance Counters'

Since the definition of the collection items is XML based, you can click the XML Document and it will open in a new window with friendly formatting. If you find that the default counters don't meet your specific needs, then you can use the following example to create a custom collection set:

use msdb;
--First create the collection set
declare @collection_set_id int
declare @collection_set_uid uniqueidentifier

exec [dbo].[sp_syscollector_create_collection_set]
   @name=N'Performance Counter Collection Set',
   @collection_mode=0, --Let's start in cached mode.
   @description=N'Collects Performance Counters from PerfMon',
   @target=N'', --Undocumented
   @logging_level=0, --0 through 2 are valid
   @days_until_expiration=5, --Let's just keep data 5 days. We will rollup for reporting.
   @proxy_name=N'', --Use if you want it to run under something other than the SQL Agent svc account.
   @schedule_name=N'CollectorSchedule_Every_5min', --Built in schedule
   @collection_set_id=@collection_set_id output,
   @collection_set_uid=@collection_set_uid output

-- Now create the collection item for the Performance Counters to be collected
declare @collector_type_uid uniqueidentifier
declare @collection_item_id int

select @collector_type_uid = collector_type_uid
from [dbo].[syscollector_collector_types]
where name = N'Performance Counters Collector Type';


exec [dbo].[sp_syscollector_create_collection_item]
   @name=N'Standard SQL Server Performance Counters',
   @parameters=N'
<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
 <PerformanceCounters Objects="Processor" Counters="% Processor time" Instances="_Total" />
 <PerformanceCounters Objects="Processor" Counters="% Privileged time" Instances="_Total" />

 <PerformanceCounters Objects="Memory" Counters="Available KBytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Pages/sec" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Committed Bytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Commit limit" Instances="*"  />

 <PerformanceCounters Objects="System" Counters="Processor Queue Length" Instances="*"  />
 <PerformanceCounters Objects="System" Counters="Context Switches/sec" Instances="*"  />

 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk Queue Length" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Read" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Write" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Reads/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Writes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Read Bytes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Write Bytes/sec" Instances="_Total" />

 <PerformanceCounters Objects="Process" Counters="% Processor time" Instances="sqlservr" />

 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Buffer Cache hit ratio" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Checkpoint pages/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Page life expectancy" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Total Server Memory (KB)" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Target Server Memory(KB)" Instances="*" />
          
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="Batch requests/sec" Instances="*"  />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Compilations/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Databases" Counters="Transactions/sec" Instances="_Total" />
 <PerformanceCounters Objects="SQLServer:Databases" Counters="Data File(s) Size (KB)" Instances="_Total" />

 <PerformanceCounters Objects="SQLServer:General Statistics" Counters="User Connections" Instances="*" />
</ns:PerformanceCountersCollector>',
   @collection_item_id=@collection_item_id output,
   @frequency=5,
   @collection_set_id=@collection_set_id, --output from sp_syscollector_create_collection_set
   @collector_type_uid=@collector_type_uid

--Start the collection
exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id --output from sp_syscollector_create_collection_set

Some thanks must go out to Jason Massie whose original post on this topic was the original foundation for the modified script above. You can find his original post titled Creating a custom data collection in SQL Server 2008 on his blog http://www.statisticsio.com. It is one of the entries that was written for CTP6, but provided the foundation for what I have posted above, so I have to give credit where due.