Don’t believe everything you read: Reconfigure flushes the plan cache

I’ve been doing a fair bit of research and investigation into the behavior of the plan cache recently. So I was concerned when I read somewhere that the reconfigure statement causes the SQL Server plan cache to be flushed. If this is true then it is a pretty crucial piece of information for a DBA.

So I set up a quick experiment, using the same setup as in this earlier post.

After the set up I run a plan cache query:

select DB_NAME(dbid) as [db_name],usecounts,cacheobjtype,objectid,[text]
from sys.dm_exec_cached_plans p
	cross apply sys.dm_exec_sql_text(plan_handle) as s
where cacheobjtype = 'Compiled Plan'
	and [text] not like '%dm_exec_cached_plans%'

And get:

The details aren’t overly important – I just wanted to make sure that I had some database and some server scoped plans.

Now I want to ensure that I don’t have any ‘unhardened’ server configuration changes.

select * from sys.configurations where value_in_use <> value

If this returns empty then that means that running reconfigure will have no effect on server settings.

reconfigure

And, sure enough (as I had hoped) this has no effect on the plan cache.

So, try changing a configuration setting that shouldn’t have any effect on the query optimiser:

exec sp_configure 'remote admin connections', 1

(Or switch to 0 if it’s already 1). Check the plan cache – no change. Run reconfigure – still no change.

Lastly change a configuration that we definitely expect to flush the cache:

exec sp_configure 'max server memory (MB)', <some changed value>

Check the cache and there is still no change, but run reconfigure and the change is hardened and the plan cache is cleared out.

Of course the question then becomes: what configuration changes will flush the plan cache?

The settings that I found were:

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

Conclusion: Strictly speaking running the reconfigure statement does trigger a plan cache flush BUT ONLY IF it is run after one of a certain group of sp_configure changes has been made.

About these ads
This entry was posted in Plan cache, SQL Server, Uncategorized and tagged , . Bookmark the permalink.

4 Responses to Don’t believe everything you read: Reconfigure flushes the plan cache

  1. dazpoc says:

    wow – I know of at least one client that will be intersted to know this!

  2. mrdanni says:

    Great article.
    Thanks for sharing!

  3. alzdba says:

    Interesting findings.
    Thank you for sharing.

  4. Craig says:

    I suppose this makes sense, given that those settings affect query ‘behavior’ (for lack of a better term) on/by the server. Could you mitigate this by running the list of most used statements/stored procedures after the reconfigure?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s