Where do I find the Performance Dashboard for SQL Server 2008?

By: Norman Chan   |   Read Comments (9)   |   Related Tips: More > SQL Server Management Studio

Problem

I’ve downloaded and installed Performance Dashboard reports for SQL Server 2005 from Microsoft’s download site, but I can’t find Performance Dashboard for SQL Server 2008?  How can I use this for SQL Server 2008 and 2008R2?

Solution

Unfortunately Microsoft does not provide Performance Dashboard for SQL Server 2008 for download, but there is a work-around by installing Performance Dashboard for SQL Server 2005 and modifying the provided Performance Dashboard T-SQL setup script from Microsoft. We will walk through the steps below.

Overview

Performance Dashboard is a set of (SQL Server Reporting Services) SSRS reports that are loaded into our SQL Server Management Studio (SSMS) built using Dynamic Management Views (DMVs) intended to help quickly identify potential performance problems within SQL Server. This allows the DBA to view reports for CPU and IO bottlenecks through several pre-written reports in areas such as top CPU, duration, logical reads, physical reads, logical writes, CLR time, waits, missing indexes and IO information. This tool is convenient for DBAs to quickly see information about key SQL Server performance statistics in one “dashboard” (place). DMVs are not obsolete with this tool, if more research is required we can dig deeper querying specific DMVs, but by using the Performance Dashboard it provides an excellent starting point. Although it is a great tool, keep in mind these reports come from DMVs, which are not static and only show the current status of SQL Server, therefore once SQL Server is restarted the historical statistics from DMVs are lost.

This package is provided for download by Microsoft. It’s comprised of two key components Performance Dashboard custom reports that will be installed on the client machine for use with SSMS and a T-SQL setup script with custom stored procedures utilized by the Performance Dashboard reports. The reports are stored locally on our SSMS client after installation. In order for reports to work it requires executing the T-SQL setup script on each SQL Server we wish to use with Performance Dashboard reports to install the custom stored procedures.

Download

First download Performance Dashboard reports for SQL Server 2005 from Microsoft. http://www.microsoft.com/en-us/download/details.aspx?id=22602

Installation

Install Performance Dashboard on our workstation accepting the default locations, or you can choose a different installation location:

By default it is installed in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard

Note: If the default directory changes, you will need to remember to change the path appropriately for the following steps outlined in this tip.

A successful installation message will be displayed when complete:

Post Installation Setup

Post-Installation has two parts:

• Executing a modified version of Microsoft’s custom T-SQL script (setup.sql)
• Setting up Performance Dashboard in SQL Server Management Studio (SSMS)

The first part requires executing Microsoft’s “setup.sql” script on the SQL Server we wish to use the Performance Dashboard. This can be found in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard where it was installed.

1. Connect to the SQL Server we would like to utilize SQL Server Performance Dashboard. (e.g. MYSQLSERVER )
2. Open a New Query Window in SSMS for that SQL Server.
3. Open file setup.sql located: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard
4. Use the Find feature (CTRL-F) look for keyword: select @ts_now = cpu_ticks
5. The find feature will help us locate where to change setup.sql from the old code:
    
   select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
   
   to the new code:
    
   select @ts_now = ms_ticks from sys.dm_os_sys_info
   
   
6. After the code change, we should be able to successfully execute the setup.sql script.

If we try to run the original setup.sql on a SQL Server 2008 or 2008 R2 server before making the changes above, we get this error:

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6 Invalid column name 'cpu_ticks_in_ms'.

This is due to a change in the sys.dm_os_sys_info view, which removes “cpu_ticks_in_ms” in versions of SQL Server 2008 and newer. (note: If we have a SQL Server 2012 instance and attempt to install the available SQL 2012 Performance Dashboard download, that particular stored procedure has also been fixed in the same manner using “ms_ticks” instead of calculating with “cpu_ticks_in_ms”)

The second part of post-installation requires setting up SQL Server Performance Dashboard for the first time by providing SSMS the location of our Performance Dashboard reports.

1. We should be connected to the SQL Server we ran setup.sql on earlier (e.g. MYTESTSQLSERVER)
2. Right-Click the SQL Server, navigate to the server level reports section, select Custom Reports.
   
   
   
   
   
3. A pop-up window will appear, navigate to Performance Dashboard’s installation directory: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard

   If you have chosen a different file path during installation, you will need to use that file path to find your Performance Dashboard folder.

   Select “performance_dashboard_main” and click open.

   
   
   
4. Accept by clicking Run.
   
   
   
   
   
5. You’ve now successfully setup Performance Dashboard custom Reports that are compatible with SQL Server 2008 and 2008 R2.

Sample Performance Dashboard

Viewing the Dashboard

Next time we would like to view the Performance Dashboard, we navigate to server level reports and open “performance_dashboard_main” for our SQL Server.

Conclusion

By configuring the Performance Dashboard for SQL Server 2005 to be used for SQL Server 2008 we can utilize this free tool from Microsoft to aid in trouble-shooting database performance issues. Although it may become unsupported, so far due to its popularity it has been available directly from Microsoft or via work-arounds for all versions of SQL Server beginning with SQL Server 2005 and newer. The most recent version available is for SQL Server 2012.

Next Steps

• Run the setup.sql Performance Dashboard script on all of your SQL Server 2008 and 2008R2 Servers to save time in troubleshooting issues: http://www.mssqltips.com/sql-server-tip-category/140/central-management-servers/
• Learn more about Dynamic Management Views: http://www.mssqltips.com/sql-server-tip-category/31/dynamic-management-views-and-functions

Last Update: 2/28/2013

About the author

Norman is a SQL Server DBA with over 12 years of IT experience as a software developer and DBA.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, February 28, 2013 - 12:28:21 AM - Yadav	Read The Tip
Nice Information..! Thanks

Thursday, February 28, 2013 - 9:06:58 AM - John Waclawski

Read The Tip

Norman,   Thanks for the info on this report.  Although I am getting the following error when I follow your steps to the letter: ERROR: The datediff function resulted in an overflow.  The number of dateparts seperating two date/time instances is too large.  Try to use datediff with a less precise datepart.   Any suggestions?  I'm digging around but thought I'd throw this out there in case some one else is experiencing the same issue.

Thursday, February 28, 2013 - 10:09:02 AM - Woody

Read The Tip

John, I too have had a similar problem with the datetime data type when transforming Oracle tables into SS2008. It turned out that by default SS2008 was converting the Oracle datetime data type into the SS2008 smalldatetime data type. I was able to fix it by changing the data type in that field to either datetime or datetime2. Afterwards, I received no more overflow errors when doing the translations. I would be interested to know if this tip helps. Best regards. Woody

Thursday, February 28, 2013 - 10:28:45 AM - Adam	Read The Tip
Additionally you can just download the 2012 Dashboard Reports and not worry about altering the 2005 reports.  http://www.microsoft.com/en-ca/download/details.aspx?id=29063

Friday, March 01, 2013 - 10:26:12 AM - Seth Delconte	Read The Tip
Thanks!

Tuesday, March 05, 2013 - 2:46:35 AM - NormC

Read The Tip

Hi John, Apologies for the delayed response as I was away. The overflow occurs because stored procedure created in the MSDB by Performance Dashboard called MS_PerfDashboard.usp_Main_GetSessionInfo attempts to sum total login time using DATEDIFF in "milliseconds" with getdate() Current time.  Since the DATEDIFF function returns an INT value that has have a maximum value of 2,147,483,647 (ref: http://msdn.microsoft.com/en-us/library/ms189794.aspx)  the results can far exceed  2,147,483,647 millisconds  (24.8 days) if a server has not been restarted(SQL Server Agent) An overflow can occur. Hope this helps explain the overflow error, great point by Woody on potential overflow problems on conversions from different systems.   If you are curious to further explore run the following Query below on the SQL Server you are encoutering issues to verify: select login_time, [PROGRAM_NAME] , [status], [HOST_NAME], datediff(day,login_time,getdate()) as [DaysLoggedIn] from sys.dm_exec_sessions where is_user_process = 0x1 order by login_time asc This query will show all non-system sessions. If you visually sum the number of days it is over 24.8 days, and we can confirm the overflow can occur from these typically logged in sessions: SQLAgent - Email Logger SQLAgent - Generic Refresher SQLAgent - Job invocation engine The resolution is taken from Microsoft Performance Dashboard SQL Server 2012, only modifies portions code that is required. Another poster Adam made that great point about using SQL 2012 also, but we are only using what is needed. Due to compatibility differences SQL Server 2012 Performance Dashboard would not work on SQL Server 2008 if trying to apply SQL 2012 setup.sql, therefore the following below are instructions to solve your issue.   1. Please make a change in [usp_Main_GetSessionInfo] besure you are in MSDB   2. Type:  Use MSDB     (enter) 3. Run the following code below: if object_id('MS_PerfDashboard.usp_Main_GetSessionInfo', 'P') is not null     drop procedure MS_PerfDashboard.usp_Main_GetSessionInfo go create procedure MS_PerfDashboard.usp_Main_GetSessionInfo as begin     select count(*) as num_sessions,         sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,         sum(convert(bigint, s.cpu_time)) as cpu_time,         case when sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) > 0             then sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time))             else 0         end as wait_time,         sum(convert(bigint, MS_PerfDashboard.fn_DatediffMilliseconds(login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,         case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))             else NULL             end as cache_hit_ratio     from sys.dm_exec_sessions s     where s.is_user_process = 0x1 end go grant execute on MS_PerfDashboard.usp_Main_GetSessionInfo to public go 4.  Afterwards in a new session preferably (still in MSDB) run the following: if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_DatediffMilliseconds'), 'IsScalarFunction') = 1     drop function MS_PerfDashboard.fn_DatediffMilliseconds go create function MS_PerfDashboard.fn_DatediffMilliseconds(@start datetime, @end datetime) returns bigint as begin     return (datediff(dd, @start, @end) * cast(86400000 as bigint) + datediff(ms, dateadd(dd, datediff(dd, @start, @end), @start), @end)) end go -Norm

Tuesday, March 12, 2013 - 10:59:10 AM - Chuck	Read The Tip
Norm, Thanks for the update. I had seen several hacks to the SessionInfo but this is one of the more elegant ones.  I have changed over to it and wanted to drop you a note saying thanks for the lovely work. Your Friendly Neighborhood DBA, Chuck

Tuesday, March 12, 2013 - 12:53:59 PM - Woody	Read The Tip
Very good, Norm. Thank you.

Tuesday, March 12, 2013 - 1:12:38 PM - Avinesh	Read The Tip
This is awesome tool. Thanks

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 Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. Get your SQL Server database under version control now! Find out why... Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. The SQL Server Security THREAT - It’s Closer Than You Think