I have a SQL Server 2005 32 bit instance that occassionally
has issues with the Procedure Cache. This instances 200+ databases and is configured with 4 processors and 12 GB of memory.
The Procedure Cache Hit Ratio drops to 25% for an extended period of time. The size of the procedure cache during this time is very small. I am able to tell this by quering the DMVs. Also, DBCC Memory status shows a small procedure cache area.
Using tools such as vmstat.exe or sqbmemory (from Red-Gate) I see that the Commited memory value drops from around 1900 MB to 600 MB and the Reserved memory value increases from 50 MB to 1350 MB. The memory is being stolen from the Committed bucket and
is being reserved by some other SQL process such as XML or CLR.
After we restart the instance, the memory pressure issues go away.
Do any of you know if there is a DMV or a tool that I
View Complete Post