question

JohnSterrett avatar image
JohnSterrett 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
sql serverexecution-plan
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Grant. I appreciate your feedback here.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.