question

narendba avatar image
narendba asked

When the old execution plans changed in plan cache

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 column 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. Please provide any demo for checking of same.
sqlserver2012execution-planplan-cacheupdate-stats
2 comments
10 |1200 characters needed characters left characters exceeded

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

glad the answers are substantially similar. Now @narendba has two opportunities to accept an answer or provide additional clarification :)
1 Like 1 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
For review, technet ([ https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx][1]) says some of the "conditions that invalidate a plan include the following:" - Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW). - Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE). - Changes to any indexes used by the execution plan. - **Updates on statistics used by the execution plan**, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically. - Dropping an index used by the execution plan. - An explicit call to sp_recompile. - Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query). - For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly. - Executing a stored procedure using the WITH RECOMPILE option. Erin Stellato has a quick demo showing that point at which statistics are invalidated versus updated in SQL 2012. It covers how to tell from the plan whether it used the statistics in question - [ http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/][2] Kendra Little has a more recent one showing the same behavior in SQL 2014 and how the plan is *usually* invalidated by a statistics update - [ https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/][3] [1]: https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx [2]: http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/ [3]: https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/
10 |1200 characters needed characters left characters exceeded

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

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.