July 19, 2010 at 2:17 pm
We have a sql server with 2.0g of RAM for collecting small amount of data daily. Monitoring the Procedure Cache Hit Ratio, the counter is around 82%~89%. However according to the performance monitors, average memory utilization is below 50%. My question is why the Proc Chace Hit Ratio is around 82%~89% when not all the Memory being used? Could someone explain this?
thanks
July 19, 2010 at 5:59 pm
tran008 (7/19/2010)
We have a sql server with 2.0g of RAM for collecting small amount of data daily. Monitoring the Procedure Cache Hit Ratio, the counter is around 82%~89%. However according to the performance monitors, average memory utilization is below 50%. My question is why the Proc Chace Hit Ratio is around 82%~89% when not all the Memory being used? Could someone explain this?thanks
Stored procedure recompiles, adhoc queries, executing with RECOMPILE etc... Look at the whitepapers below for more details.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Plan Caching in SQL Server 2008
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 19, 2010 at 8:05 pm
It is due to procedure cache bloating as sankar mentioned.
Thank You,
Best Regards,
SQLBuddy
July 20, 2010 at 6:00 am
thanks, since this is a third party software, changing the code is a bit of a problem. Will addition 2.0g of RAM, since it is cheap to get, help at all?
July 20, 2010 at 6:34 am
I'd say no. Your issue is that the procs are not in the cache, but it's not necessarily a memory problem. It's much more likely to be a coding issue. Just a guess, the app uses ad hoc sql or an ORM tool or something along those lines, right? You're lucky you're getting 80% cache hits.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply