Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Plan Cache Memory Allocations Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 4:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:08 AM
Points: 1,380, Visits: 2,703
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?
Post #1395594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse