Awesomesauce SQL Server 2012: Performance Dashboard

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.

The active XEvent Sessions report (available from the main report as a drill through option) shows you all the XEvent sessions currently active on the instance only with their configuration and events being captured. You will always see the system_health session active on a default SQL Server 2012 installation (unless you have disabled this).

For a default SQL Server 2012 installation, you will always see two active XEvent sessions as it picks the information from sys.dm_xe_sessions output, which shows the system_health session and the sp_server_diagnostics session. The databases overview report available by clicking on the databases option give you additional information about the databases on the instance as compared to it’s predecessor, namely the Log Reuse Wait Description and the Page Verify Option:

When you are looking at the Sessions detail (again another drill-down option from the main dashboard), you have the option of hiding/showing system sessions and this will show you all the sessions currently active on the server including background tasks:

These were just a few changes to explain why using the new set of Performance Dashboard reports is a much better idea. I can also configure my SQL Server 2008 and 2008 R2 instances to use the new set of Dashboard reports. The screenshot below shows the SQL Server 2008 R2 instance for the dashboard report.