Sql-server – When the old execution plans changed in plan cache

sql server 2014

Can any one share demo for when the execution plans changing in plan cache. For example as I know if the table has a modifications of 20% + 500 rows then the table columns and index statistics are updated automatically. So my question is once they updated whether the old execution plan changed to new execution plans with the new update stats on the table or not.

Please provide any demo for checking of same.

Best Answer

The are a lot of caveats about execution plan generation and reuse, so the old saying 'it depends' definitely comes into play. I think you will find some very valuable information from these links (they include examples of various tests):