3

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?

2
  • 1
    Do you mean the statistics have never been updated or the table has never been updated?
    – Tom V
    Aug 7 '18 at 14:15
  • Statistics never been updated
    – Mazhar
    Aug 7 '18 at 18:20
12

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 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'

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.