December 10, 2008 at 10:50 am
Hi,
I have two identical instances of SQL Server 2005 Enterprise Edition running on a Win2k3 Enterprise Edition OS and when I run the following two statements the code works fine on one of the servers but fails with an "arithmetic overflow error" error on each of the statements when I run it on the other. Is there something I can change in the code, I've tried alsorts of different things but none have worked:
selectgetdate(),
ce.type, ce.current_cost,
sum(cp.size_in_bytes) as total_size_in_bytes
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce
on cp.memory_object_address=ce.memory_object_address
where ce.type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP') and
ce.current_cost = 0
group by ce.type, ce.current_cost
go
selectgetdate(),
sum(size_in_bytes)/1000 as total_size_in_KB,
count(size_in_bytes) as number_of_plans,
((sum(size_in_bytes)/1000) / (count(size_in_bytes))) as avg_size_in_KB,
cacheobjtype, usecounts
from sys.dm_exec_cached_plans
group by usecounts, cacheobjtype
order by usecounts asc
go
Thanks in advance.
www.sqlAssociates.co.uk
December 10, 2008 at 10:56 am
Hi Chris
Are you inserting the result on any table, if so then check the table structure, one more thing to consider is that all the information stored will be from the last start of the instance 🙂
December 10, 2008 at 11:24 am
One of the field you are summing is adding up to more than 2.1 billion on one of the servers - this is a data problem.
Convert to a BIGINT first:
SUM(cp.size_in_bytes) becomes SUM(CONVERT(BIGINT,cp.size_in_bytes))
December 10, 2008 at 11:30 am
Check the ARITHABORT setting on each server/database. I bet on the one that is failing - this setting is ON.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 12, 2008 at 1:25 am
Thanks Michael the conversion worked.
www.sqlAssociates.co.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply