allocating memory for execution plan cache

Question

allocating memory for execution plan cache

bodhilove

SSCarpal Tunnel

Points: 4035
More actions
December 4, 2009 at 12:39 am

#218195

Hi Folks,
I was just wondering if you could allocate memory to the execution plan cache. My understanding is the server option min/max server memory is for buffer cache. My parsing of queries take a while so I am interested in configuring this better.
ta

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The max/min settings are for the buffer pool. The buffer pool consists of the data cache, the plan cache and some other much smaller caches. The allocation of the buffer pool among those caches is controlled by SQL and is not configurable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 2

thanks.. so how can I decrease the parsing time of my queries if I cannot configure plan cache memory - would decreasing my buffer cache allow more room for plan cache?

I am also getting what I would consider a low plan cache hit ratio (round 85%)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

bodhilove (12/4/2009)
would decreasing my buffer cache allow more room for plan cache?

As I said, the buffer cache (usually called the buffer pool) is divided into the data cache and the plan cache (and a few more smaller caches). The plan cache therefore is part of the buffer cache. Reduce the buffer cache and you reduce the size of both the data and the plan cache

I am also getting what I would consider a low plan cache hit ratio (round 85%)

Let me guess, lots of ad-hoc, non-parameterised SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 4

bodhilove

SSCarpal Tunnel

Points: 4035

December 6, 2009 at 4:06 pm

#1088301

Haha.. yes they are using sp_executesql quite a bit.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Then you need to look at the cause of the problem, not the symptoms of the problem. Move as much as possible from dynamic SQL to normal stored procedures. Where dynamic is needed, ensure that it's parameterised.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I will be there are other reasons cache hit ratio is poor. I also bet there are other performance-related problems here too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 7

If you have a moment, run the following and post the results please.

select objtype,

count(*) as number_of_plans,

sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,

avg(usecounts) as avg_use_count

from sys.dm_exec_cached_plans

group by objtype

It would also be very useful to know how much memory is dedicated to SQL Server, whether it is 32-bit or 64-bit, whether it is Standard or Enterprise edition, and what version and edition of Windows it is running on.

You might like to look into Forced Parameterization too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi