http://www.sqlservercentral.com/blogs/steve_jones/2012/01/19/reconfigure-can-flush-the-procedure-cache/

Printed 2014/04/16 11:46AM

RECONFIGURE can flush the procedure cache

2012/01/19

I ran across this KB article the other day, which lists a few ways in which performance is affected by various maintenance or administrative type operations.

In KB article 917818, it notes that some operations cause a performance issue. Some of the operations make sense (offline/online, restores, etc), but there were a few that surprised me. For example, did you know that Autoclose flushes the cache? Might not be a big deal, but it also might mean that your apps based on Express might end up running slowly each time the user accesses the databases.

There are also a number of items which are implemented by a RECONFIGURE that will flush the cache. These are listed in the KB and are:

Also, changing a filegroup to read-only will flush the cache.

This is by design, and I wouldn’t expect it to change anytime soon. Since these options can affect query plans, it might make sense to flush the cache, but if you don’t agree, file a CONNECT item and stump for votes.


Filed under: Blog Tagged: Performance, sql server, syndicated
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.