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.