Mimic DBCC Memusage

Does anyone have a routine that returns results like DBCC MEMUSAGE used to return in older versions of SQL Server?

I think sys.dm_os_buffer_descriptors gets you close. Books Online shows a query that assembles some of the data you are looking for. I've modified this to be a bit more user-friendly.

Hope this helps,
Bryan

Code Snippet

SELECT

x.name,

obj.name as [object_name],

index_id,

count(*)AS cached_pages_count,

is_modified

FROMsys.dm_os_buffer_descriptorsAS bd

INNERJOIN

(

SELECTobject_name(object_id)ASname

,index_id ,allocation_unit_id

FROMsys.allocation_unitsAS au

INNERJOINsys.partitionsAS p

ON au.container_id = p.hobt_id

AND(au.type = 1 OR au.type = 3)

UNION ALL

SELECTobject_name(object_id)ASname

,index_id, allocation_unit_id

FROMsys.allocation_unitsAS au

INNERJOINsys.partitionsAS p

ON au.container_id = p.hobt_id

AND au.type = 2

)AS obj

ON bd.allocation_unit_id = obj.allocation_unit_id

innerjoinsys.databases x

on bd.database_id=x.database_id

GROUPBY x.name, obj.name,index_id, is_modified

ORDERBY 1, 2, 3, 5

|||

Thanks, Bryan. This is similar to what I presently have, but it might fix the problems; hang on and I'll give this a try.

This looks pretty good; thank you.