Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-02-09 : 14:59:04
|
| I have installed a SQL Server diagnose tool for evaluation. It prompts and warns me that "Procedure Cache hit rate is for example 15%. Its help indicates:The Procedure Cache Hit Rate alarm is raised when the ratio between the number of times SQL Server looks for a plan in the procedure cache and the number of times it does not find a required plan in the procedure cache falls below a threshold.A low procedure cache hit rate indicates that SQL Server is finding fewer of the query execution plans it needs already in memory and therefore has to perform more compiles. These extra compilations will degrade SQL Server performance by causing extra CPU load.What can I do to increase the rate?Canada DBA |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-09 : 15:38:53
|
| Usually, increasing this value means using more stored procedures, and using bind variables (look up the parameter object for ADO examples). |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-10 : 08:58:29
|
| "What can I do to increase the rate?"If you are not using Stored Procedures then parameterising your dynamic SQL may help.Kristen |
|
|
|
|
|
|
Procedure Cache hit rate