SQL Server 2012 Performance Dashboard Report

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)
  • Blocking
  • 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)”

sql_2012_performance_dashboard_1

  • 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

sql_2012_performance_dashboard_2

You will get a warning message before executing it from SSMS, ignore that and click “Run” button.

sql_2012_performance_dashboard_3

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

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.

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

sys.dm_db_stats_properties DMV
Service pack 1 for SQL Server 2012 has been released on 9th Nov 2012. It took couple of weeks for me to install it in our servers. Before I settl...
Performance View on INNER JOIN and OUTER JOIN
Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on perform...
What's new in DTA - SQL Server 2012
DTA stands for Database Tuning Advisor which has introduced from SQL Server 2005. This utility helps the end user to tune their query and give th...
powered by RelatedPosts