SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

Creating a custom data collection in SQL Server 2008

In the first post, we took a quick look at how data collection works. In this post, we will see how to create custom data collection.

To create a custom collection, you must use the stored procedures. In Bill Ramos's web cast, he hints it may stay like this i.e. NO GUI. I tend to doubt it because Microsoft built their empire making hard stuff easy.

In this example, we will look at collecting 3 key indicators of a CPU bottleneck. These items are actually included in the "server activity" system data collection. However, it cannot be modified and it collects everything under the kitchen sink. Multiply that times 20 or 100 servers and you are looking at some huge storage requirements. I think smaller data collections like this will be common.

Let's get to business...

use msdb;

 --Let's create the collection set

 Declare @collection_set_id_1 int

Declare @collection_set_uid_2 uniqueidentifier

EXEC [dbo].[sp_syscollector_create_collection_set]

      @name=N'CPU indicators',

      @collection_mode=0, --Let's start in cached mode.

      @description=N'Collects CPU KPIs from perfmon and DMVs',

      @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_1 OUTPUT,

      @collection_set_uid=@collection_set_uid_2 OUTPUT

Select @collection_set_id_1, @collection_set_uid_2

 --Let's get the needed perfmon counters

Declare @collector_type_uid_7 uniqueidentifier

Select @collector_type_uid_7 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';

Declare @collection_item_id_8 int

EXEC [dbo].[sp_syscollector_create_collection_item]

      @name=N'Perfmon CPU counters',



  <PerformanceCounters Objects="SYSTEM" Counters="Processor Queue Length" Instances="*" />     

  <PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="*" />


      @collection_item_id=@collection_item_id_8 OUTPUT,


      @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set


Select @collection_item_id_8

--Let's get the DMV data

Declare @collector_type_uid_3 uniqueidentifier

Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Generic T-SQL Query Collector Type';

Declare @collection_item_id_4 int

EXEC [dbo].[sp_syscollector_create_collection_item]


      @name=N'CPU pressure check',




      <Value>select SUM(runnable_tasks_count) from sys.dm_os_schedulers where scheduler_id >2




<Databases UseSystemDatabases="true" UseUserDatabases="true" />


      @collection_item_id=@collection_item_id_4 OUTPUT,


      @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set


Select @collection_item_id_4

--Let's start the collection

 exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id_1 --Output from sp_syscollector_create_collection_set


 We can now see that the collection has been created in SSMS. This is what it looks like:


That's it. In the next part, we will look at reporting off of this collection through tsql and SSRS.

Technorati Tags:



Blog today Blog Tommarrow said:

SQL monitoring - If you dont want to use Sql Profiler

April 23, 2010 4:34 PM

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at or
Powered by Community Server (Commercial Edition), by Telligent Systems