Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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.


Comments

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

Loading comments...