July 28, 2008 at 7:27 am
I have a SQL Server 2005 64bit server running and it currently has a really low procedure cache hit rate (usually in the 40% but goes lower at times). There are many databases on this server for applications for various vendors (so this may be part of the problem). But one of the things I did notice is the Procedure cache size is small, usually around 20-50mb. My other servers procedure caches are usually a couple of gb.
Is the low cache size causing my low hit rate and if so what can I do to increase this?
Thank you,
Derek
July 28, 2008 at 7:29 am
Yes your low cache size is causing this. How much memory is on the server and what is SQL Server configured to use? Are there any other applications (IIS, Exchange, etc.) running on this server? Are there any VM's on it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 28, 2008 at 7:39 am
Hi Jack... thank you for the reply.
There is no other applications currently running on that server. The server has 16gb of memory with most of that being used up by the Buffer Cache (currently at 13.9gb).
Thanks,
Derek
January 4, 2012 at 2:47 am
Was this ever resolved?
I am encountering the same issue with my SQL Server.
It is on a server dedicated to SQL Server with nothing else running on it. Occasionally I will see the buffer cache literally devour all memory and the procedure cache will shrink from several hundred Mb to around 2-4mb and stay there. As a result the CPU goes into overdrive and the procedure cache hit ratio plunge.
Any idea what was causing it or how to resolve it without restarting the services?
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
January 5, 2012 at 4:59 am
are you getting any Resource Semaphore wait types during this period? Could be similar to a problem I had a while ago - Blog Post Here[/url]
Do you know what processes are running at this time? Index Rebuild for example.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 5, 2012 at 5:11 am
I'll dig back through the history and check the semaphores.
As for anything running, we couldn't see anything at all, no index rebuilding, dbcc's, page spltting, etc.
The only thing that i knew was going off was that someone was copying a backup file from SQL Server to a test machine. This was cancelled which made no difference.
What we saw was the memory being consumed by the database increasing to maximum, paging went ballistic at this point, procedure cache virtually emptied.
We had always seen SQL Server max out at a specific memory value, but whatever caused this caused the memory beyond this to be consumed to such an extent that the server ran out!
We have seen intensive issues like this before, but never this critical. We had to restart the sql services to cure it - and it was definitely SQL and not an external process that was the issue.
I'll check the blog posting, thanks for this, and will continue to investigate further.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply