October 7, 2009 at 12:44 pm
I can not find any information on manually configuring Procedure Cache on SQL Server 2005 on a 64bit system. It appears to have been an option on prior versions. The reason I ask is that we're experiencing major performance problems with CPU usage frequently above 80% (99-100% for short time this morning) and Procedure Cache Hit Rate frequently below 75% (61% today). 2-3 months ago the same server had CPU usually around 20% & Cache at 95%. The major change has been the splitting of multi-client DBs onto their own DB with number of DBs on that server going from 100 to now over 500. They use the same stored procedures, but each DB has their own copy (about 200 SPs per DB). The System has 63 gig of ram (55 for SQL Server), but only about 7 gig is going for procedure cache. Any suggestions greatly appreciated.
October 7, 2009 at 1:24 pm
MS has indicated that support for procedure cache configuration will not be provided despite this being requested by many SQL Server MVP. See "Amount of RAM for procedure cache should be configurable" by Adam_Machanic at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188
However, your problem may be caused by the procedure cache being flushed when certain operations are performed, as described at http://support.microsoft.com/kb/917828. The work-around is to not perform these operations or upgrade to 2008.
Good Luck.
SQL = Scarcely Qualifies as a Language
October 8, 2009 at 7:09 am
Have you tried watching trace events to see where or when you're getting either recompiles or cache miss events? If you can nail down when it occurs, you might be able to identify the cause.
"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
October 9, 2009 at 8:35 am
You cannot explicitly configure the procedure cache. If you are on SP2+ then you have lower ratios of procedure cache than prior to that version, which actually could be beneficial for you (although many shops had a LOT of problems with this). Not sure what recourse you have here, other than to split the databases back out. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply