Search This Blog & Web

Tuesday, November 13, 2012

Performance Dashboard to work on Optimization tasks

Question
Most of the time I need to work on any optimization tasks I some steps to find out problem in query but this helps when I was doing SQL Optimization. But when there is any Administration tasks then I need to know some easy way to find my problem.

Answer:
There are a lot of tools available in the market like ApexSQL and RedGate etc. But we need to purchase these tools to get maximum benifit.
I am looking for free tools that works with SQL Server Managment Studio and easy to use and I find SQL Server Performance Dashboard.

It is easy to use and generate enoumrous reports using SSMS. Have a look at it.

 
This is Performance dashboard report main page and we can drill down using links to view detail like.
 
1) CPU bottlenecks (which queries are consuming the most CPU and Plan for this query)
2) IO bottlenecks (which queries are performing the most IO and Plan for this query).
3) Index recommendations generated by the query optimizer (missing index recommendations pulled from sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats)
4) Blocking
5) Latch contention and other Wait Types
 
Pre-Requisites to Performance Dashboard
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. If you are still using SQL Server 2005 the it needs to have at a minimum SP2 applied (build 9.0.3042 or greater) and it is also available for SQL Server 2008

How to Get it:
Please download Performace dashboard msi setup link from here
http://www.microsoft.com/en-us/download/details.aspx?id=22602

If you are using SQL Server 2008 (First install above setup and then download new dashboard from following link)
http://www.cshandler.com/2011/09/performance-dashboard-reports-in-ssms.html

How to Install:
For SQLServer 2005
1. Run the setup exe downloaded 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 that you want to monitor the performance for.
 
For SQLServer 2008 R2
1. Run the setup exe downloaded from HERE.
2. Once you install the above. Download the attachment from the link at end of this post and extract the files. You’ll find two files there copy them and paste them to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard. Overwrite the old files and run the setup.sql script against the SQL instance that you want to monitor the performance for.

Hint: If you are installing this setup on SQL SERVER 2008 R2 Setup.sql file attached setup. It might generate error. You need to change column name



Result:
It is quite usefull for me to get performace and find problems in my daily working. Use it and Comment what you feel about it.
 
Post a Comment