20 January,2012 by Jack Vamvas
For each cleared cachestore in the plan cache SQL Server reports a message in the SQL Server error logs , such as :
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Notes on SQL cachestore flush:
1) When the plan cache clears , a recompilation is forced , this can cause a temporary performance decrease
2) Using DBCC FREEPROCCACHE clears the cache .
3) Restoring a database and attaching\detaching a database causes a plan cache clear for the SQL Server instance.
4) Using DBCC FREESYSTEMCACHE clears the cache. Good practise for Query baseline testing
5) AUTOCLOSE = ON which causes the database to shutdown forces a clear down of cache. Read more on SQL auto close - Keep it turned off
6) Buffer manager sql counters and sys.dm_os_memoryclerks are good starting points to identify pressure
SQL Server page life expectancy and memory bottleneck
Sys.dm_os_memory_clerks and AWE memory allocation
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |