Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. The descriptions in the reports are hopefully intuitively obvious enough for the casual observer to interpret the reports.

Server Activity History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Server Activity History
ActivityHistory1 ActivityHistory2

System CPU Usage
You can launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.
CPUUsage1 CPUUsage2

System Disk Usage
You can launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.
DiskUsage1 DiskUsage2

SQL Server Memory Usage
You can launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.
MemoryUsage1 MemoryUsage2

System Memory Usage
You can launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.
SystemMemoryUsage

SQL Server Waits
You can launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.
ServerWaits1 ServerWaits2

Query Statistics History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Query Statistics History or by clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.

Only 10 queries are listed here. I would recommend clicking on the biggest one first to check the Query Details in order to optimize it better.
QueryStatistics

Query Details
You can 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.

You can also click on the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.
QueryDetails1 QueryDetails2

Query Plan Detail
You can 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 awesome demo portion is shown below.
QUeryPlanDetail
Hope this post was useful for you.

Choice to learn

One thought on “Management Data Warehouse (MDW) Reports

  1. Hi, thank you for your blog! On problem I ran into is, that I have set up data collectors on different servers: Data gets collected (Servers and data flow in the central MDW), however the standard reports just show the local instance. I can not find a parameter or something to switch between different servers? Anyone experienced this issue? Help is much appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.