Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Windows Server

SQL Server 2008 R2 : Performance Monitoring Tools (part 5) - Creating a Customized Data Collection Set

5/10/2013 6:01:56 PM
Creating a Customized Data Collection Set

Although you cannot change or delete the built-in system Data Collectors, you can define your own custom data collection sets. However, currently, you can define them only in T-SQL. There are four different collector types that you can use to build a collector set:

  • T-SQL query— Executes a user-provided T-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse.

  • SQL Trace— Uses SQL Trace to monitor the SQL Server Relational Engine, with trace data coming from the system default trace or from one or more custom traces.

  • Performance counters— Collects specific performance counter information from Windows Performance Monitor on the computer running SQL Server 2008.

  • Query activity— Collects query statistics and query activity information along with the query plan and query text for queries that meet predefined criteria. Essentially, this collector type collects the same information as the Query Statistics collection set, so it is recommended that you simply use the predefined Query Statistics collection set.

One of the reasons you might create a customized data collection set is that the default system Data Collector for Query Statistics does not store all the statements. It captures only the worst-performing queries based on the algorithms specified in the collection set. You might want to collect more queries than the top three worst performing ones. However, if you create your own data collection for query statistics, you should probably disable the default system collector to reduce data collection overhead.


To see an example of a collection set based on performance counters, DMVs, and T-SQL queries, you can look at the definition of the default Server Activity collection set. You can easily see this definition by right-clicking on Server Activity in the System Data Collection Sets folder in SSMS and selecting Script Data Collection.

There is also GUI support for creating a collection set based on a SQL Server Profiler trace. After you define a trace in SQL Server Profiler with the events you want to capture, select Export from the File menu; then choose Script Trace Definition and select For SQL Trace Collection Set. Doing so generates a T-SQL script that you can use to create a custom Data Collector Set based on a SQL Server Profiler Trace definition.

Assuming you’ve already set up your MDW, you can begin by creating the data collection set and adding the collection items you want it to contain. To create the data collection set, use the sp_syscollector_create_collection_set procedure. Next, you need to create the collection_items to indicate what information you want the collection set to collect. If you are creating collection items for Performance Monitor counters, The Performance Counter collector type takes three input parameters:

  • Objects— The SQL Server objects running in an instance of SQL Server

  • Counters— The counters associated with a SQL Server object

  • Instances— The instances of the specified object

Some input parameters support wildcard characters, which enable you to include multiple counters in a single statement. However, you can use wild cards only at the Counters and Instances levels and, even then, only at the beginning of the string (for example, '* Processor') or at the end of the string (for example, 'Memory *').

An example of the creation of custom collection set for capturing information for the Logical Disk and Process Performance Monitor counters is shown in Listing 1.

Listing 1. Creating a Custom Collection Set
Use msdb

Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [dbo].[sp_syscollector_create_collection_set]
     @name=N'Disk I/O Perf and SQL CPU',
     @collection_mode=1, — non-cached
  N'Collects logical disk performance counters and SQL Process CPU',
     @collection_set_id=@collection_set_id_1 OUTPUT,
     @collection_set_uid=@collection_set_uid_2 OUTPUT
Select collection_set_id_1=@collection_set_id_1,

**  Now, create the desired collection items

Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid
   From [dbo].[syscollector_collector_types]
   Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_4 int
EXEC [dbo].[sp_syscollector_create_collection_item]
@name=N'Logical Disk Collection and SQL Server CPU',
@parameters=N'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Avg. Disk Bytes/Read"
          Instances="*" />
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Avg. Disk Bytes/Write"
          Instances="*" />
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Avg. Disk sec/Read"
          Instances="*" />
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Avg. Disk sec/Write"
          Instances="*" />
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Disk Read Bytes/sec"
          Instances="*" />
     <PerformanceCounters Objects="LogicalDisk"
          Counters="Disk Write Bytes/sec"
          Instances="*" />
     <PerformanceCounters Objects="Process"
          Counters="% Privileged Time"
          Instances="sqlservr" />
     <PerformanceCounters Objects="Process"
          Counters="% Processor Time"
          Instances="sqlservr" />
@collection_item_id=@collection_item_id_4 OUTPUT,
Select @collection_item_id_4


After you create the collection set, you can start this data collection, either through SSMS (your user-defined collection sets will be listed directly within the Data Collection node) or with the following stored procedure call:

Declare @collection_set_id int
select @collection_set_id = collection_set_id
from syscollector_collection_sets
where name = 'Disk I/O Perf and SQL CPU'

EXEC sp_syscollector_start_collection_set
        @collection_set_id = @collection_set_id

Because there aren’t any custom reports available for displaying the results of the custom collection set just defined, you need to run a query in the MDW database to view the collected Performance Monitor counter values. A sample query (which could serve as the basis for a customer report) is provided in Listing 2.

Listing 2. Querying the MDW for Custom Data Collection Values
Use UnleashedMDW
select spci.path as 'Counter Path', spci.object_name as 'Object Name',
spci.counter_name as 'counter Name', spci.instance_name,
spcv.formatted_value as 'Formatted Value',
spcv.collection_time as 'Collection Time',
sii.instance_name as 'SQL Server Instance'
from snapshots.performance_counter_values spcv
        inner join
    snapshots.performance_counter_instances spci
        on spcv.performance_counter_instance_id = spci.performance_counter_id
        inner join
   core.snapshots_internal si
        on si.snapshot_id = spcv.snapshot_id
        inner join
    core.source_info_internal sii
        on sii.source_id = si.source_id
    sii.collection_set_uid = '5D9849BE-1526-4159-99EB-6B0E690C31EA'
order by spcv.collection_time desc


It is possible to create your own custom reports using SQL Server Reporting Services that query the information for your custom collection sets in the MDW database. 

Data Collector Limitations and Recommendations

Although the Data Collector is a great start to a built-in performance monitoring tool, it does have some limitations still, which are not wholly unexpected in a product that’s still early in its release cycle. One key limitation is the limited number of built-in data providers and the reports available. It is hoped that future versions will make it easier to extend the Data Collector to add additional collection sets and reports.

If you are defining your own custom Data Collectors, consider these recommendations:

  • Combine multiple performance counter or query collection items into a single collection item wherever possible.

  • Combine collection items into a single collection set whenever possible unless you need separate data retention periods or different collection schedules for the collection items.

  • If you collect data frequently, it is more efficient to run the collection set in cached collection mode than starting and stopping a new process every time new data must be collected. In cached collection mode, the collection process runs continuously. As a general rule, if you will be capturing data every five minutes or less, consider using a collection set that runs in cached collection mode.

  • If you are collecting data less frequently than every five minutes, using noncached mode is more efficient than leaving a generally idle process running all the time.

  • Although the collection frequency for cached collection sets can be set to run as frequently as every five seconds, be aware that more frequent collection has correspondingly high overhead. Always choose the lowest collection frequency that will meet your needs.

Currently, removing data collection after it has been configured is not supported. You can disable data collections but cannot remove them or the SSIS packages and jobs associated with them after they have been defined. Attempting to manually remove data collection may lead to errors if you try to re-implement data collection in the future. In addition, you should not drop or change the name of the MDW database because all the jobs are based on the original database name.

Another key limitation in the Data Collector is the lack of built-in alerting in the event that certain performance thresholds are crossed while monitoring the system. In contrast, the SQL Server Utility, which performs more limited monitoring and data capture than the Data Collector, does provide a threshold and alerting mechanism.

Other -----------------
- Microsoft Systems Management Server 2003 : Package Distribution and Management - Monitoring Status
- Microsoft Systems Management Server 2003 : Configuring the Client (part 4) - Managing the Advanced Client Download Cache, Advertised Programs Process Flow
- Microsoft Systems Management Server 2003 : Configuring the Client (part 3)
- Microsoft Systems Management Server 2003 : Configuring the Client (part 2) - Running Advertised Programs on Clients - Advertised Programs Wizard
- Microsoft Systems Management Server 2003 : Configuring the Client (part 1)
- Microsoft Systems Management Server 2003 : Package Distribution and Management - Creating an Advertisement
- SharePoint 2010 : Connecting and Disconnecting Servers with Windows PowerShell, Additional Functionality in SharePoint 2010
- Microsoft Dynamics CRM 4 : Digital Phone Integration (part 3) - c360 CTI for Microsoft CRM
- Microsoft Dynamics CRM 4 : Digital Phone Integration (part 2) - Cisco Unified CallConnector for Microsoft Dynamics CRM
- Microsoft Dynamics CRM 4 : Digital Phone Integration (part 1)
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro