Does a RECONFIGURE statement flush the Plan Cache?

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top