Create Custom Data Collections for SQL Server Data Management Warehouse

By:   |   Updated: 2010-03-25   |   Comments (4)   |   Related: More > Performance Data Warehouse


Out of the box, SQL 2008 comes with 3 pre-canned data collection scripts. However, it is possible to create custom made data collection scripts that are fully integrated with SQL Server Data Management Warehouse.  In this tip, we cover the process to create your own collection.


Part of a DBA's duties is to monitor the status and activities of all the SQL Servers. If no third party monitoring software is used, we can rely on SQL Server Data Management Warehouse to create custom data collections that can be used to capture SQL Server information.

For example, the following query can be used to gather SQL Server cache utilization values by object types.

SELECT objtype AS 'Cached Object Type',
COUNT(*) AS 'Numberof Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1048576 AS 'Plan Cache SIze (MB)',
AVG(usecountsAS 'Avg Use Counts'
FROM sys.dm_exec_cached_plans
GROUP BY objtype 
ORDER BY objtype

Query output:

SQL 2008 comes with 3 pre-canned data collection scripts


Proc Stored procedure
Prepared Prepared statement
Adhoc Ad hoc query
ReplProc Replication-filter-procedure
Trigger Trigger
View View
Default Default
UsrTab User Table
SysTab System Table
Check CHECK Constraint
Rule Rule

In my example, I have 93 stored procedures in the cache and they have been used 14,537 times.

In order to create a custom data collection we need to create the collection set first, and then define the collection type that will be used.

The collection set defines the collection name, the expiration date, and a predefined collection schedule.

The collection type defines the collector types that will be used, in our example generic T-SQL query. The last step creates the collection item where the T-SQL query body is wrapped in XML.

Let's see an example:

DECLARE @collection_set_id_1 INT
@collection_set_uid_2 uniqueidentifier
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set] @name=N'Cache Usage Report'@collection_mode=1
@description=N'Cache Usage Report'@logging_level=1@days_until_expiration=14
=N'CollectorSchedule_Every_6h'@collection_set_id=@collection_set_id_1 OUTPUT
=@collection_set_uid_2 OUTPUT

DECLARE @collector_type_uid_3 uniqueidentifier
SELECT @collector_type_uid_3 collector_type_uid FROM [msdb].[dbo].[syscollector_collector_types] 
name N'Generic T-SQL Query Collector Type';
DECLARE @collection_item_id_4 INT
[msdb].[dbo].[sp_syscollector_create_collection_item] @name=N'Cache Usage Report'@parameters=N'

SELECT objtype AS ''Cached Object Type'',
COUNT(*) as ''Number of Plans'',
SUM(cast(size_in_bytes as BIGINT))/1048576 as ''Plan Cache SIze (MB)'',
avg(usecounts) as ''Avg Use Counts''
from sys.dm_exec_cached_plans
group by objtype
order by objtype

=@collection_item_id_4 OUTPUT@frequency=5

SELECT @collection_item_id_4
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorProcedure NVARCHAR(200);
@ErrorSeverity ERROR_SEVERITY(),
@ErrorState ERROR_STATE(),
@ErrorNumber ERROR_NUMBER(),
@ErrorMessage ERROR_MESSAGE(),
@ErrorProcedure ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684@ErrorSeverity@ErrorNumber@ErrorSeverity@ErrorState@ErrorProcedure
END Catch;

From SQL Server Management Studio we can see our newly created custom collection

From SQL Server Management Studio we can see our newly created custom collection

Once created, the new collection needs to be started. We can do it by right clicking on the collection name and then click start.

Once created, the new collection needs to be started

Once the data has been collected, we can check the results inside our data management warehouse database. The table name is Cache_Usage_Report and it was defined by us in the following statement: <OutputTable>Cache_Usage_Report</OutputTable>

The table name is Cache_Usage_Report

If we would like to see what data has been collected we can simply run the following statement:

SELECT FROM custom_snapshots.Cache_Usage_Report 

 to see what data has been collected we can simply run the following statement

The above table shows the cache utilization of my stored procedures during a period of time.

Next Steps

Last Updated: 2010-03-25

get scripts

next tip button

About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources

Comments For This Article

Tuesday, April 01, 2014 - 4:42:48 AM - Carla Abanes Back To Top (29933)

i have the following code below for my custom data collector set. The data collector is succesfully created but when i visit my MDW, I dont see my custom_snapshots.failedsqljobs. please help!


Begin Transaction
Begin Try
 Declare @collection_set_id_1 int
 Declare @collection_set_uid_2 uniqueidentifier

--section A; define data collector container
 EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
  @name=N'Failed SQL Jobs',
  @description=N'Collects data about failed jobs for all servers.',
  @[email protected]_set_id_1 OUTPUT,
  @[email protected]_set_uid_2 OUTPUT

 Select @collection_set_id_1, @collection_set_uid_2
-- end of section A

--section B; define data collector type
 Declare @collector_type_uid_3 uniqueidentifier
 Select @collector_type_uid_3 = collector_type_uid
 From [msdb].[dbo].[syscollector_collector_types]
 Where name = N'Generic T-SQL Query Collector Type';
--end of section B

--section C; define data collection item 
 Declare @collection_item_id_4 int

 EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
  @name=N'Failed SQL Jobs Item',
  @parameters=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
  SELECT  @@ServerName AS [ServerName],
    [sJOB].[name] AS [JobName],
    [sJOBH].[run_date]  AS [LastRunDateTime],
    CAST([sJOBH].[run_duration] AS VARCHAR(6)) AS [LastRunDuration (HH:MM:SS)],
    [sJOBH].[message] AS [LastRunStatusMessage],
    CAST([sJOBSCH].[NextRunDate] AS CHAR(8)),
    [sJOBSCH].[NextRunDate] AS [NextRunDateTime]
  FROM [msdb].[dbo].[sysjobs] AS [sJOB]
   LEFT JOIN (SELECT [job_id], MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
  LEFT JOIN (SELECT [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1
  WHERE  [sJOBH].[run_status] = ''0''
  ORDER BY [LastRunDateTime] DESC

  </Query><Databases UseSystemDatabases="true" UseUserDatabases="true" />
  @[email protected]_item_id_4 OUTPUT,
  @[email protected]_set_id_1,
  @[email protected]_type_uid_3
--end of section C

 Commit Transaction;
End Try

Begin Catch
Rollback Transaction;
 DECLARE @ErrorMessage NVARCHAR(4000);
 DECLARE @ErrorSeverity INT;
 DECLARE @ErrorState INT;
 DECLARE @ErrorNumber INT;
 DECLARE @ErrorLine INT;
 DECLARE @ErrorProcedure NVARCHAR(200);
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

End Catch;



Sunday, May 20, 2012 - 1:32:35 AM - Jude Back To Top (17554)

Added the change


EXEC [msdb].[dbo].[sp_syscollector_create_collection_item] @name=N'Cache Usage Report', @parameters=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>

        SELECT objtype AS ''Cached Object Type'',
        COUNT(*) as ''Number of Plans'',
        SUM(cast(size_in_bytes as BIGINT))/1048576 as ''Plan Cache SIze (MB)'',
        avg(usecounts) as ''Avg Use Counts''
        from sys.dm_exec_cached_plans
        group by objtype
        order by objtype
        @[email protected]_item_id_4 OUTPUT, @frequency=5,
        @[email protected]_set_id_1, @[email protected]_type_uid_3


Sunday, May 20, 2012 - 1:03:19 AM - Jude Back To Top (17553)

I am getting the following message when attempting to create the collection


Msg 14684, Level 16, State 1, Procedure sp_syscollector_create_collection_item, Line 144
Caught error#: 6909, Level: 16, State: 1, in Procedure: -, Line: 1, with Message: XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /
Msg 266, Level 16, State 2, Procedure sp_syscollector_create_collection_item, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Appreciate , if you can pointout what im missing


Thanks in advance


Tuesday, November 01, 2011 - 7:33:36 PM - Pete Back To Top (14987)



Firstly thanks for your article "Create Custom Data Collections for SQL Server Data Management Warehouse". In the article I could not help but notice the additional custom data collection call "Last backup report". Would it be possible to make this available please?


Thanks in advance,




Recommended Reading

Performance Data Collection and Warehouse Feature of SQL Server 2008 Part 1

Performance Data Collection and Warehouse Feature of SQL Server 2008 Part 2

Removing the SQL Server Management Data Warehouse

get free sql tips
agree to terms