System Data Collection Reports
In my last article (http://sql-server-performance.com/articles/per/Management_Data_Warehouse_p1.aspx) I introduced one of the new SQL Server 2008 features known as the Management Data Warehouse (MDW). In that article I described how to install the components that make up the MDW. Now I’m going to expand on the subject and write about the rich reporting capabilities that Microsoft has included with the MDW.
What is the MDW?
For a quick review let me describe the basic MDW concept. The MDW is a database that contains data populated and managed by different Data Collections. A Data Collection defines the specific data that will be stored in the MDW, the method of collecting that data, and a schedule around when the data will be collected and purged. For each Data Collection a series of SSIS packages and SQL Agent jobs are built to collect and manage the data related to the collection. The MDW is more than just a database it is the whole process of collecting, managing and stored data that can be used to monitor your SQL Server environment.
Report Capabilities of the MDW
When you install the MDW three different system Data Collection sets get defined: Disk Space, Query Statistics and Server Activity. Microsoft has built a number of different reports for each one of these Data Collections. The reports are built using Reporting Services, and are run from within SQL Server Management Studio (SSMS). Rendering these reports are done through SSMS, and does not require Reporting Services to be installed.
Each system Data Collection set has one main report that provides a high level overview of the MDW data for the associated Data Collection. From each of these high level reports you get a general overview of where your server instance stands related to Disk Space, Query Statistics, or Server Activity, depending on which report you are viewing. From each one of the high level reports, Microsoft has built in a number of different drilldown reporting capabilities. These drilldown capabilities allow you to easily and quickly focus in on different detailed data related to a data collection. By looking at the detailed aspects of the drill down reports you can explore the different aspects of each Data Collection to determine what might be causing performance or capacity management issues. Let me show you some of the reports available, and how the navigation works for some these reports.
Disk Usage Reports
For the first set of reports let me explore the MDW data that is exposed using the Disk Space report. All of the Data Collection reports are rendered by using the SSMS Object Explorer pane. To do this you expand Management, Data Collection and System Data Collection set items in the SSMS tree. Then right click on the Data Collection set that you want to report on. Here is how that looks like when I bring up the reports for the Disk Space collection:
When I right clicked on the Disk Usage collection the left most menu is displayed. From there I mouse over the “Reports” item which then displays the middle menu. On that menu there is a “Historical” item. When I mouse over this item the high level report for the Disk Space collection is displayed, which is called “Disk Usage Summary”. You may note that the report is also listed in the middle drop down menu. This item will only be display in this middle menu after you have opened up the “Disk Space Usage” report for the first time. So once you have displayed this report you don’t need to hover over the “Historical” item to bring up that third menu, instead you can just click on the “Disk Usage Summary” on the middle menu. This kind of functionality is the same for all Reporting Services reports displayed within SSMS. After clicking on the “Disk Usage Summary” the following report is displayed:
On this high level report you can see a graphical representation of the growth rate for each database on the database instance named SDSSQL01SQL2008. This particular graph shows disk space usage trend for each database between 4/22/2008 and 6/16/2008. With this chart you can quickly identify the growth trend for each database. You can get a detailed report by drilling into one of the individual charts for a specific database. You do this by clicking on the graph. If I click on the MDW database graph I get the following detailed space usage report for the MDW database:
On this report you can see two months worth of space growth information. Here you can see the MDW started out at about 1300 MB and has grown to a little over 3000 MB between 4/19/2008 and 6/16/2008.
Keep in mind your charts might track data over a larger or smaller timeframe. The amount of data retained for the “Disk Usage” is controlled by a collection property. The upper limit for retaining the data is “32767”. In my case I’m retaining data for 720 days.