Hi all
I've done my research, I've done my testing.
I just want to confirm something
Given that any plan in cache with a size more that 8192 get it's memory allocation from the MultiPage Allocator which is not part of the SQL Buffer Pool. Any any plan in cache with a size more that 8192 will not affect the buffer pool.
I have a system that reports zero for the following query
SELECT
sum (cp.size_in_bytes) /1024/1024
FROM sys.dm_exec_cached_plans AS cp
where size_in_bytes < = 8192
And returns +- 1500MB for the following query
SELECT
sum (cp.size_in_bytes) /1024/1024
FROM sys.dm_exec_cached_plans AS cp
where size_in_bytes > 8192
This means that there are no plans in the area of the buffer pool allocated to the plan cache.
Is this right?