Collecting baseline data is essential. In my previous article I introduced my Free Comprehensive Baseline Collector Solution. I gave an overview about:

• Why baseline data is essential
• The rules I follow during developing the solution
• The collectors themselves, how the collectors work, what data are collected 

I am glad to introduce the new version of the Baseline Collector Solution. The new version does not only differ in bugfixes from the previous version (of course it contains all bugfixes which are in V1.2), but there are also some very cool and useful new features and impovements in BCS V2.

In this article I am introducing two of them:

• Reworked - sp_CollectFileInfo
• Central Configuration 

To check all the improvements and features please see the full Change History at the end of the article.

The Rules

The most important thing to understand is the rules, so let's review them:

1. Collect only raw data 

   The Solution collects and stores raw data. No calculations, no analysis.

2. Collect as little data as possible:

   Collecting baseline continuously requires a lot of storage space. So I tried to collect as little data as possible.

3. Be as flexible as possible:

   The Solution has its own standard, but it is still easily customizable. It tries to achieve a 'golden mean' between being extremely standard and being extremely customizable.

The Solution

One of the most important changes is that SQL Server 2005 is not supported anymore, only SQL Server 2008 and above. The reason behind this decision is that I wanted the Solution to be more accurate, so it was necessary to change the DATETIME data type to DATETIME2.

sp_CollectFileInfo - Reworked

The first version of the Solution works with two types of data concerning the volatility of the data: static and dynamic

Static: Data are considered to be static when they change very rarely, like instance configurations. The collectors which collect static data are the following: sp_CollectInstanceData, sp_CollectConfigData and sp_CollectDatabaseInfo These collectors collect and store data in a very effective way by only storing the data which are changed compared to the data stored or the data which are missing for any reason. So only the changed or missing data are collected and stored even if the collectors run every hour or so.

Dynamic: Data are considered to be dynamic when they change very often, like IO virtual file statistics or the wait statistics. Dynamic data can be extremely volatile like performance counters. These data need to be collected frequently to have understandable, analysable, straight and valid trends. The collectors which collect dynamic data are the following: sp_CollectIOVFStats, sp_PerfmonData, sp_CollectWaitStats* and sp_CollectTempDBUsage

*Although wait statistics don't need to be collected very frequently, however this is not because they are not dynamic, but because of the nature of the data, the way we analyze them!

I spent some time thinking this problem over and trying to recategorize the data collected according to the rules followed. I tried to find some area to improve. Finally, I managed to identify a third type. Let's call it semi-dynamic.

Semi-dynamic: Data are considered to be semi-dynamic when they change sometimes or even very often, but they are not necessarily needed every single time. It is possible to specify some kind of thresholds which allow you to decide whether the data are needed or not.

sp_CollectFileInfo has been totally reworked in a way that it can handle database files' data as semi-dynamic data based on different kind of thresholds. It got 3+1 new parameters which allow you to control this behaviour.

@CollectionTimeThreshold: With the @ColletionTimeThreshold parameter you can specify a time threshold in hours. If it is used, data collection is only performed on those data (database files) whose time of the last collection date reaches the threshold specified. 

@ChangedSizeThreshold: With the @ChangedSizeThreshold parameter you can specify the threshold for the file size change in percentage. If it is used, data collection is only performed on those data (database files) whose size changed at least by the specified percentage.

You can think of these @%Threshold parameters as switches that you can turn on or off. They are not mutually exclusive, so you can turn on only just one of them or even all of them.

For example, you would like to collect database file data only for those files whose size are changed by 25% and you would also like to have database file data at least every 3 days. The only thing you need to do in this case is to configure the parameter @ChangedSizeThreshold to 25 and @CollectionTimeThreshold to 72.

Please notice that regardless of the state of the switches, data collection is always performed on database files whose physical location were changed since their last collection time.

Four indicator columns were also added to the [dbo].[file_info] and the [dbo].[ts_file_info] tables which let you know what triggered the data collection.

• ic_cst: indicator column for parameter @ChangedSizeThreshold. NULL means that the switch is disabled, so the condition are ignored, 0 means that it is enabled but the condition were not met, 1 means that it is enabled and the condition were met
• ic_csut: indicator column for parameter @ChangedSpaceUsedThreshold. NULL means that the switch is disabled, so the condition are ignored, 0 means that it is enabled but the condition were not met, 1 means that it is enabled and the condition were met
• ic_ctt:  indicator column for parameter @CollectionTimeThreshold. NULL means that the switch is disabled, so the condition are ignored, 0 means that it is enabled but the condition were not met, 1 means that it is enabled and the condition were met
• ic_path: indicator column for physical location change. 1 means that the file physical location was changed, 0 means that it wasn't.

Please notice that there is no indicator column for parameter @CSUTExcludeLog.

Central Configuration

Besides efficiency (using less storage) the other important improvement of BCS V2 is convenience, i.e. 'ease of use'. So let's talk about the second best feature of BCS V2, which is the Central Configuration Feature.

Every collector has a new parameter called @CustomConfig. This new parameter refers to a predefined configuraion of the specific collector which is stored in table [dbo].[bcs_config] using column [config_name]. Every collector has a default configuration which is named by its name. These default rows cannot be deleted from table [dbo].[bcs_config] and the config names of the default configurations cannot be modified. So basically the default configurations store the default values of the parameters of the collectors. 

You can easily create a new set of predefined values (configuration) for any of the collectors. For this there is only one single puzzle you need to know. Every configuration has a type based on its supported collector. Here you can see the types of the collectors:

Here you can see the parameters supported by the types.

There is one additional type as you can see, this is type 0 which is a general type. Custom configurations defined with type 0 can support every collector. Keep in mind that the collectors don't check the type of the provided custom config. Supportability means that the columns (~parameters) can only be modified if the type of the custom config supports the collector, so the collector has that specific parameter.

In addition, you don't need to maintain table [dbo].[bcs_config] manually using insert, update, delete statements. You can use sp_configureBCS stored procedure.

Syntax:

Creating a new custom config
sp_ConfigureBCS @Action = {'new' | 'create' | 'insert'}
,@NewConfigName = 'config_name'
[ ,<config_parameter_specifications> ]
[ ,<collector_parameter_specifications> ] 

Duplicating an existing custom config
sp_ConfigureBCS @Action = {'clone' | 'duplicate' | 'copy'}
,@ConfigName = 'existing_config_name'
[, @NewConfigName = 'new_config_name']
[ ,<config_parameter_specifications> ]
[ ,<collector_parameter_specifications> ]

Modifying custom config
sp_ConfigureBCS @Action = {'update' | 'modify'}
,@ConfigName = 'existing_config_name'
[ ,@NewConfigName = 'new_config_name' ]
[ ,<config_parameter_specification> ]
[ ,<collector_parameter_specifications> ]

Deleting custom config
sp_ConfigureBCS @Action = {'delete' | 'del' | 'remove' | 'drop'}
,@ConfigName = 'existing_config_name'

Listing custom configs
sp_ConfigureBCS @Action = 'list'

Getting help
sp_ConfigureBCS @Action = 'help'

<config_parameter_specifications>::=
[ ,@ConfigType = {0 | 1 | 2 | 3 | 4 | 5 | 6} ]
[ ,@ConfigDescription = 'config_description' ]
<collector_parameter_specifications>::=
[ ,@Retention = retention ]
[ ,@LogInfo = loginfo ]
[ ,@DestTable = 'desttable' ]
[ ,@DestSchema = 'destschema' ]
[ ,@TSMode = tsmode ]
[ ,@EmptyTSTable = emptytstable ]
[ ,@CollectingInterval = collectinginterval ]
[ ,@Sampleinterval = sampleinterval ]
[ ,@MeasuringInterval = measuringinterval ]
[ ,@ResetWaitStats = resetwaitstats ]
[ ,@BypassNonActiveSrvConfError = bypassnonactivesrvconferror ]
[ ,@CollectionTimeThreshold = collectiontimethreshold ]
[ ,@ChangedSizeThreshold = changedsizethreshold ]
[ ,@ChangedSpaceUsedThreshold = changedspaceusedthreshold ]
[ ,@CSUTExcludeLog = csutexcludelog ]

If you would like to create a new custom config you have two options. You can create a new config or you can create a copy from one of the existing configs. In the first case, every parameter which is not explicitly provided will get a default value of the specified type. If you don't specify the type it will be type 0. In case of duplicating one of the existing config the parameters which are not explicitly provided will get the value of the config to be duplicated. If you don't provide the new config name it will get 'cl_' prefix plus the original config's name

As you can see in the syntax description, you can also list, modify or delete a config. 

Please test your custom configs carefully, since there is not any parameter verification implemented in sp_ConfigureBCS yet.

So far that's all. This is Baseline Collector Solution V2. I hope you will all find the new version useful.

Future plans

Creating stored procedure for collecting Memory Usage Data is still on my list so as the set of stored procedures for analyzing and evaluating the data collected (Analyzing Part of the Solution).

Please report me every bug you may find so I can fix it as soon as I can.

Ideas are always welcome!

Keep checking the updates on SQLApprentice.net. If you have any remarks, questions or any ideas, please feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Download Baseline Collector Solution V2

Baseline Collector Solution V2 

Change History

/*********************************************************************************************