Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SSMS. These reports will allow the DBA’s to quickly identify whether there is a current bottleneck on their system and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
Common performance problems that the dashboard reports may help to resolve include:
– CPU bottlenecks (and what queries are consuming the most CPU)
– IO bottlenecks (and what queries are performing the most IO)
– Index recommendations generated by the query optimizer (missing indexes)
– Blocking
– Latch contention
The information captured in the reports is retrieved from the DMV’s. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring the DB server.
Here are the detailed steps of Installation/Configuration of Performance Dashboard-
(a) Installing SQL Server 2012 Performance Dashboard Reports
The following steps will install the Performance Dashboard Reports-
1) 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
2) Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click ‘Next’.
3) 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’.
4) The next screen will ask you the required registration information. Enter the details and click ‘Next’.
5) You will be asked to choose the program features for installation. Go with the default selection and click ‘Next’.
6) 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.
7) Click ‘Finish’ to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.
(b) 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.
(c) 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.
Sample reports are pasted here for your further reference.
Happy Learning.
Cheers!
Aman Kharbanda
SQLServerPosts 