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 TRANSACTIONEND Try
BEGIN Catch
ROLLBACK TRANSACTIONDECLARE @ErrorMessage NVARCHAR(4000DECLARE @ErrorSeverity INTDECLARE @ErrorState INTDECLARE @ErrorNumber INTDECLARE @ErrorLine INTDECLARE @ErrorProcedure NVARCHAR(200SELECT @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.
Matteo is currently a DBA for one of the largest global electronic payment service providers in the U.S. He has been working in IT since he graduated from college in 1993. Matteo has a Master’s degree in Computer Science, and specializes in MSSQL with a good working knowledge of MySQL as well.
- MSSQLTips Awards: Rising Star (50+ tips) – 2021 | Author Contender – 2020