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:

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.