Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Installing SQL Server 2005 Performance Dashboard Reports

MSSQLTips author Armando Prato By:   |   Read Comments (6)   |   Related Tips: More > SQL Server Management Studio

Problem
I am trying to leverage the new SQL Server 2005 DMVs for performance analysis. While they're very useful, it would be nice to be able to see graphical representations of the data collected by the DMVs. Is there a graphical utility within SQL Server I can use as well?

Solution
In SQL Server 2005 Service Pack 2, Microsoft introduced a new feature that affords DBAs the ability to run Custom Reports. This feature allows DBAs to run their own Reporting Services custom reports from within the SQL Server Management Studio. To leverage this feature, Microsoft has released a Performance Dashboard that you can download and use to identify performance issues in your database server. The dashboard abstracts the data that is collected from the SQL Server 2005 dynamic management views (DMVs). However, it should not be used as a substitute for learning the various DMVs and their purpose. Note that the custom reporting feature is only available as of Service Pack 2 of SQL Server 2005. Both your database server and the Management Studio must be at Service Pack level 2 or greater. You don't need Reporting Services installed to run the dashboard.

Open a Management Studio session and set your focus to the Object Explorer pane. Right clicking on any node will display a Reports option which leads to the Custom Reports option.

You can download the Performance Dashboard from the following link

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

The following is from the web-site in the above link:

Common performance problems that the dashboard reports may help to resolve include:

  • 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

The name of the download file from the above link is DownloadSQLServer2005_PerformanceDashboard.msi. It is a Windows installer file that, when executed, will create a folder named PerformanceDashboard on your server. If you choose the default location, the installer will place the files in the following default location on the server: C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard.

Double clicking the installer, we're presented with the Welcome screen. Click Next.

 

We're presented with License Agreement screen. Enter your license information and click Next.

 

We're presented with a registration screen. Enter your registration information and click Next.

 

Accept the default features by clicking Next

 

We're now ready to install. Click Install

 

Installation is now complete!

 

Now that the Dashboard has been installed, we need to set our server up to run it. Open a new query window within SQL Server Management Studio. Navigate to the location to where you chose to install the dashboard files (the default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard). Execute script setup.sql. This script will connect to the msdb database and install everything needed to run custom reports.

 

Once you've run the set up script, you're ready to run the custom report. Right click on any node in the Object Explorer and click on Custom Reports...

 

A file search dialog box will open. Navigate to the folder where you installed the dashboard files. The dashboard report file is performance_dashboard_main.rdl.

 

Once you open the .rdl file via the dialog, you're presented with the following warning screen.

Clicking Run in the warning dialog, we're presented with the report!

Next Steps

  • Get a feel for the dashboard by running its various sub-reports
  • Read the following blog for pointers about custom reporting in SQL Server 2005
  • Read this Custom Reporting overview in the SQL Server 2005 Books Online
  • Read about MVP Aaron Bertrand's custom report that can be used to examine blocking


Last Update: 8/1/2008


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, August 01, 2008 - 6:28:08 AM - jerryhung Read The Tip

 I've used this couple times, it's nice to see

however I wonder if it works with SQL 2008, I doubt it, but SQL 2008 has a better Activity Monitor that covers some monitors such as Disk IO, Batch Request, Expensive Queries, Processes, etc...


Friday, August 01, 2008 - 11:58:12 PM - rajaobj Read The Tip

I tired configuring Dashboard,but when i run the setup.sql script  I am getting the following error messages.can you please help me out

 

"Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27
'object_schema_name' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42
Incorrect syntax near the keyword 'as'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission."

 

Regards,

Raja


Saturday, August 02, 2008 - 2:44:54 PM - aprato Read The Tip

Do you have SQL Server 2005 SP2 installed on the server?

What do you see in the character_value for ProductVersion
when you run

use master
go
exec xp_msver
go

 


Friday, August 17, 2012 - 4:42:19 AM - David Read The Tip

I've got a SP4,

installed and running rdl,

then

some error occurs,

saying  'version_string' is missing.

 

could anyone help out?


Wednesday, January 16, 2013 - 10:31:48 PM - Dinesh Vishe Read The Tip

the stored Procedure and finction required by dashboard have not been installed.

a database administrator must run SETUP.sql script (supplied with report) 

on each sql instance that will monitored via the dashboard report.

 

 

could not find store procedure 'msdb.Ms_perfDashboard.usp_CheckDependecies'

Please let know how sloved Problem.


Friday, May 23, 2014 - 7:19:01 PM - Frank Read The Tip

Run the Setup.SQL script, and before the SP got created.

 

them run the custom reports.

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.