Management Data Warehouse - Server Activity report

I love to set up MDW on SQL Server because it is worth for the data collection overhead most cases. It helped me a lot creating performance baselines, redesigning existing infrastructure as well as finding out problematic queries. There are dozens of reports behind MDW which can be displayed by using SSMS. MSFT did not published these reports for SSRS and not flexible enough at most of the cases, so I needed to rewrite all MDW and some other, custom reports to SSRS implementation. 
Server Activity report allows 24 hours window to see but I needed at least a week. You can download my report from the following link: Server Activity.rdl

You need to create and re-map data sources then providing the following parameters:
  • ServerName: it is the SQL server name which has data collected. Eg: in case of default instance --> SQLSERVER1, named instance --> SQLSERVER1\INSTANCE1. You can find all your instances in MDW by using this query: SELECT DISTINCT instance_name FROM core.snapshots
  • Interval End Time (UTC): last data (snapshot) collection time in UTC. Report will show in your local time all data by using some magic code in the report ;-) 
  • Window size: time window, like 4,12,24 hours or a week. You can extend the time window at here if you wish.
This report does not contain the links to other reports, drill down to details is not supported in this public version. I am using this report as a simple dashboard.
Bill Ramos' blog series helped me a lot recreating all the reports.

Comments (2) -

Marcin Woszczyna 1/9/2020 12:06:54 PM

Hi
I looking for rdl files of MDW reports. I found  Server Activity.rdl on your site. Blog of Bill Ramos is unavailabe, and I don't know how recreating all the reports. Could you help me ?

János Berke 1/9/2020 12:50:17 PM

Hi, These reports were not shared publicly, however they are stored in one of the SSMS dll and can be extracted with a tool. The latter is not really supported and ma break the EULA of the product.

Add comment