SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Filed under: Blog Tagged: Performance, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...