SQL Server Performance Monitoring dashboard is SQL server tool developed by Microsoft for monitoring of various performance parameter of SQL server from one centralized locations. With the help of this tool one can know the waiting/lock queries, more CPU centric, more I/O centric queries and many more parameters.
The Microsoft Documentation states that “The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator 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. The Dashboard reports basically retrieve information from SQL Server Dynamic Management Views and hence there is no additional overhead when using these reports.” Common performance problems that the Performance Dashboard Reports may help to resolve include:
• CPU bottlenecks
What queries are consuming the most CPU?
• IO bottlenecks
What queries are performing the most IO operations?
• Index recommendations generated by the query optimizer
What are the potential missing indexes?
What blocking is occurring?
• Latch contention
What are the volume of latches?
Following steps can be followed for installation of the tool.
Step-1: Download “SQLServer2012_PerformanceDashboard.msi from from internet and install the tool , copy the Location path where it is installing.
( download link : Download “SQLServer2012_PerformanceDashboard.msi )
Step-2: Please copy its installation path “C:\Program Files\Microsoft SQL Server\110\Tools\Performance Dashboard”
Step-3: Open SQL Server Management Studio 2012 , and now go to the location where performance monitoring tool is installed i.e “C:\Program Files\Microsoft SQL Server\110\Tools\Performance Dashboard”.
Step-4: Open the setup.sql file through SQL server management Studio 2012. As shown in the following screen and run the SQL script.
Step-5: right Click on SQL Server Instance and go to Custom Reports and click on it as shown in the image.
Step-6: Provide the installation path again “C:\Program Files\Microsoft SQL Server\110\Tools\Performance Dashboard” to locate the file “performance_dashboard_main.rdl” and select the file and click open.
Step-7: Click on run to run the tool, this tool collect all the statistics of Performance from the system memory and present at one single dashboard for performance monitoring.
Step-8: The following screen show the SQL Server Performance Monitoring Dashboard report with various parameters and almost all parameters are having hyperlink to further drill down the report to its lowest level to identify the performance monitoring even upto SQL query level incluing CPU and I/o specific queries. Slow queries etc.
This completes the installation steps.