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

Leave a Reply