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

By:   |   Comments (12)   |   Related: > 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:

Installation Performance DashBoard

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:

Installation Complete Performance DashBoard

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.

    Right Click 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.

    DashBoard Reports

  4. Accept by clicking Run.
    Accept Run Custom

  5. You've now successfully setup Performance Dashboard custom Reports that are compatible with SQL Server 2008 and 2008 R2.

Sample Performance Dashboard

Accept Run Custom

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.

Accept Run Custom

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Norman Chan Norman Chan is a SQL Server DBA with over 12 years of IT experience as a software developer and DBA.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, February 17, 2014 - 9:05:52 PM - Asif Back To Top (29481)

Hi,

I try to use this tool. installed the performanceDashboard on SQL 2008 R2 instance. When i execute the setup.sql it comes up with error. it says

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6

Invalid column name 'cpu_ticks_in_ms'.

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.

any help to resolve this will be much appreciated

Cheers,

Asif

Tuesday, December 10, 2013 - 10:23:02 AM - Asif Iqbal Back To Top (27751)

Good This is help ful.

Tuesday, July 9, 2013 - 3:22:02 PM - Gabe Back To Top (25762)

This is so cool 

Tuesday, March 12, 2013 - 1:12:38 PM - Avinesh Back To Top (22754)

This is awesome tool.

Thanks

Tuesday, March 12, 2013 - 12:53:59 PM - Woody Back To Top (22749)

Very good, Norm. Thank you.

Tuesday, March 12, 2013 - 10:59:10 AM - Chuck Back To Top (22744)

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 5, 2013 - 2:46:35 AM - NormC Back To Top (22562)



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

Friday, March 1, 2013 - 10:26:12 AM - Seth Delconte Back To Top (22506)

Thanks!

Thursday, February 28, 2013 - 10:28:45 AM - Adam Back To Top (22479)

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

Thursday, February 28, 2013 - 10:09:02 AM - Woody Back To Top (22478)

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 - 9:06:58 AM - John Waclawski Back To Top (22473)

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 - 12:28:21 AM - Yadav Back To Top (22463)

Nice Information..! Thanks














get free sql tips
agree to terms