question

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/