SQL SERVER – SSMS: Performance Dashboard Installation and Configuration

If the standard set of reports were not enough, in this blog, let me introduce an interesting addition to the reports. This is called as Performance Dashboard reports. There are a set of predefined additional custom reports, built by Microsoft SQL Server Support team members, which are worth a look and a great addition to SQL Server Management Studio reports. In this blog, let me take you through the installation steps for the same.

The set of reports can be got from Microsoft Downloads site:

  1. Microsoft® SQL Server® 2012 Performance Dashboard Reports – Used in this blog post.
  2. SQL Server 2005 Performance Dashboard Reports

For SQL Server Management Studio version of 2008 and above, use the first link. It works even with the SQL Server 2014 version without any problem. In my opinion, these reports for sure compliment the already existing reports with additional information which any Developer / DBA would want to explore. I must point out that standard reports are not drill down reports which means you can just look at data shown in report. In the dashboard, you can keep clicking on hyperlink available in the report itself and keep going to other reports. We can explore the health of the server by clicking on the various charts and hyperlinks in the report.

Once you download, please go ahead by double clicking the setup.exe and the wizard starts as shown below:

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall1

I generally prefer the defaults and one of the important steps is to keep a note of the default installation location as marked below. Please take a special note because we will be using this path later in our configuration.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall2

So go through the rest of the steps and finish. Open the folder under question as described above. We will be presented with the readme.txt automatically and feel free to follow the steps there.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall3

The initial step is to run the “setup.sql” file from this folder. An important point to note is that the reports will not work for SQLAzure databases. Though the folder has only one .rdl file, in reality this is a bunch of 21 report files which are actually hidden for end user. If you enable hidden files, the complete list can be viewed here.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall4

Now that we have got all the installation done, it is time to visualize the reports. To invoke the report, Right click Server node -> Reports and select “Custom Reports…”. This will bring the file Open dialog to select the file. Point it to the same folder as discussed above.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall5

Be careful in this step, because if you select any other .rdl apart from “performance_dashboard_main.rdl”, we will get an error like below.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall6

The 'version string' parameter is missing a value
OR
The 'plan handle' parameter is missing a value

The errors are caused because all other reports are drill down reports and a value is passed from parent report to child report. Hence it is good in a way to have the other .rdl files as hidden because we will never get into these problems. If we point the dialog to “performance_dashboard_main.rdl” and select. The Dashboard will open like shown below.

SQL SERVER - SSMS: Performance Dashboard Installation and Configuration dashboardinstall7

This is the high-level report and the starting point for other reports to feed. In future blogs, we will look into each of the sections and how to land into each of these reports.

Do let me know if you have ever used these “Performance Reports” in your environment. It is worth a try for sure and let me know your experience in using the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Reports
Previous Post
SQL SERVER – A Question on SSMS Prompt about Connect to Server
Next Post
SQL SERVER – SSMS: Performance Dashboard Reports – Home Page details

Related Posts

8 Comments. Leave new

Leave a Reply