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 SDSSQL01\SQL2008. 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.


Query Statistics Reports
The “Query Statistics” report is displayed just like I to display the “Disk Usage” reports, by right click on the “Query Statistics” data collection item in Object Explorer, and then clicking on the “Query Statistics History” report item. When I do this on my machine the following report is displayed (two screenshots are displayed due to this size of the report):





This report is broken up into three different sections. The top section of the first screenshot contains a navigation timeline chart that allows you to select the timeframe you want to display for the reports. You can click on the plus (+) and minus (-) magnifying glass symbols to change the time frame for the display. The plus (+) sign allows you do zoom in, or decreased the interval of the display, or you can use the minus (-) sign to zoom out the display to show are larger interval. In this particular display my time frame is from 6/18/2008 at 2:28 AM to 6/18/2008 at 6:28 AM. The triangular symbols allow you to move back and forth through the timeline. You can also click on one of the light blue bars in the timeline to display query statistics for that time frame. Note that the current timeframe is highlighted by a dark blue color. This smallest duration you can zoom in and look at is 15 minutes, and the largest timeframe you can zoom out to is 24 hours.

The second part of this graph displays a graphical representation for resource usage. Five different types of resource usage measurements can be displayed: CPU, Duration, Total I/O, Physical Reads and Logical Writes. My screenshot above displays the 10 most expensive queries based on CPU usage. The screenshot above didn’t identify any queries with significant CPU usage. If there had been some that used a significant amount of CPU then you would see some bars in the “Most Expensive Queries by Total CPU” bar chart. You can change the display to another category by clicking on the “Rank Queries By” hyperlinks. If there are vertical bar to represent resources usage for any of the queries then you can drill down into detail of that query by clicking on the bar.

The last section of the above screen shots, lists the top 10 queries (found in the second screen shot). You can drill down into the details of these queries by clicking on the hyperlink in the “Query“ column. Below is the screen that is displayed when I drill down into the first query in my report above (note there are two screen shots since the report needs to be scrolled to see entire report):





On this report you get the complete text of the query, and a hyperlink to edit the query. Being able to edit the query allows an easy method to test, and modify the query to resolve the resource issues associated with the query. You can also drill down further into the different Query Plans. In the example there is only one plan identified. For some queries multiple plans might be displayed.

Server Activity Report
The Server Activity report, like the rest is rendered by selecting the report from the series of menus that are displayed by right clicking on the Object Explorer menu. Below are two screen shots that show what data is available on the Server Activity report:





As you can see there are lots of different graphs to help you identify the resource usage trends for my machine. You can click on each one of these graphs to drill down and get more detailed information. Below are two screenshots that show the information available when I drill down by clicking on the “Memory Usage” graph above.





Data Collection Reports
Microsoft has done a great job of building some out of the three data collections and associated reports into SQL Server 2008. All it takes to use these reports is to configure the Management Data Warehouse, and enable the system data collections. Once configured and enabled a DBA can get a wealth of performance and capacity information to better manage their environment.