Sunday, March 25, 2012

@@CPU_BUSY stops incrementing and generates "Arithmetic overflow occurred"

I am using several globals including @.@.CPU_BUSY to log the performance of some stored procedures. It works fine on my dev server, but in production the value reported by @.@.CPU_BUSY on my production server has stopped at 134217727. And when I try to assign its value to a variable, it always generates an error.

For example:

DECLARE @.FOO BigInt
Set @.foo = @.@.CPU_BUSY
print @.Foo


generates

Arithmetic overflow occurred.
134217727

Note that

DECLARE @.FOO BigInt
Set @.foo = 134217727
print @.Foo


works fine.

I understand that when the value of @.@.CPU_BUSY reaches a certain point, its value becomes inaccurate according to MSDN TSQL Reference. I've also seen some descriptions that say the value is supposed to "wrap" back to zero.

So the questions are:
Is the value of @.@.CPU_BUSY supposed to wrap back to zero when it overflows?
Is there a better/more reliable way to do this?

Cheers,
Jeff
by the way:
Select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)sql

No comments:

Post a Comment