March 9, 2009 at 9:55 am
I have a production sql server 2005 active/passive cluster that runs and OLTP app. Forever cache hit ratio at 99, proc cache at 89-90. THis morning all of a sudden user complaints of slowness and proc cache has dropped to 50%. CPU is 28-33 system queue length still around 0. Any ideas where to look?
March 9, 2009 at 10:04 am
was there a failover? This will clear proc cache as SQL will re-start.
thanks
SQL_EXPAT
March 9, 2009 at 10:10 am
Check the size of Procedure Cache, is Procedure Cache growing at the same time hit ratio goes down?
Remember that a growing Procedure Cache would take space away from Data Cache and consequently negatively affect query performance.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply