SQL Server Performance

Overflow Error with Performance DashBoard

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Jul 7, 2010.

  1. sonnysingh Member

    Hi
    I have following error came up when I click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further info (reports for the last 15 minutes).
    "Difference of two datetime columns caused overflow at runtime".
    I have seen the solution and implemented in usp_Main_GetSessionInfo SP but error still coming up....
    "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,"
    Thanks in Advance
    sonnysingh, Jul 7, 2010
    #2

  2. sonnysingh Member

    I have found the answer here...
    http://dbaonline.wordpress.com/2008/07/10/troubleshooting-sql-2005-performance-dashboard/
    sonnysingh, Jul 7, 2010
    #3

  3. Madhivanan Moderator

    Thanks for the solution. This would certainly help those who have similar problems
    Madhivanan, Jul 7, 2010
    #4

  4. sonnysingh Member

    Here are couple of more refers for this Error...
    1. http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
    2. http://www.sqldev.org/sql-server-tools-general/performance-dashboard-reports-57776.shtml
    I have question relate to Dashboard....
    Can we see the code of SPs and various reports as this will very helpful for cater the report as per your need. As all of SPs are locked (or encrypted)?
    Pls help...
    Thanks
    sonnysingh, Jul 16, 2010
    #5

  5. satya Moderator

    I don't think so as the Performance Dashboard is native SQL tool from Microsoft, it may not be circulated the important SPs. Rather you have to design your own code to get the report further..
    satya, Jul 16, 2010
    #6

  6. sonnysingh Member

    Reason to ask this is that I want to weigh up the CPU utilization, number of transactions and memory usage of DB Server... see if DMVs can help me....
    Any Help Pls...
    sonnysingh, Jul 27, 2010
    #7

  7. satya Moderator

    It is easy if you can run PROFILER trace at the point of time running the required Perf.dashboard report, see below that I got for AvgIO:
    exec sp_executesql @stmt=N'begin try
    select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
    , (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
    , creation_time
    , last_execution_time
    , (total_worker_time+0.0)/1000 as total_worker_time
    , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
    , total_logical_reads as [LogicalReads]
    , total_logical_writes as [LogicalWrites]
    , execution_count
    , total_logical_reads+total_logical_writes as [AggIO]
    , (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
    , case when sql_handle IS NULL
    then '' ''
    else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
    end as query_text
    , db_name(st.dbid) as database_name
    , st.objectid as object_id
    from sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(sql_handle) st
    where (total_logical_reads+total_logical_writes ) > 0
    order by [AvgIO] desc
    end try
    begin catch
    select -100 AS row_no
    , 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
    , ERROR_NUMBER() AS execution_count
    , ERROR_SEVERITY() AS AggIO
    , ERROR_STATE() AS AvgIO
    , ERROR_MESSAGE() AS query_text
    end catch',@params=N''
    satya, Aug 2, 2010
    #8

Share This Page