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%'
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.
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.
wow – I know of at least one client that will be intersted to know this!
Thanks for sharing!
Thank you for sharing.
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?
The 2012 version of my Plan Recompilation whitepaper made this clear: http://msdn.microsoft.com/en-us/library/dn148262.aspx (The 2008 version wasn’t clear enough that it was only certain options to that command).
Pingback: Counting the number of calls of a stored procedure | XL-UAT
Pingback: Does a RECONFIGURE statement flush the Plan Cache? |