patternsqlModerate
Updating statistics, what about existing execution plans
Viewed 0 times
whatupdatingstatisticsplansaboutexistingexecution
Problem
If you update the statistics on tables that have never been updated (don't ask why they never have), does SQL Server automatically refresh the execution plans based on the outdated statistics? If not, what is best approach to do so?
Solution
If you update statistics on a table that didn't have any modifications since the previous statistics update, your execution plans will not be invalidated.
If you update statistics AND data has been changed between this and previous statistics update then it will be invalidated if the database has the
See this post by Kendra Little: Does Updating Statistics Cause a Recompile if No Data Has Changed?
Finding: Statistics Update Alone Didn’t Cause a Recompile
SQL Server was smart enough to check if the data had changed. Updating
statistics alone doesn’t always invalidate execution plans.
And this post by Kimberly Tripp referencing a no longer available connect item:
It’s NOT a bug, it’s BY DESIGN. And, it actually makes sense.
If the plan should NOT be invalidated (directly due to statistics
because the data has NOT changed) then it won’t. But… If the plan
should be evaluated (statistics have been updated AND data changed)
then it will.
Also see this post by Erin Stellato: Statistics and Recompilations which references this MSDN documentation page
When the AUTO_UPDATE_STATISTICS database option is set to ON, queries
are recompiled when they target tables or indexed views whose
statistics have been updated or whose cardinalities have changed
significantly since the last execution. This behavior applies to
standard user-defined tables, temporary tables, and the inserted and
deleted tables created by DML triggers. If query performance is
affected by excessive recompilations, consider changing this setting
to OFF. When the AUTO_UPDATE_STATISTICS database option is set to OFF,
no recompilations occur based on statistics or cardinality changes,
with the exception of the inserted and deleted tables that are created
by DML INSTEAD OF triggers. Because these tables are created in
tempdb, the recompilation of queries that access them depends on the
setting of AUTO_UPDATE_STATISTICS in tempdb. Note that in SQL Server
2000, queries continue to recompile based on cardinality changes to
the DML trigger inserted and deleted tables, even when this setting is
OFF.
So if you want to be absolutely sure your plans for those tables are recompiled you will have to recompile them your self using
If you update statistics AND data has been changed between this and previous statistics update then it will be invalidated if the database has the
AUTO_UPDATE_STATISTICS database option set to ON. Note that affected plans are not immediately recompiled; each plan will recompile when it is next retrieved from cache for execution.See this post by Kendra Little: Does Updating Statistics Cause a Recompile if No Data Has Changed?
Finding: Statistics Update Alone Didn’t Cause a Recompile
SQL Server was smart enough to check if the data had changed. Updating
statistics alone doesn’t always invalidate execution plans.
And this post by Kimberly Tripp referencing a no longer available connect item:
It’s NOT a bug, it’s BY DESIGN. And, it actually makes sense.
If the plan should NOT be invalidated (directly due to statistics
because the data has NOT changed) then it won’t. But… If the plan
should be evaluated (statistics have been updated AND data changed)
then it will.
Also see this post by Erin Stellato: Statistics and Recompilations which references this MSDN documentation page
When the AUTO_UPDATE_STATISTICS database option is set to ON, queries
are recompiled when they target tables or indexed views whose
statistics have been updated or whose cardinalities have changed
significantly since the last execution. This behavior applies to
standard user-defined tables, temporary tables, and the inserted and
deleted tables created by DML triggers. If query performance is
affected by excessive recompilations, consider changing this setting
to OFF. When the AUTO_UPDATE_STATISTICS database option is set to OFF,
no recompilations occur based on statistics or cardinality changes,
with the exception of the inserted and deleted tables that are created
by DML INSTEAD OF triggers. Because these tables are created in
tempdb, the recompilation of queries that access them depends on the
setting of AUTO_UPDATE_STATISTICS in tempdb. Note that in SQL Server
2000, queries continue to recompile based on cardinality changes to
the DML trigger inserted and deleted tables, even when this setting is
OFF.
So if you want to be absolutely sure your plans for those tables are recompiled you will have to recompile them your self using
sp_recompile 'tablename'Code Snippets
sp_recompile 'tablename'Context
StackExchange Database Administrators Q#214286, answer score: 12
Revisions (0)
No revisions yet.