Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).

share|improve this question
sp_spaceused returns data about the size stored on disk, not what's in memory / in the buffer cache. – Mark S. Rasmussen Feb 29 '12 at 20:59
up vote 5 down vote accepted
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
share|improve this answer
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 '12 at 12:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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