25112
asked on
ORDER by gives error 'Arithmetic overflow error converting expression to data type int.'
if you comment out the ORDER by, then there is no error.. what causes error on ORDER by?
SELECT TOP 25
obj.[name],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
GROUP BY obj.name, obj.index_id
--ORDER BY Buffered_Page_Count DESC
Which version of SQL? This query with the ORDER BY works on my 2008R2 and 2012 boxes.
What happens if you do this?
I've never been able to get a column alias to work like you're trying. I usually have to use the actual aggregate function. Maybe I'm doing something wrong myself?
SELECT TOP 25
obj.[name],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
GROUP BY obj.name, obj.index_id
ORDER BY count(*) DESC
I've never been able to get a column alias to work like you're trying. I usually have to use the actual aggregate function. Maybe I'm doing something wrong myself?
ASKER
hmm.. you did not get error.. could it my system memory or such internals?
ASKER
Daniel - I get the error on your code also.
I use sql 2008.
I use sql 2008.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting. I don't get it in 2008, 2008R2, 2012, or 2014.
Do you still get it when you replace Buffered_Page_Count with count(*) in that last line ?
Mike
Do you still get it when you replace Buffered_Page_Count with count(*) in that last line ?
Mike