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.
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.
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.
Connect to the SQL Server we would like to utilize SQL Server Performance Dashboard. (e.g. MYSQLSERVER )
Open a New Query Window in SSMS for that SQL Server.
Open file setup.sql located: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard
Use the Find feature (CTRL-F) look for keyword: select @ts_now = cpu_ticks
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
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.
We should be connected to the SQL Server we ran setup.sql on earlier (e.g. MYTESTSQLSERVER)
Right-Click the SQL Server, navigate to the server level reports section, select Custom Reports.
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.
Accept by clicking Run.
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.
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.
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:
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
Friday, March 01, 2013 - 10:26:12 AM - Seth Delconte
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.
Thursday, February 28, 2013 - 9:06:58 AM - John Waclawski