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.

Plan Cache Hunt
Published: Jun 26, 2022
This is a follow-up to a previous blog post entitled “Which Table or Index is Hogging the Buffer Pool?”… inventive and exciting title, I know…
What we did in that post was to have a look through the Buffer Pool in SQL Server in order to find what was hogging our most precious resource. This is great to find exactly which table, and which index, is causing us problems, but we need one further crucial step… we need to know WHY that table / index is being pulled into the Buffer Pool in the first place.

To do this let’s leverage the plan cache and have a root around to see if we can find which queries were using that table / index and therefore see if we can find our culprit.

To be honest, this isn’t a golden bullet because we could have hundreds of queries which are accessing the same resource or numerous which are causing scans of indexes etc. However, this will definitely allow you to narrow down your target quite dramatically compared to the number of SQL queries being run against your system as a whole.

Anyway, let’s get to business…

We’ll start by clearing out the plan cache:


Then, using our code from the previous post, we can validate that our cache is empty:

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

Once this has been done we’ll write a few queries… these will all hit my enlarged SalesOrderDetails table but to varying degrees (only one will scan the entire table, pulling it into cache, the others will Seek)…

select *
from sales.SalesOrderDetailEnlarged
where salesOrderID = 43892

select *
from sales.SalesOrderDetailEnlarged
where CarrierTrackingNumber = '4911-403C-98'

select *
from sales.SalesOrderDetailEnlarged
where salesOrderID between 44650 and 44655

My point is that we can run the same Buffer Pool scan and see that we have the whole table in cache… BUT we have a “lot” of queries running on the server and therefore we don’t know what’s caused it…

So what do we do with this information? Well, actually, there’s a way we can dig into the plan cache a little further and SEE if we can find a culprit (yes, that’s in capitals intentionally because there’s no guarantee… but any help we can get is a bonus).

Therefore we can run the following:

-- NOTE:  The index name MUST have [ and ]
declare @indexName varchar(500) = '[PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]';

with xmlnamespaces
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
select s.value('(@StatementText)[1]', 'varchar(max)') sqlText,
o.value('(@Database)[1]', 'varchar(128)') databaseName,
o.value('(@Schema)[1]', 'varchar(128)') schemaName,
o.value('(@Table)[1]', 'varchar(128)') tableName,
o.value('(@Index)[1]', 'varchar(128)') ixName,
o.value('(@IndexKind)[1]', 'varchar(128)') indexType,
p.plan_handle, query_plan
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_query_plan(p.plan_handle) q
cross apply query_plan.nodes('//StmtSimple') n(s)
apply s.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') i(o)
option (maxdop 1, recompile) -- to stop it hogging resources

NOTE: This is adapted from code provided by the insanely intelligent (and entertaining) Jonathan Kehayias – if I can find the link I’ll include it but I’m currently offline so, if I subsequently forget, PLEASE do Google him and his blog!!!

The output of this is as follows:

Therefore you can now see all the queries that used the index in question… as mentioned, if you have thousands then it’ll not be perfect by any stretch, but it does narrow the buffer pool considerably and allow you to potentially spot problem queries.

In the case above, I would personally go straight for the top query purely because it isn’t parameterized and therefore the most likely to be causing a scan… but that’s by no means likely in all cases and it may take a little poking about in the execution plans themselves. But it’s definitely better than no guidance at all and should at least help you narrow down your target.

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.


© Copyright 2020 SQLTraining Ltd.