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:

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.

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.

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.

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.

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.

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.

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)