July 23, 2009 at 1:06 pm
On Saturday of last week, got a call sql server 2005 with 48 gig box running very slow, only a few users on the weekend were there. Looked at it, only about 15% cpu could not see anything glaring. Come Monday box with 50 users at a slow crawl. OLTP buried, CPU at about 18%, Buffer Cache at 99% like usual. Looked at proc cache and it had sunk to 40%. No blocking, lock issues, restarted SQL Server problem went away. Looked at my IDERA Dm and noticed on Friday that procedure cache size had gone from 400 MB to 0, and procedure cache was dropped from 90 to 40%. Now I need to play detective with the DMV's Anyone seen this before (this also happened about 45 days ago), or any idea where to look to see what killed my procedure cache?
Thanks
July 23, 2009 at 1:43 pm
Several possibilities
Someone ran DBCC FREEPROCCACHE
A database was closed, taken offline or restored
An sp_configure was run. Some options flush the proc cache
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply