patternsqlMinor

How much memory is a table taking up?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

muchmemoryhowtabletaking

Problem

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

Solution

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,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
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

Code Snippets

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,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
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

Context

StackExchange Database Administrators Q#14150, answer score: 9

Revisions (0)

No revisions yet.