0

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.

2
  • It is not necessary that after stats update all plans are invalidated. I suggest why not try your self. Google it and you would find Erin Stellato and Paul Randal's article on it.
    – Shanky
    Aug 21, 2016 at 6:06
  • This question has been cross-posted to Ask.SqlServerCentral: ask.sqlservercentral.com/questions/139829/… Aug 22, 2016 at 20:02

1 Answer 1

0

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):

https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.