SSMS Performance Dashboard Reports in SQL Server to find Bottlenecks

SQL Server developers, DBAs or if you are just a normal developer who often uses the SSMS if you are assigned to optimize the database performance and you're not much familiar or don't want to play around with DMVs and DMFs then Performance Dashboard Reports are for you.

Go and download the performance dashboard reports 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. I have also included custom modified files as an attachment at end of the post that allows you to use this for a SQL Server 2008 instance. This is provided "as is" with no guarantees.

What will you get by these reports?

Well, these reports are created by analyzing the performance aspects of a SQL Server instance. The reports are mainly intended to quickly identify performance problems and help resolve them. These reports use the DMV's that came out with SQL 2005, so they are very fast & reliable 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. 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

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 then it needs to have at a minimum SP2 applied (build 9.0.3042 or greater)

Steps to Install:

For SQLServer 2005

1. Run the setup exe downloaded fromHERE.
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 fromHERE.
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.

Start using the performance reports

Note: The script will create some Stored Procedures, table-valued & scalar functions in the MSDB database. All the SP's created by Dashboard with have the schema MS_PerfDashboard.

If you want more information on the newly created objects, use the query below

select * from sys.objects where schema_id = SCHEMA_ID
('MS_PerfDashboard')

Now, Open a new Management Studio window, right-click on instance name -> Reports -> Custom Reports.

Browse to the location %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard and choose the performance_dashboard_main.rdl file and click OK. This will add the performance dashboard to SSMS.

Next time when you right-click on the instance and go to Reports, you should see the entryperformance_dashboard_main. Clicking on this will launch the Main Dashboard page.

Below is the screenshot of what the performance report will look like:

Few basic fixes you can do quickly:

Missing Indexes

In the Miscellaneous Information section, it'll show you the Missing Indexes link. Clicking on it will open the other reports that will not only show you the Missing index information but also the queries to create them.

Expensive Queries

If you have queries running on your server instance as a job or frequently and are  consuming lots of resources you'll find these reports very useful.

A report for queries taking higher CPU time will be show by the graph, you can click on the bar and find the query details like execution plan, time etc.

Export your reports – Reports should have Export function, so it is there. Just right click on any report and select Excel or PDF.

Sound interesting.. umm then go ahead and explore more.

You can find some more interesting material of using Performance dashboard reports

1. PPT on Performance Dashboard Reports by Amit Banarjee
2. Perfromance Dashboard "troubleshooting problems" Video by Harish Chawla

I hope you will enjoy this small but great Dashboard to tune your SQL Servers.

Download the upadted setup.sql for SqlServer 2008