SQL Cachestore flush

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

Read More

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)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on SQL Cachestore flush


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer