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:
checkpoint
dbcc dropcleanbuffers
go
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)
cross 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.