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.
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:
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.
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.
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.