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
@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,
@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.
posted @ Tuesday, March 04, 2008 10:32 AM by Technical Musings
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail