SQL Server Performance Dashboard comprises a set of custom reports that give you nitty gritty details about the performance of your SQL Server instance. Using these reports, you can keep track of the performance issues confronted by SQL Server and get significant help to resolve these issues without having to run T-SQL queries. SQL Server 2012 has introduced new enhancements to the Performance Dashboard toolset.

In order to use SQL Server 2012 Performance Dashboard Reports, you do not require installing Reporting Services. You can access these report files through the Custom Reports feature of SQL Server Management Studio. The data fetched from these reports can be used to diagnose performance issues, including CPU bottlenecks, IO bottlenecks, blocking, and latch contention. You may also get recommendations about creating and modifying indexes (from query optimizer).

You can download SQL Server 2012 Performance Dashboard Reports from the Microsoft Download Center and get started. The below given tips will guide you how to install and use SQL Server 2012 Performance Dashboard Reports in SQL Server 2012. In the later section, you will see what are the key changes made to the Performance Dashboard in SQL Server 2012.

Installing SQL Server 2012 Performance Dashboard Reports

The following steps will install the Performance Dashboard Reports.

• Open the below link in your web browser and download SQL Server 2012 Performance Dashboard Reports: http://www.microsoft.com/en-us/download/details.aspx?id=29063&WT.mc_id=aff-n-in-loc--pd

• Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click 'Next'.

• The next screen will present you with the license agreement. Read the agreement, select 'I accept the terms in the license agreement', and click 'Next'.

• The next screen will ask you the required registration information. Enter the details and click 'Next'.

• You will be asked to choose the program features for installation. Go with the default selection and click 'Next'.

• On the next screen, click 'Install' to begin the installation process. You can choose to click 'Back' button to make any other changes to your installation settings.

• Click 'Finish' to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.

Configuring SQL Server 2012 Performance Dashboard Reports

After finishing the installation, it is necessary to configure the SQL Server to use the Performance Dashboard toolset you have just installed. The default installation directory for SQL Server 2012 Performance Dashboard Reports is 'C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\'. Navigate to this location, search for the 'setup.sql' file, double-click it to open the Performance Dashboard Reports in SQL Server Management Studio. Next, establish connection to the SQL Server Database Engine instance where you need to install and access the reports. Hit 'F5' to start configuring the Performance Dashboard Reports.

Using SQL Server 2012 Performance Dashboard Reports

After you have successfully installed Performance Dashboard Reports, you need to consider the following:

• You need to ensure that all functions and procedures accessed by the queries in reports must be present in all instances of SQL Server for which you need to carry out performance analysis. You need to search for the 'setup.sql' file after navigating to the installation directory and run the script. Close the window after the execution is finished.

• Go to Object Explorer in SQL Server Management Studio, right-click on the server instance, and select 'Reports' → 'Custom Reports'. In the 'Open File' dialog box, select the 'performance_dashboard_main' report file and click 'Open'. You will be displayed a warning dialog box stating that you are about to run a custom report. Click 'Run' in this dialog box to open the 'performance_dashboard_main.rdl' file. You will be presented with various charts and hyperlinks in the report for analyzing the performance of the server.

• You can click links provided in the main report to access the remaining reports. In order to better know how to use these reports, you can view the help file, i.e. PerformanceDashboardHelp.chm.

• On the dashboard, you will also find the extended events ('XEvent') sessions that the instance is currently running. XEvents is a feature in SQL Server that enables you to get information about various events that occur inside the database engine. The XEvents sessions report displays the configuration of all the currently active XEvents sessions on the SQL Server instance and the events captured by these sessions.

• By default, the system_health session is enabled by default in SQL Server 2012. The two sessions (i.e. the system_health session and the sp_server_diagnostics session) are always active on a default installation of SQL Server 2012.

• In SQL Server 2012, the databases overview report provides more information about the databases than that given by any previous version of Performance Dashboard Report. The additional options that you can view in the report are 'Log Reuse Wait Description' and the 'Page Verify Option'. You can view the databases overview report by clicking the database option in the main report.

• You can also view details of the System Sessions using the Sessions option (a drill-down option available on the main dashboard). If you take a sneak-peek at the Sessions details, you will see all the sessions that the server is currently running. It will also display information about the background tasks including other details, such as memory usage, logical reads, physical reads, and more.

You can also use SQL Server 2012 Performance Dashboard Reports to diagnose performance issues for SQL Server 2008 and 2008 R2 instances.