If I had used this specification for the <Databases> element in the above script I would have gathered record counts for only the databases USERDB1 and USERDB2.
Running the above script creates my new custom Data Collection. Just defining the collection doesn’t cause it to start collecting record counts. I need to also enable the collection. To do this I can either user SSMS, or by running the following T-SQL statement:
EXEC msdb.dbo.sp_syscollector_start_collection_set @name = ‘Number of Rows’
To start my Data Collection in SSMS I would right click on the Data Collection item in the Object Explorer and select the “Start Data Collection Set” option.
Custom Data Collection Information in MDW
Once my data collection is defined and started it will start collecting data once a day at midnight. When the data collection process runs and uploads information to the MDW the first time it will create the table to store my data collection information. From the script above we know the table that will contain my collected statistics will be named “NumOfRows”. This table will be created in the “custom_snapshots” schema.
By running the following SELECT statement I can review the first 10 row counts for my AdventureWorks2008 database:
SELECT TOP 10 [ObjectName]
,[SchemaName]
,[NumOfRows]
,[database_name]
,[collection_time]
,[snapshot_id]
FROM [MDW].[custom_snapshots].[NumOfRows]
WHERE [database_name] = ‘AdventureWorks2008’;
When I run this code I get the following output:
By collecting row counts routinely I can track table the growth rate of each table over time. If I wanted to I could build a process that would provide me a nice trend report off my row count information. If I built this trend report using Reporting Services, I could build a report with drill down capabilities that would allow me to graphically view the growth trend based on row counts.
Extending the Data Collection Process
The Custom Data Collection process of SQL Server 2008 now provides the DBA with an easy to use method to collect and manage data for statistical reporting. By allowing DBAs to place their own data collection requirements under the control of the data collector process provides the DBAs with a simple process to control collecting and purging their data. Using the Data Collection process the DBA no longer has to write home-grown routines to collect, store and manage statistical data. The only short fall of the data collector process is the fact that you have to script your data collections instead of using a GUI interface from within SSMS. As you get more familiar with SQL Server 2008 you should consider the Data Collection process as a means to collection statistically data to help you better manage your environment.
]]>