mssqltips logo

Create Custom Data Collections for SQL Server Data Management Warehouse

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

Problem

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.

Solution

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

Where:

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:

BEGIN TRANSACTION
BEGIN 
Try
DECLARE @collection_set_id_1 INT
DECLARE 
@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
@schedule_name
=N'CollectorSchedule_Every_6h'@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
 
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';
DECLARE @collection_item_id_4 INT
EXEC 
[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
 
Cache_Usage_Report'

@collection_item_id
=@collection_item_id_4 OUTPUT@frequency=5
@collection_set_id
=@collection_set_id_1@collector_type_uid=@collector_type_uid_3

SELECT @collection_item_id_4
 
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);
SELECT @ErrorLine ERROR_LINE(),
@ErrorSeverity ERROR_SEVERITY(),
@ErrorState ERROR_STATE(),
@ErrorNumber ERROR_NUMBER(),
@ErrorMessage ERROR_MESSAGE(),
@ErrorProcedure ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684@ErrorSeverity@ErrorNumber@ErrorSeverity@ErrorState@ErrorProcedure
@ErrorLine@ErrorMessage);
 
END Catch;
 
GO

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 
ORDER BY 1

 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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',
  @collection_mode=1,
  @description=N'Collects data about failed jobs for all servers.',
  @logging_level=0,
  @days_until_expiration=180,
  @schedule_name=N'CollectorSchedule_Every_6h',
  @[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

  </Value><OutputTable>FailedJobs</OutputTable>
  </Query><Databases UseSystemDatabases="true" UseUserDatabases="true" />
  </ns:TSQLQueryCollector>',
  
  @[email protected]_item_id_4 OUTPUT,
  @frequency=60,
  @[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);
SELECT @ErrorLine = ERROR_LINE(),
       @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;

GO


 


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

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
         
        </Value><OutputTable>Cache_Usage_Report</OutputTable></Query></ns:TSQLQueryCollector>',
        @[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

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

Jude


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

Matteo,

 

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,

Pete.

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools