Dz0001


Donwload the sample from the site

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

, follow the inftruction and run the setup.sql script to create requried sp and udf at msdb, SQL server is on WIN2003 and SP2 for sql server 2005.

But I got the following error when running performance_dashboard_main reports and I got the following error

"Difference of two datetime columns caused overflow at runtime"

It looks like the

procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]

creating error, could you please take a look

Thanks





Re: Error running SQL 2005 Performance Dashboard reportDonwload the sample from the site

bwunder


The problem occurs on my system if I have a login that has been connected for over 24 days. I "fixed" it by giving up a little precision if the connection is old by changing the query to:

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, case when datediff(day, login_time, getdate()) < 24
then datediff(ms, login_time, getdate())
else datediff(s, login_time, getdate()) * 1000
end)) - 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

You still get millisecond precision if the connection is younger than 24 days but only second precision if older than 24 days. This will support connections that have beem in for about 65 years. That's a long time between rebootsWink

I agree it would be better if they corrected the download, but if you change it in the setup.sql in your local PerformanceDashboard folder it is idempotent and you can redeploy when you see the error on any server.

fwiw: staying connected for 24 days is not a generally recognized best practice for a user SPID.






Re: Error running SQL 2005 Performance Dashboard reportDonwload the sample from the site

Dz0001

Hi Bwunder,

Thanks for your tips, I will test it out right away!

By the way, since staying connected for more that 24 days is not good practices, how do you normally handle this if you don't mind.







Re: Error running SQL 2005 Performance Dashboard reportDonwload the sample from the site

bwunder

Depends on what you are doing with the connection.

One good place to investigate the answers would be the in the .NET Data Access Architecture Guide
http://msdn2.microsoft.com/en-us/library/ms978510.aspx#daag_managingdatabaseconnections