Performance dashboard is developed for SQL Server 2005 which provides useful information about the server, in simple it will give you an performance overview of entire SQL Server. If you worked with SQL Server 2005 then you might be familiar with this tool. Unfortunately they didn’t provide or updated the tool to work with SQL Server 2008\R2, however based on user request we have done an workaround to make SQL 2005 reports to work with SQL 2008\R2. The good news is now MS have upgraded performance dashboard to SQL Server 2012 which now includes extended event trace details.
Below are the things that are covered in performance dashboard.
- 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)
- Latch contention
Let’s now discuss how can we enable the same in SQL Server 2012.
- Download SQL Server 2012 Performance Dashboard setup and install it.
- Now open SSMS and connect to the server
- Now open the setup file (setup.sql) from the folder “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\” and execute it it
- Where X: is the SQL binary folder drive
- For x86 the program folder is “Program Files” and for x64 its “Program Files (x86)”
- Now it’s time to open the report file. Right click on the servername in SSMS and then click on “Reports” and then “Custom Reports” as shown below. Now you could find a report rdl (performance_dashboard_main.rdl) file in the same directory open it
You will get a warning message before executing it from SSMS, ignore that and click “Run” button.
That’s it you will get all the performance overview of the server, just click on the links in the report to get more details of the report.
This tool is very useful to get overall health status of the server so you can get an over all idea where its going wrong. Hope this tool will help you guys to get some information about SQL Server health in the first instance itself.