SQL Server 2008 data collection sets
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
