Hosting the Performance Dashboard Reports in SSRS

  • Article

I blogged a while back about modifying the SQL Server 2005 Performance Dashboard reports to run on SQL Server 2008. I’ve since been working with several customers who use these reports for performance troubleshooting, but who would like to host them on their Reporting Services platform so they can be viewed online instead of within SQL Server Management Studio. So over the past few days I’ve been killing time on flights and trains doing just that. So here goes…

It’s actually very simple to get the reports up and running on Reporting Services. All you have to do is install the Performance Dashboard reports, create a new Report Server project in BIDS (or Visual Studio) and import the .rdl files from the directory they were installed to (right-click the solution name in Solution Explorer –> Add –> Existing Item).

Capture1

They use a shared data source, so you need to update this to point to the SQL Server instance you want to monitor (making sure that you have enabled the instance for use with the Performance Dashboard reports first), deploy the reports and data source to your Report Server and you’re ready to roll.

Capture2

However, this means that you can only look at one server. To monitor multiple servers, you would would need to repeat the process and host a separate copy of the reports for each one… not very scalable !

The solution I’ve come up with (and which you can download in the attached .zip file) requires you to publish the reports only once and use a parameter to dynamically determine which server we want to connect to in our data source. This parameter is set in the Performance_Dashboard_Main.rdl file when you first launch the report and is used as an expression in the report data source to dynamically build the connection string.

Capture5

Capture3

We need to modify the existing data source to be an embedded data source as using expressions in connection strings is not supported with shared data sources.

Capture4

Capture6

This parameter is then passed through to subsequent linked reports to build the dynamic data source connection for those reports as well.

Capture7

I’ve created 2 versions of the Performance_Dashboard_Main report (Performance_Dashboard_Main.rdl and Performance_Dashboard_Main_CMS.rdl) which I’ve included in the attached solution file. The first one uses a free-text field to enter the server name and the second one uses the new Central Management Server (CMS) functionality in SQL Server 2008 to dynamically populate a drop-down list of servers you have already registered on your CMS… very cool !

These reports have been designed and tested to work on SSRS 2008, however you can use the same technique to host them on SSRS 2005 or 2008. The data source for the reports can also either point to SQL Server 2005 or 2008 instances (as long as you’ve followed the instructions for modifying the reports for SQL Server 2008 first). As I’ve mentioned before, these reports are not a replacement for the fantastic new Management Data Warehouse functionality of SQL Server 2008, but can provide another valuable tool to help DBA’s analyse performance issues.

Download the reports, have a play and let me know what you think !

Performance Dashboard Reports.zip

Comments

  • Anonymous
    January 01, 2003
    thanks Rob.. !!

  • Anonymous
    December 06, 2010
    Hi Rob, Great tip. I do not see in my BIDS in SQL Server 2005 SE the option to modify existing shared data source into an embedded data source, the option embedded connection does not exist in the data source properties. A limitation in Standard Edition?

  • Anonymous
    December 08, 2010
    The comment has been removed

  • Anonymous
    March 13, 2011
    A big thank you!!

  • Anonymous
    March 05, 2012
    I am pointing to machine A to access the published report and in the ServerName field, I point to B and I got this error? What am I missing here?    An error has occurred during report processing. (rsProcessingAborted)        Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)            For more information about this error navigate to the report server on the local server machine, or enable remote errors

  • Anonymous
    March 20, 2012
    I tried to do this for the 2012 Performance Dashboard reports but could only get the main to work, it wouldn't run any of the sub reports. Anyone got this working yet?

  • Anonymous
    February 05, 2013
    in select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info to:    select @ts_now = cpu_ticks / convert(float, (cpu_ticks/ms_ticks)) from sys.dm_os_sys_info or:    select @ts_now = ms_ticks from sys.dm_os_sys_info blogs.technet.com/.../performance-dashboard-reports-for-sql-server-2008.aspx

  • Anonymous
    September 03, 2013
    Nick In the sub report change the datasource name default is datasource1, change it some name like MyDataSource then in the Report Server; Report DataSource ; A custom  datasource, ; Credentials stored securely in the report server ;  add your custom credentials.   This should fix sub report issue