Which Table or Index is Hogging the Buffer Pool?
Published: Jul 12, 2026
In the last post I showed how we can easily tell which database is hogging our precious resources but, although that’s useful in helping us narrow down rogue processes, it would be a lot more helpful to be able to go a step further, picking out the exact table or index.
Therefore I’ve written some code that can do exactly that…
As mentioned, this is an extension of the previous post and therefore we’ll simply be expanding the code that we had there in order to get to a level below…
The above query uses buffer_descriptors but also links through Partitions, Indexes, and Objects to narrow down to the specific index used (and if the Index is the clustered or the heap, then obviously that’s simply the table itself).
So… using the same sales.salesOrderDetailEnlarged table as in my previous post, because I did a simple select *, we can see that the Clustered Index is the one which is in the Buffer Pool:
So… when this is so handy, why would we need to use the query from the previous post?
Well, it’s simple really… the query above returns data only for the database within which it’s being executed. Therefore the query from the previous post will narrow down the database to look at, and then the query above will narrow down to the table or index.
Obviously you could write this query to run across all databases and collect the data into one large table (which I have been known to run occasionally), but I’ll leave that up to you… let’s face it, it’s unfair if I give you all the answers ;o)
Therefore I’ve written some code that can do exactly that…
As mentioned, this is an extension of the previous post and therefore we’ll simply be expanding the code that we had there in order to get to a level below…
if object_id('tempDB..#pagesInMemory') is not null drop table #pagesInMemory
select t.name tableName, i.name indexName, count_big(*) noPages
into #pagesInMemory
from sys.dm_os_buffer_descriptors b
inner hash join sys.allocation_units a
on b.allocation_unit_id = a.allocation_unit_id
inner hash join sys.partitions p
on a.container_id = p.partition_id
inner hash join sys.indexes i
on p.index_id = i.index_id
and i.object_id = p.object_id
inner hash join sys.tables t
on i.object_id = t.object_id
where database_id = db_id()
group by t.name, i.name
select *, convert(decimal(20, 2), convert(bigint, noPages) * 8192 / 1024 / 1024.) usedMB
from #pagesInMemory
order by usedMB descThe above query uses buffer_descriptors but also links through Partitions, Indexes, and Objects to narrow down to the specific index used (and if the Index is the clustered or the heap, then obviously that’s simply the table itself).
So… using the same sales.salesOrderDetailEnlarged table as in my previous post, because I did a simple select *, we can see that the Clustered Index is the one which is in the Buffer Pool:
So… when this is so handy, why would we need to use the query from the previous post?
Well, it’s simple really… the query above returns data only for the database within which it’s being executed. Therefore the query from the previous post will narrow down the database to look at, and then the query above will narrow down to the table or index.
Obviously you could write this query to run across all databases and collect the data into one large table (which I have been known to run occasionally), but I’ll leave that up to you… let’s face it, it’s unfair if I give you all the answers ;o)