Home

Using Performance Data Collection

Main Menu

Resources

Polls

Who administers your corporate database infrastructure?

	Own IT department
	Single full time employee
	IT outsourcing company
	A company similar to DB-Staff

Setup Management Data Warehouse

Management Data Warehouse is the repository for the performance data to be collected in other words it is a user database on SQL Server 2008 instance.

Go to SQL Server Management Studio Management \ Data Collection right click and choose Configure Management Data Warehouse.

A Wizard will show up. Click next. On Select configuration task page choose Create or upgrade a management data warehouse. Click Next

Then you will need to choose a database as management data warehouse storage or create new one, for instance DataCollection.

Next page will prompt you to map existing sql server logins to management data warehouse roles. There are 3 possible roles

mdw_admin – user has full access over management data warehouse: change data warehouse schema (previously created database), run maintenance tasks over mdw such as archive and clean up, read write delete update access to mdw tables.
mdw_reader - has read-only access to historical performance data for troubleshooting.
mdw_writer – can upload and write data into warehouse. Any collector using mdw to store data must be member of this role.

These roles are stored in msdb. More information about management data warehouse security may be found here.

Start from adding built in administrators to mdw_admin role. Click Next and then Finish.

Now call the Configure Management Data Warehouse Wizard again (right click on Management \ Data Collection) but this time choose Set up data collection radio button

On the next page connect to your sql server and choose the management data warehouse database created earlier. You can leave cache directory field blank. Then click finish.

If you have done everything correctly you will see new nodes under Data Collection node in SSMS.

As you can see system data collection sets include disk usage, query statistics and server activity. Right clicking on a data collection set gives you opportunity to Start / Stop, Script as Create/ Drop, Initiate immediate collecting and upload to mdw (Collect and Upload Now).

On instances using data collections SQL Server Agent must be started and the account SQL Server Agent logs on as must be mapped to mdw_writer role otherwise collectors’ uploads will fail. You can see that Configure MDW Wizard created new jobs to collect the data

Custom data collection set

Now after you have set up management data warehouse you can create custom data collection sets There are four types of collectors which you may query using

select * from msdb.dbo.syscollector_collector_types

This query returns four types of collectors

Generic T-SQL Query Collector Type
Generic SQL Trace Collector Type
Query Activity Collector Type
Performance Counters Collector Type

You can see that three of them have not null field parameter_schema which is xml schema declaration. The purpose of this XSD will be described further.

Unfortunately Microsoft does not provide graphic interface for design and creation of collectors. Except automatically created system data collection sets (disk usage, query statistics and server activity) you will need to use scripts to create a data collector. The stored procedure that creates data collection set is sp_syscollector_create_collection_set and its syntax is

sp_syscollector_create_collection_set

          [ @name = ] 'name'

        , [ [ @target = ] 'target' ]

        , [ [ @collection_mode = ] collection_mode ] –- 0 – cached mode (collect results into temp folder before upload), 1- non-cached mode

        , [ [ @days_until_expiration = ] days_until_expiration ]

        , [ [ @proxy_id = ] proxy_id ]

        , [ [ @proxy_name = ] 'proxy_name' ]

        , [ [ @schedule_uid = ] 'schedule_uid' ]

        , [ [ @schedule_name = ] 'schedule_name' ] –- a name from sysschedules

        , [ [ @logging_level = ] logging_level ] –- 0 – minimal logging, 1- average and 2- maximal logging

        , [ [ @description = ] 'description' ]

        , [ @collection_set_id = ] collection_set_id OUTPUT

        , [ [ @collection_set_uid = ] 'collection_set_uid' OUTPUT ]

All its params are self-descriptive and you see that mandatory are only two of them – name and collection set id. For more information about these parameters see BOL article. Let’s create our own collection set using next query

USE msdb;

GO

DECLARE @collection_set_uid uniqueidentifier;

DECLARE @collection_set_id int;

SET @collection_set_uid = NEWID();

EXEC dbo.sp_syscollector_create_collection_set

    @name = N'SQLTeam collection set',

    @collection_mode = 0, -- cached mode

    @days_until_expiration = 365,

    @description = N'Collection set example from sqlteam.com which runs in cached mode',

    @logging_level = 2,

    @schedule_name = N'CollectorSchedule_Every_5min',

    @collection_set_id = @collection_set_id OUTPUT, -- this number will be used in the next script to create collection set items

    @collection_set_uid = @collection_set_uid OUTPUT;

select @collection_set_uid as collection_set_uid

select @collection_set_id as collection_set_id

Thus we created a custom collection set which can be visible in SSMS under data collection node or using query

select * from msdb..syscollector_collection_sets

Now we need to add collection items to this data collection set. This step depends on the type of collector we want to add. Collection items can only be added using T-SQL via stored procedure sp_syscollector_create_collection_item. Its syntax is as following

sp_syscollector_create_collection_item

            [ @collection_set_id = ] collection_set_id

        , [ @collector_type_uid = ] 'collector_type_uid'

        , [ @name = ] 'name'

        , [ [ @frequency = ] frequency ]

        , [ @parameters = ] 'parameters'

        , [ @collection_item_id = ] collection_item_id OUTPUT

@collection_set_id is the identifier of collection set we have created before

@collector_type_uid unique id of collector type that may be looked up in syscollector_collector_types

@frequency is optional parameter that defines time in seconds which is the interval between uploads.

@parameters is xml parameter which is of most interest for us. This xml parameter schema must be valid to the syscollector_collector_types. parameter_schema (we were talking about above) for the given collector_type_uid. For instance for Query Activity Collector Type this parameter should be NULL. Let’s discus every collector type.

Generic T-SQL Query Collector Type

This type allows you to use Transact-SQL select query or stored procedure call to determine the data to be collected.

For instance we will create a data collection item that collects information about space used by tables.

USE msdb;

GO

DECLARE @collection_item_id int;

DECLARE @collection_set_id int = (SELECT collection_set_id

                                  FROM syscollector_collection_sets

                                  WHERE name = N'SQLTeam collection set');

DECLARE @collector_type_uid uniqueidentifier =

    (SELECT collector_type_uid

     FROM syscollector_collector_types

     WHERE name = N'Generic T-SQL Query Collector Type');

DECLARE @params xml =

    CONVERT(xml, N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">

            <Query>

                <Value>

                    select db_name() as databasename

                        , OBJECT_NAME(object_id) as objname

                        , SUM (reserved_page_count) * 8192/ 1024 as reserved_kb

                        , SUM(used_page_count) * 8192 / 1024 as used_kb

                    from sys.dm_db_partition_stats

                    group by OBJECT_NAME(object_id)

                    order by reserved_kb desc

                </Value>

                <OutputTable>TableSpaceDistribution</OutputTable>

            </Query>

            <Databases>

                <Database> UseSystemDatabases = "false"

                           UseUserDatabases = "true"

                </Database>

            </Databases>

         </ns:TSQLQueryCollector>');

EXEC sp_syscollector_create_collection_item

    @collection_set_id = @collection_set_id,

    @collector_type_uid = @collector_type_uid,

    @name = 'SQLteam custom TSQL query collector item',

    @frequency = 10000,

    @parameters = @params,

    @collection_item_id = @collection_item_id OUTPUT;

As you see @parameters xml contains the t-sql query enclosed into <Value> tags (the output of this query is to be collected), OutputTable (TableSpaceDistribution) – the table where the data will be stored in mdw database under custom_snapshots schema.

Generic SQL Trace Collector Type

This collector type creates server-side trace on the target server. Data is collected to the trace file and uploaded to MDW according to schedule. You can use profiler to graphically design and test the trace and then save it as collection set creation script.

The exported script is complete script to create data collection set with single sql trace collection item. Below is the script to add sql trace collection item to the data collection set we created before

DECLARE @trace_definition xml

DECLARE @collection_item_id int

SELECT @trace_definition = convert(xml,

N'<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">

<Events>

<EventType name="Security Audit">

    <Event id="14" name="Audit Login" columnslist="1,9,6,10,14,11,12" />

    <Event id="15" name="Audit Logout" columnslist="15,16,9,17,6,10,14,18,11,12,13" />

</EventType>

<EventType name="Sessions">

    <Event id="17" name="ExistingConnection" columnslist="1,9,6,10,14,11,12" />

</EventType>

<EventType name="Stored Procedures">

    <Event id="10" name="RPC:Completed" columnslist="15,16,9,17,2,10,18,11,12,13,6,14" />

</EventType>

<EventType name="TSQL">

    <Event id="12" name="SQL:BatchCompleted" columnslist="15,16,1,9,17,6,10,14,18,11,12,13" />

    <Event id="13" name="SQL:BatchStarting" columnslist="1,9,6,10,14,11,12" />

</EventType>

</Events>

<Filters>

<Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="NOTLIKE" value="SQL Server Profiler - fb9c3bd6-3f7e-461c-9d84-d23db780c31b" />

</Filters>

</ns:SqlTraceCollector>

')

DECLARE @collector_type_GUID uniqueidentifier

SELECT @collector_type_GUID = collector_type_uid

FROM [dbo].[syscollector_collector_types]

WHERE name = N'Generic SQL Trace Collector Type'

DECLARE @collection_set_id int = (SELECT collection_set_id

                                  FROM syscollector_collection_sets

                                  WHERE name = N'SQLTeam collection set')

EXEC [dbo].[sp_syscollector_create_collection_item]

   @collection_set_id = @collection_set_id,

   @collector_type_uid = @collector_type_GUID,

   @name = N'SqlTrace Collection Item from SQLTeam.com',

   @frequency = 900, -- specified the frequency for checking to see if trace is still running

   @parameters = @trace_definition,

   @collection_item_id = @collection_item_id output

SELECT @collection_item_id

Dealing with sql trace collection items be carefull to not overload the target server with the trace. Collect in the trace only specific and critical information.

Query Activity Collector Type

This collection type item is used by system Query Statistics data collection set. This collection type is not configurable (parameter_schema is NULL) and should be avoided from using in custom collection sets. It collects information from such dynamic management views as sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_query_stats.

Performance Counters Collector Type

Collection items of this collector type can collect data from performance monitor (perfmon). All the counters viewed in system performance monitor can be used within this collector type.

Lets create performance counters collection item that collects Cpu load %, Avg. Disk Queue length and SQL Server Buffer Cache hit ratio.

USE msdb;

GO

DECLARE @collection_item_id int;

DECLARE @collection_set_id int = (SELECT collection_set_id

                                  FROM syscollector_collection_sets

                                  WHERE name = N'SQLTeam collection set');

DECLARE @collector_type_uid uniqueidentifier =

    (SELECT collector_type_uid

     FROM syscollector_collector_types

     WHERE name = N'Performance Counters Collector Type');

DECLARE @params xml =

    CONVERT(xml, N'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">

            <PerformanceCounters Objects="ProcessorPerformance" Counters="percentage" Instances="*"/>

            <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk Queue Length" Instances="*"/>

            <PerformanceCounters Objects="SQL Server Buffer Manager" Counters="Buffer cache hit ratio"/>

         </ns:PerformanceCountersCollector>');

EXEC sp_syscollector_create_collection_item

    @collection_set_id = @collection_set_id,

    @collector_type_uid = @collector_type_uid,

    @name = 'SQLteam Performance counters collection item',

    @frequency = 5,

    @parameters = @params,

    @collection_item_id = @collection_item_id OUTPUT;

After some time you will see this performance counters data in your management data warehouse in snapshots.performance_counters table.

Viewing collected data

There are three standard reports available by right clicking on Data Collection node

Server Activity History
Disk Usage Summary
Query Statistics History

For instance Disk Usage Summary looks like

Besides standard reports you can query the tables in the management data warehouse i.e. snapshots.disk_usage, snapshots.log_usage etc.

For collection items of T-SQL Query Collector Type query the table you specified as OutputTable in parameter XML for instance
select * from custom_snapshots.TableSpaceDistribution

Performance counters collector type data gets into snapshots.performance_counters table.

SQL Trace data collection items data is stored in snapshots.trace_data table in MDW. All these tables you will probably need to join with the view core.snapshots.

Data collector architecture

The next diagram describes architecture of data collector.

You configure the data collector itself and data collection sets in Sql Server Management Studio, this data gets into msdb and new jobs are created. Then sql server agent executes the jobs which steps call dcexec utility, for instance

dcexec -c -s 5 -i "SQL2008I2" -m 1

All of collection work is done by dcexec utility which is located in C:\Program Files\Microsoft SQL Server\MSSQL10.[InstanceName]\MSSQL\Binn\ directory. If you run dcexec -? from command line it will return

Microsoft (R) SQL Server Data Collector Command Line Tool

Copyright (c) Microsoft Corporation. All rights reserved.

usage: dcexec [-u | -c] -s set_id [-m collection_mode] [-i instance_name] [-e exit_event_name]

Data collector cooperates with sql server engine and operating system and uses the data collector cache. Finally collected data makes it into management data warehouse. The following image describes the data flow of performance data.

Actually this picture shows dataflow for a data collection set working in cached mode. In case of non-cached mode there will be only one job with no data collection cache. You can look at these jobs yourself as soon as you configured your data collector there will be at least 5 jobs for the system data collection sets/

Troubleshooting Data collector

There is an article in BOL Troubleshooting the Data Collector so here I will ask you only to make sure that you granted all the necessary mdw permissions (mdw_admin, mdw_reader, mdw_writer) to the login sql server agent runs under or created a proxy credential with necessary access. Use also our MS SQL Server forum, we will be glad to help you.

Comments (0)