What are all these reports anyways? – MDW Report Series Part 5
By Bill Ramos, Principal Program Manager, SQL Server Manageability Team
It just dawned on me that in preparing for SQL PASS and helping folks understand more about the existing MDW reports, that there is zero documentation on what reports are in SSMS for the system collection sets. Kalen Delaney covered the top level reports in her SQL Server 2008 white paper SQL Server 2008 Management Data Warehouse. In the SQL Server 2008 documentation, there is one topic How to: View a Collection Set Report that covers the three main reports. That’s it! 
In this blog post, I’ll provide an overview of each of the reports so that you know what we’ll end up with once I’ve published all of the RDL files – rewritten for Reporting Services 2008 R2.
Report Time Line Control
The report time line control allows the user to select a point in time for reports showing data over time. The grayish blue areas represent time periods where there is data in the warehouse. The broad blue line represents the boundaries of the currently-selected time window. The report reflects the data in the blue window.
The user can navigate (change the size or position of the selected time window) using the buttons below the timeline, or by clicking on an arbitrary point on the timeline. Here is an example of a timeline control for an MDW instance that has no data.
Report Drill-Through Relationships
This is the “money” diagram that shows the navigation relationship between all of the MDW reports.
Reports in the MDW
This section shows examples of each of the MDW reports. The good news is that I don’t have to describe them for you since the description of each of the reports is in the header for the report. Hopefully the data that is presented in the report is intuitively obvious to the casual observer. Yes, the images are a bit on the old side, but they are mostly what we ended up shipping for SQL Server 2008. There were no changes made to the reports for SQL Server 2008 R2. Here we go…
Server Activity History
System CPU Usage
Launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.
System Disk Usage
Launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.
SQL Server Memory Usage
Launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.
System Memory Usage
Launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.
SQL Server Waits
Launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.
SQL Server Blocking
Launch this report by clicking on the Lock hyperlink within the SQL Server Waits report.
SQL Server Blocking Chain Detail
Launch this report by clicking on either the blocking chain part in the chart or on the Chain # link number in the list in the SQL Server Blocking report.
SQL Server Active Requests
Launch this report by clicking on the Sample Time within the SQL Server Blocking Chain Detail report.
When you expand the SPID row, you get details for that SPID as shown below.
SQL Server Observed Waits
Launch this report when clicking on any of the other wait hyperlinks in the Wait Category list in the SQL Server Wait report.
Note, in this report, it shows the queries that just happen to be sampled during the time of the observed waits. If you need to perform detailed wait analysis, check out Jonathan Keyayais’s white paper Using SQL Server 2008 Extended Events.This is 500 level material, but the MDW reports tend to get you close enough.
Query Statistics History
Launch this report directly from the Data Collection reports menu or my clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.
My motto here – click on the big stuff. We only list 10 queries here because these are the most expensive ones to worry about. Listing more would be like rearranging deck chairs on the Titanic while it’s sinking.
Query Details
Launch this report by clicking on any hyperlink text in any of the reports that has them. For example, SQL Server Observed Waits, SQL Server Active Requests, and the Query Statistics reports all have links to Transact SQL queries.
Note, clicking on the Edit Query Text link in the report, launches the query editor with the selected text. This is due to a limitation of the report viewer control not having the ability to select text and copy it to the clipboard. Once we get the reports ported to run with SQL Server Reporting Services, you’ll be able to copy text from the reports.
You can also click in the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.
Query Plan Detail
Launch this report by clicking on the Query Plan line in the chart or the Plan # in the list in the Query Details report. The top part of the report is the same as the Query Details report, but the killer demo portion is shown below.
If there is a missing index for the plan, you can see the query text, but the copy/paste problem exists. So we added the View Graphical Execution Plan link to this report to display the execution plan that was captured by the Query Statistics collection set.
The Other Reports
We have Disk Usage reports, but they aren’t all that interesting and have some nasty bugs that require rewriting with the time line control. I’ll leave them for a future blog post.
Other Performance Troubleshooting References
For some great references on troubleshooting SQL Server performance problems, check out https://sqlcrunch.com/PerformanceTuning/tabid/100/Default.aspx. Rod Colledge has done a great job of cataloging all of the greatest white papers on all topics around SQL Server.
See you at SQL PASS
I’ll be at SQL PASS presenting how to write reports against the MDW for hosting them on Reporting Services on Wednesday at 4:30. my session follows Ben Nevarez’s Data Collector and Management Data Warehouse session. Right after my session, Ben and I will handle Q&A in room 305.
Be sure to wear your Kilt on Wednesday - https://www.sqlpass.org/summit/na2010/Connect/AfterHours.aspx. I’ll be wearing my Kilt on both Tuesday and Wednesday to drum up support for the event. If you need a Kilt, check out the folks at UtiliKilts in Seattle or order on line.
Comments
- Anonymous
February 08, 2012
The comment has been removed - Anonymous
March 11, 2013
Please let me know procedure to take all report as my day tonday work as a sql dbaEmail id: vijayjain347@gmail.com