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.