Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Cachestore flush

For each cleared cachestore in the plan cache SQL Server reports a message in the 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:

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.

5)       AUTOCLOSE = ON which causes the database to shutdown forces a clear down of cache.

6)     Buffer manager sql counters and sys.dm_os_memoryclerks  are good starting points to identify pressure

 See Also

SQL Server page life expectancy and memory bottleneck

Sys.dm_os_memory_clerks and AWE memory allocation

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...