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,

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

Why use SQL 2008 client tools

Here’s a quick example of a time saving query analysis feature with SSMS 2008.

We want to examine the performance of a query. When we run the query, and ask for the actual execution plan, SSMS 2008 will also display missing indexes that the server thinks would speed the query (this example is estimated to have a 64% impact on the query):

Just right click on the “missing” index (in green) and choose “Missing Index Details…”