Performance Dashboard error "Difference of two datetime values caused an overflow at runtime"


 


Have you tried using SQL Server Performance Dashboard?  We're using this tool on SQL Server Escalation team with customers to troubleshoot real-time performance problems.  It is a great way to quickly find queries with high-CPU, high-reads, waits, etc.


 


SQL Server 2005 Performance Dashboard Reports


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


 


We discovered a problem with one of the stored procedure when it calculates idle_connection_time for connections > 24 days.  The revised stored procedure usp_Main_GetSessionInfo corrects this problem.


 


USE [msdb]


GO


/****** Object:  StoredProcedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]    Script Date: 06/19/2008 15:35:54 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


 


ALTER 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,


            sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,


            --sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,


            --FIX to correct for sessions connected >24 days


            sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), 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


 


 

Comments (2)

  1. millardjk says:

    While the updated SP took care of the initial report view, I’m still getting the datetime overflow error when using the ‘recent cpu’ drilldown report. I don’t know enough about the report definition to be able to discern what is causing the overflow…

  2. John Marsh says:

    Thanks for the post, it saved me some hunting around.

Skip to main content