asked
What forces queries to have different execution plans?
I am currently aware of the reasons in MSDN article for having execution plans invalidated. I am wondering if there are other reasons why an execution plan can change other than the following in this link below. Also are there any new reasons in SQL 2016 or 2014?
https://technet.microsoft.com/en-US/library/ms181055(v=SQL.105).aspx
I'm not aware of any new reasons for plans to be invalidated in 2016. However, I haven't tested some of the new scenarios that might lead to plan invalidation. For example, stretch tables. If the data moves from local storage to Azure, does the execution plan change? I do know the plan reflects where the data is being retrieved from, so this is potentially a new one. Obviously the new functionality around Query Store will allow plans to be changed, but that's by request, not automatically. Those are the only ones that come to mind that might change the fundamental behaviors of the plan cache.
16 People are following this question.
