Early Christmas Present – Data Collector Reports!

Now you can get the source of the MDW reports for customization

The Data Collector was introduced in SQL Server 2008 and provides some great functionality “out of the box”, with some cool interactive reports based on performance metrics. I spent much of the PASS Summit conference last month trying to track down the source code for these reports. Eventually, I tracked down Bill Ramos, Principal Program Manager at Microsoft who promised to provide the source within days of the conference finishing. Bill has kept true to his word…

The concept of the Data Collector is that a “Management Data Warehouse” (MDW) is created according to a predefined database schema to capture performance metrics about your SQL Servers. SQL Agent jobs are then set up to collect and upload the metrics to the MDW for analysis. Prebuilt reports are then run against the MDW to produce interactive graphical charts. Very nice.

The intent is that the MDW sits on a separate SQL Server so that it does not impact the performance of the servers you are keeping an eye on. Watch out, the MDW can get quite big quite quickly but the ability to track performance metrics over time and have the data persisted in a database can have great value. The reports are excellent and have interactive links enabling drilldown capability for further analysis.

Under the Management folder in SSMS 2008, you can run the “Configure Management Data Warehouse” wizard by right-clicking the Data Collection node. First time through it creates the empty MDW. Second time through, it creates the collect and upload jobs when you run the wizard on the server you want to monitor. There are three Data Collection Sets predefined for you: Disk Usage, Server Activity and Query Statistics. Let the Data Collection run for a while, then the reports can be run by right-clicking the Data Collection node and choosing Reports/Management Data Warehouse.

The Data Collector was originally called the Performance Studio but was changed during the Katmai beta program in 2008. Unfortunately, the Windows 2008 team also came up with the same term when renaming Perfmon counter logs. It would be nice if the Windows and SQL teams had lunch once in a while but that’s another story…

Bill Ramos: “May the source be with you! MDW Report Series Part 6–The Final Edition”:
http://blogs.msdn.com/b/billramo/archive/2010/11/18/may-the-source-be-with-you-mdw-report-series-part-6-the-final-edition.aspx

Cheers
Brian

• Microsoft
• Storage
• Data Collection Sets
• Data Collector
• Disk Usage
• Query Statistics
• Server Activity
• SQL Server 2008