Conor vs. Max Server Memory

Question (paraphrased):  “Conor, does changing the max server memory settings always empty the plan cache on SQL 2005 and 2008?”

Answer: yes, this is what happens and what is supposed to happen.  One of the inputs to the optimizer is the max server memory, and it is used in the costing functions to determine which plan gets chosen during optimization.  Changing this setting could imply that other plans are now better.  As such, the plan cache is emptied and plans are compiled on their next submission.

As an example, imagine that we had a buffer pool of 10MB (something absurdly small but fine for an example).  If we have a table that requires 11MB of buffer pool pages to scan, then a forward scan of the heap/clustered index would not fit in the buffer pool.  If you only scan something once, then it may not be a big deal.  Now let’s do that on the inner side of a nested loops join or if many clients are running the scan in succession.  If you are scanning the same pages over and over again, then you will always be kicking pages out of the buffer pool right before you need them again.  (Note:  the actual algorithms used in buffer pool eviction are more complicated than this – I am only trying to explain the concept of why plan selection cares about this issue). 

The optimizer makes some costing assumptions when building plans.  First, it assumes that the buffer pool is initially empty for a given query.  This is not true usually, but it avoids negative cases to make this assumption and it is rare that there are significant bad cases by assuming each initial IO for a page in a query is physical.  Physical IO is more expensive than logical IO (one is a disk read, the other is in memory).  When queries start doing “rewinds” or “rebinds” to read pages multiple times, then the sizing of the buffer pool is modeled.  If you make the size bigger or smaller, it could make different plan strategies relatively faster than the plan you have currently.

Technically, there are cases where we could try to understand that the plans are the same and should be.  However, the ones where this possible are also the cheapest to recompile.  In practice, it’s just not worth the effort today to do this.

If you have concerns about the plan selection before/after, you can always capture the XML showplan for any given query and use that with a USE PLAN hint or plan guide in the future to get back to the query plan in question.

Happy Querying!

Conor