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:

  • cross db ownership chaining
  • index create memory (KB)
  • remote query timeout (s)
  • user options
  • max text repl size (B)
  • cost threshold for parallelism
  • max degree of parallelism
  • min memory per query (KB)
  • query wait (s)
  • min server memory (MB)
  • max server memory (MB)
  • query governor cost limit

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.