Tuesday, June 26, 2012

SQL Server 2005 Performance Dashboard in SQL Server 2008


Many of you DBA’s out there would have heard about the SQL Server 2005 Performance Dashboard Reports and used it before. I find the reports highly useful and time saving, it allows me to get a picture of how my SQL Server is performing.

For those who don't know about it, you can download it from here and add it as a custom report to Management Studio (SSMS). It is a free download! Read on below to find out how to install it and use it on your SQL Server. Also there are included custom modified files that allow you to use this for a SQL Server 2008 instance. This is provided "as is" with no guarantees.

Introduction:


The report are mainly intended to quickly identify performance problems and help to resolve them. These reports use the DMV's that came out with SQL 2005. so they are very fast & reliable information when troubleshooting a performance problem. Some of the common problems that can be detected using the Dashboard Reports are:


  1. CPU bottlenecks (which queries are consuming the most CPU and Plan for this query)
  2. I/O bottlenecks (wich queries are performing the most IO and plan for this query)
  3. Index recommondations generated by the query optimizer (missing index recommendations)
  4. Blocking
  5. Latch contention and other Wait Types
Pre-requisite:

  1. You need to have Management Studio installed on the machine where you intend to use the Performance Dashboard Reports (could be either client or server machine).
  2. Your SQL Server 2005 needs to have at a minimum SP2 applied (build 9.0.3042 or greater)
Installation:

  1. You can install the Performance Dashboard Reports from here.
  2. Once you install the above, go to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard and run the setup.sql script against the SQL instance you want to monitor the performance for.

No comments:

Post a Comment