Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)?

For example, suppose I have a table with 30 million records. I'd like to know how many pages belonging to this table are currently on the buffer cache, including index, data and text/image pages.

I found this query by Pinal Dave, but it seems this query only returns pages allocated by indexes (be it clustered or unclustered ones).

  • 2
    sp_spaceused returns data about the size stored on disk, not what's in memory / in the buffer cache. Feb 29, 2012 at 20:59

1 Answer 1

with bd as (
    select count(*) as pages_in_memory, bd.allocation_unit_id
    from sys.dm_os_buffer_descriptors bd
    where bd.database_id = db_id()
    group by bd.allocation_unit_id)
select p.object_id,
    au.total_pages as pages_on_disk,
from bd 
join sys.allocation_units au 
    on au.allocation_unit_id = bd.allocation_unit_id
join sys.partitions p
    on p.partition_id = au.container_id
  • If you add some comment to your answer, I'll select it. As it stands, it's not an answer, just some piece of code. (Yes, I know it works, but that is not my point.)
    – ivanmp
    Mar 1, 2012 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.