Performance Dashboard Main Report Quirkiness

I was attempting to view the performance dashboard main report today and ran across an interesting "quirk".

If your server has any connections that are more than 24.8 days old, the report will not run. Why 24.8 days? That is how many milliseconds it takes to overflow the datediff(ms,,) function – 24.855134837962962 days gets you a bit closer, but is unlikely to yield much additional information J The system in question had connections nearing two months old. It didn't get it's monthly reboot last month and hasn't failed over.

This bug is in the msdb.MS_PerfDashboard.usp_Main_GetSessionInfo stored procedure. The fix – short of occasionally restarting your SQL service—is to change the total_elapsed_time calculation:

------------------------------------------------------------------------------

--Changed per msdn forum

--FROM

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

--TO

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,

-------------------------------------------------------------------------------