Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am trying to retrieve data cache size from my sql instance. This is the query I have used for that.

SQL
SELECT count(*)*8/1024 AS 'Data Cache Size(MB)',
CASE database_id WHEN 32767 THEN 'RESOURCEDB' ELSE db_name(database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id),database_id ORDER BY 'Data Cache Size(MB)' DESC


Now I want to get the sum of cache value, and i have tried it as follows....

SQL
SELECT sum(count(*)*8/1024) AS 'Data Cache Size(MB)',
CASE database_id WHEN 32767 THEN 'RESOURCEDB' ELSE db_name(database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id),database_id ORDER BY 'Data Cache Size(MB)' DESC


Now it throws the following exception

XML
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.


Any help on this would be great.

Regards
Sebastian
Posted

1 solution

Try this :
SQL
select sum(data) as 'Data Cache Size(MB)' from 
(
SELECT count(*)*8/1024 AS 'data',
CASE database_id WHEN 32767 THEN 'RESOURCEDB' ELSE db_name(database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id),database_id ORDER BY 'Data Cache Size(MB)' DESC
) as t


You can add your group by columns to the above.
 
Share this answer
 
Comments
Sebastian T Xavier 19-Jul-12 4:55am    
Good catch! Don't know how I missed it?
Sebastian T Xavier 19-Jul-12 5:09am    
my 5+
Mehdi Gholam 19-Jul-12 5:18am    
Cheers!
sandeep nagabhairava 19-Jul-12 7:03am    
good job... my 5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900