By: Matteo Lorini | Updated: 2010-03-25 | Comments (4) | Related: > 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(usecounts) AS 'Avg Use Counts'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY objtype
Query output:
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, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure,
@ErrorLine, @ErrorMessage);
END Catch;
GO
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 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>
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
The above table shows the cache utilization of my stored procedures during a period of time.
Next Steps
- Results from custom collections can be used to create reports with Microsoft Report Builder or Visual Studio Business Intelligence and utilized to create a centralized management solution based on Microsoft technology.
- Refer to these other tips related to this topic.
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-03-25
