May the source be with you! MDW Report Series Part 6–The Final Edition

Bill Ramos, Principal Program Manager, SQL Server Manageability

Up until this point, I’ve walked through step by step how the MDW reports could be recreated to use SQL Server Reporting Services 2008 R2 and edit them in Report Builder 3.0.

At last week’s SQL PASS, I announced in my Wednesday session that I would release the source to the RDL files that I demonstrated.

At the bottom of this post, you’ll find a zip file that contains the RDL files that we converted up to this point. I say we because this was a collaboration with Bart Duncan – author of  Query Hash Statistics, Rachna Agarwal from our Microsoft IT department in Hyderabad India and myself.

Download Query Hash Stats

The first think you will want to do is download and install the SQL Server Query Hash Statistics project on MSDN Code Gallery. This includes a new version of the Query Statistics collection set – the old one – you’ll want to turn off – and a set of reports that can run inside of SSMS as a custom report. I’ve included a slightly modified version of Bart Duncan’s reports that work in SSRS.

May the Source Be With You

You will want to refer back to my post “What are all these reports anyways? – MDW Report Series Part 5” that lists of the MDW reports and there relationship to each other. These reports assume that you have created shared data source called MDW in your Reports manager filder. Just unzip the reports and upload them to your Reporting Services server. Start with the new MdwOverviewCustom.rdl file and you should be good to go.

Here is a list of the reports you will find at the bottom of this posting in MDW RDL Files.zip.

MdwOverviewCustom.rdl – Bart’s new an improved Overview report

mdw_overview.rdl – The original one as part of the blog series

QueryHashStatsReportsDetail.rdl – From Query Hash Stats that includes a query to get the execution plan.

QueryHashStatsReportsTopN.rdl – The new and inproved Query Statistics report from Query Hash Stats. You’ll never want to look back after seeing this report.

server_activity.rdl – From the blog series.

sql_active_requests.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_activity.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_disk_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_memory_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_waits_blocking_chain_detail.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_blocking_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_generic_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_wait_stats_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

That’s it!  Have fun with the source. Let me know what you think about them on https://twitter.com and include my @billramo address and the hash tag #MDWReports.

The source reports point to a shared MDW datasource on my old machine at Microsoft. You'll most likely end up with the following error:

• The report server cannot process the report or shared dataset. The shared data source 'MDW' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

The workaround is pretty simple, create your own MDW shared data source and then after uploading the report, open it up in Report Builder 3.0 or BIDS. Simply go the MDW data source, right click and select properties. Click on the Browse button to find your version of the MDW data source and click OK. Save the report,, and you should be good to go.

Final_MDW_RDL's.zip

• Anonymous
  December 16, 2010
  This is awesome, i have modified your reports and succesfully published to my sharepoint farm..Thanks a lot...
• Anonymous
  December 16, 2010
  Hi Bill,I dont find the rdl files for CPU utilization and also for the disk usage collection sets, can you please attach them to your blog?Thanks in advance..:)
• Anonymous
  January 20, 2011
  Hey Ashok ,We are done with the CPU utilization and the the disk usage RDL's . Bart will soon be uploading it here in his blog.Thanks,Rachna
• Anonymous
  January 20, 2011
  Sorry about the typo in the above . It's Bill and not Bart.
• Anonymous
  April 26, 2011
  Hi BillMany thanks for the tremendous work.Do you have a Report for disk usage?Thanks
• Anonymous
  December 15, 2011
  Thanks a lot for the detailed information about MDW. I have noticed that Server_Activity.rdl timeline and button underneath it are not working.Please help me out if you can. Thanks.
• Anonymous
  January 12, 2012
  Fantastic series Bill.I recommend this as a beginner tutorial for introduction to Performance Studio and SSRS. I had already setup an MDW warehouse database and was collecting data prior to following this series. The instructions were clear and easy to follow.Thank you for publishing the remaining .rdl's, though I think I will create several of them myself for the practice.
• Anonymous
  March 27, 2012
  The comment has been removed
• Anonymous
  March 31, 2012
  I have exactly the same problem. Has someone the definition of that view ??
• Anonymous
  June 10, 2012
  FYI: I also hit the issue of cannot find custom_mdw_overview_reports. However, if you take the MDWOverViewcustom.rdl from archive.msdn.microsoft.com/.../ProjectReleases.aspx & setup the queryhasstats collection set from there the reports should work.Looks like the version of MDWOverViewcustom in the zip is an outdated one...
• Anonymous
  June 28, 2012
  Many thanks for the tremendous work.Do you have a Report for disk usage?Thanks
• Anonymous
  July 26, 2013
  The comment has been removed
• Anonymous
  December 04, 2013
  Great, thanks for these. Are there any similar rdl files available for Utility Explorer reports available in SSMS for UCP MDW?
• Anonymous
  March 06, 2014
  Hi,I think the zip is missing a file.in the report sql_active_request, it has an action link to go to report query_stats_detail but there is no query_stats_detail.rdl. are you able to update the zip? Thank you.
• Anonymous
  October 28, 2014
  Hi Bill,Can you please attach script of below 2 custom stored procedures which you used inQueryHashStatsReportsTopN and server_cpu_snapshot
• Anonymous
  February 05, 2015
  The comment has been removed