A few weeks ago I got quite an interesting question from a weblog reader: does a RECONFIGURE statement flush the Plan Cache? Normally my answer to this question is a simple Yes, but as we have sometimes found out together the real answer is an “it depends”. Let’s have a look at it, and why “it depends”.
It depends…
Normally you would expect that a RECONFIGURE statement would flush the Plan Cache, but this is unfortunately not the whole truth. The Plan Cache is only flushed when you change a few specific configuration items … not for every configuration change.
I have done some research on the Internet, and finally found a blog posting that showed which configuration options would trigger a flush of the Plan Cache:
- Cross DB Ownership Chaining
- Index Create Memory
- Cost Threshold for 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
So it’s just a myth that a RECONFIGURE statement will always flush the Plan Cache!
Summary
Never ever assume that SQL Server works in the way you assume 😉 I had also initially thought that a RECONFIGURE would flush the Plan Cache, but as you have seen this is not really the complete truth. Sometimes it is worth cross checking your assumptions, because they might not be valid.
Thanks for your time,
-Klaus
