Creating a custom data collection in SQL Server 2008

Posted by Jason on Friday, February 15, 2008 to SQL Server 2008, Data collection
763 Views | 1 Comments |

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',

      @parameters=N'

<PerformanceCountersCollector>

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

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

</PerformanceCountersCollector>',

      @collection_item_id=@collection_item_id_8 OUTPUT,

      @frequency=5,

      @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set

      @collector_type_uid=@collector_type_uid_7

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',

      @parameters=

N'<TSQLQueryCollector>

<Query>

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

      </Value>

      <OutputTable>CPU_Pressure_OS_Schedulers</OutputTable>

</Query>

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

</TSQLQueryCollector>',

      @collection_item_id=@collection_item_id_4 OUTPUT,

      @frequency=5,

      @collection_set_id=@collection_set_id_1, --Output from sp_syscollector_create_collection_set

      @collector_type_uid=@collector_type_uid_3

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.