Link to home
Start Free TrialLog in
Avatar of 25112
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

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Which version of SQL?  This query with the ORDER BY works on my 2008R2 and 2012 boxes.
What happens if you do this?

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

Open in new window


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?
Avatar of 25112
25112

ASKER

hmm.. you did not get error.. could it my system memory or such internals?
Avatar of 25112

ASKER

Daniel - I get the error on your code also.
I use sql 2008.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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