Plan Cache - Query plan size

Question

Plan Cache - Query plan size

SQLSACT

SSC-Insane

Points: 21838
More actions
March 25, 2014 at 2:24 am

#286911

Hi all
It is my understanding that the area of memory dedicated for SQL Plans is part of the buffer pool.
I've read that any plan > 8KB is size will be allocated from outside of the buffer pool where extended procedures, linked servers etc are allocated from.
I find this a bit hard to swallow
I've run this on a busy server and received the following results
SELECT [text] ,
cacheobjtype ,
objtype ,
cp.size_in_bytes ,
cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
WHERE cp.size_in_bytes < = 8192
sp_cursorcloseExtended Proc
xp_enumerrorlogsExtended Proc
xp_cmdshellExtended Proc
xp_regreadExtended Proc
xp_readerrorlogExtended Proc
xp_instance_regreadExtended Proc
xp_fixeddrivesExtended Proc
xp_msverExtended Proc
sp_executesqlExtended Proc
Does this mean that every single other plan has been allocated from outside of the buffer pool?
Why is this 8KB limit so small.
I've run a simple select col1 from table1 where col1 = some_value and the size of that plan is 40960 bytes.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 1

Try running the query without the WHERE clause. You'll note that there are tons of plans right there in the cache that are larger. If there is a literal within the query plan that is longer than 8kb, the plan is not cached. But plans of bigger than 8kb are cached just fine. Assuming you don't have optimize for ad hoc on, the minimum size for plans is 24kb, which would mean that nothing was ever put into the buffer pool. But it is. Not sure where you got that limit from, but I wouldn't sweat it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Thanks

I've run it without the WHERE clause and there are tons of plans.

I've seen that limit posted on a few articles and blogs when looking into MemToLeave troubleshooting and its been said that any plan > 8KB won't be allocated from the plan cache section of the buffer pool.

If there is a literal within the query plan that is longer than 8kb, the plan is not cached

Can you explain this please? What is a literal?

Will this then be allocated from outside of the buffer pool?

Thanks

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 3

A literal is a string or number used within the query, not a variable or a parameter, but a literal value.

Then, the plan isn't cached. Period. It's not inside or outside memory, the plan will not be stored. It's like a query with a recompile hint, no storage in the cache at all.

I don't remember the exact size for plans to spill out of buffer, but I think it's 1-2mb in size, but I might even be remembering that incorrectly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

Thanks

Could a literal possibly be:

select column from table where column = 'literal'.

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 5

SQLSACT (3/25/2014)
Thanks
Could a literal possibly be:
select column from table where column = 'literal'.

Yep. That's it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning