Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Which Table or Index is Hogging the Buffer Pool?
Published: Dec 03, 2020
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…

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 desc


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)

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.