procedure cache hit ratio too high?

Question

procedure cache hit ratio too high?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
January 21, 2012 at 12:09 am

#250003

Hi,
Test Server always using procedure cache hit ratio 90% to 95%,
Total server Memory 3 GB
Max memory setting – 2048
Buffer cache Hit – 100 %
Page Life Expectancy – 64,973 seconds
DBCC PROCCACHE
num proc buffs - 3973
num proc buffs used - 65
num proc buffs active - 65
proc cache size - 559
proc cache used - 13
proc cache active – 13
how to resolve this issues, how to increase procedure cache size?
thanks
ananda

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

Why is a high plan cache hit ratio a problem? That's the % of time that SQL finds a plan in cache when it looks for one.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

GilaMonster (1/21/2012)
Why is a high plan cache hit ratio a problem? That's the % of time that SQL finds a plan in cache when it looks for one.

Monitoring sampling frequency every 1 minute, procedure cache hit ratio always 90 to 95 %..

I tested DBCC freeproccache, then below 60 % after that increase 90 to 95 %.. Is it really increase procedure cache size?

thanks

ananda

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

Don't understand your question.

As for the hit ratio, it's the % of times that SQL can find a cached plan. If it's lower, like after a free proc cache, if means SQL is compiling more plans as opposed to been able to reuse cached plans.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi,

Procedure cache Hit ratio..actually using 95%, I want to reduce the execution % for the stored procedure.

The best way for reuse the stored procedure an existing execution plan. Could give me script for how to identified those procedure are using existing plan or new plan? how to set the all the stored procedure for using existing plan cache?

Thanks

ananda

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

ananda.murugesan (1/23/2012)
Hi,
Procedure cache Hit ratio..actually using 95%, I want to reduce the execution % for the stored procedure.

The execution %? Exactly what do you mean by that?

how to set the all the stored procedure for using existing plan cache?

I'm not sure what you mean here either.

I suspect there's a bit of a misunderstanding here, I'm not clear exactly what you want. Please can you explain clearly.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Hi,

I have memory issues one of the server. I had checked as below counters all are running good values. Except procedure cache cache Hit ratio..

1. Page life expectancey- 3,745 sec

2. Buffer cache Hit Ratio - 100 %

3. Plan cache size 900 MB

4. Buffer cache size 1,599 MB

5. Memory paging rate - 8 sec

6. OS Memory utilize - 72%

7. procedure cache Hit ratio - 90% to 95%

Procedure Cache Hit Ratio

Percentage of time when SQL Server looks for an execution plan in the procedure cache and finds it for this instance. SQL Server finding precentage value 95%, how to reduce the precentage?

Solution

If this is low, try to write more reusable code and/or consider increasing the size of the procedure cache.

how to set reusable code? for existing stored procedure..

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

ananda.murugesan (1/23/2012)
...7. procedure cache Hit ratio - 90% to 95%
Procedure Cache Hit Ratio
Percentage of time when SQL Server looks for an execution plan in the procedure cache and finds it for this instance. SQL Server finding precentage value 95%, how to reduce the precentage?...

Reduce the percentage? Why? High = good, low = bad. Read Gail's posts again.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

ananda.murugesan (1/23/2012)
Percentage of time when SQL Server looks for an execution plan in the procedure cache and finds it for this instance. SQL Server finding precentage value 95%, how to reduce the precentage?

Why do you want to reduce it? A high plan cache hit ratio is good, it's an indication that SQL is effectively using the plan cache and not wasting time compiling plans that it doesn't need to.

Also, why do you think you have a memory issue?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

I am really felt disgrace, not properly understand about that procedure cache hit ratio. It should be high percentage good and there are no issues, Thanks Gail and ChrisM@home for clear it once. This cache hit ratio not increase more than 90 percentages but application running fine and no calls from user side for performance issue. If I want increase more than 100 percentages, how to do that? For using reusable stored procedure or any other ways.

Thanks

ananda

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

If the app is fine and the users aren't complaining, then don't waste your time looking for problems that don't exist.

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

king1 Grasshopper Points: 15 More actions · Answer 11

I think the problem is the tool giviing the info somehow it is not at the right threshold it may have come that way 90 to 95 is good change the threshold to maybe 85% so if it goes down for some reason you can repond to that.