Printed 2017/07/22 02:57PM

RECONFIGURE can flush the procedure cache


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-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.