Performance Dashboard | TroubleshootingSQL

I had been asked the question about using the Perf Dashboard reports from a Reporting Services instance multiple times. Though this was not the intention of launching the SQL Server Performance Dashboard Reports but there is a compelling need at times to have these accessible from a web URL. In today’s age, you will find a lot of DBAs monitor their SQL Server instances remotely. Sometimes, there is another layer of complexity added to this when they want to just look at the health of the SQL Server instance by accessing a URL exposed through a corporate server in their environment. In such cases, you wouldn’t want to jump through a few hoops of setting up your VPN connection and blah blah.

So with all that in mind, let’s talk about how you can get your SQL Server 2012 Performance Dashboard to your existing SQL Server Reporting Services instance.

First you need to create a new Reporting Services Project using SQL Server Data Tools (SSDT).
Use the solution explorer to add all the existing performance dashboard reports from the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard folder.
Modify the project properties to ensure that the data source gets written to the same folder as the reports.
Create a shared data source pointing to your SQL Server instance.
Open each report that was imported into the project and modify the data source properties to use the shared data source that you created in the above step.
Modify the project deployment properties as shown below.

Now you are ready to deploy your Performance Dashboard SSRS project to your reporting services instance.

What can you do next?

You can now set up Report Server subscriptions for the set of reports that you wish to receive via email. You can get a status report of your SQL Server instance without even having to lift a finger and that to right in your mailbox! Smile

Note that Performance Dashboard is a tool which is provided “AS-IS” by Microsoft. The steps mentioned above will help you deploy the existing Performance Dashboard reports to a SQL Server Reporting Services instance. However, the failure to deploy these reports as mentioned above is not liable for support by Microsoft SQL Server Support team.

Technorati Tags: Performance Dashboard,Data Collection,Monitoring,SQL Server,SQL Server 2012

I had written an article on SSWUG on how to track performance problems using Performance Dashboard. So, I thought it would be a good idea to blog about the new enhancements to the toolset for SQL Server 2012. The Performance Dashboard has been enhanced for SQL Server 2012 and is available for download on the Microsoft Download site.

And with this I also start the Awesomesauce series on my blog where I will keep posting about new features of SQL Server 2012 which I think are just plain awesomesauce!!

Once you install the Performance Dashboard, you need to do the following:

1. Each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the reports. Using SQL Server Management Studio (SSMS), open the setup.sql script from your installation directory (default of %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script. Close the query window once it completes.

2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file. Explore the health of your server by clicking on the various charts and hyperlinks in the report.

3. All of the remaining reports are accessed as drill through operations from the main page or one of its children. For a detailed explanation of all installation requirements and guidance on how to use the reports, please see the help file, PerformanceDashboardHelp.chm

Now that the nitty gritty details are out of the way, what will you get when you view the dashboard and you will notice the dashboard having a new entry for XEvent sessions currently active on the instance.