Database Reference
In-Depth Information
Figure 12-29.
Session output with AUTO_UPDATE_STATISTICS ON
Once the statistics are updated, the change-tracking mechanisms for the corresponding tables are set to 0.
This way, SQL Server keeps track of the number of changes to the tables and manages the frequency of automatic
updates of statistics.
For large tables, you may find that you need more frequent updates on the statistics. As was mentioned
previously, you can use trace flag 2371 to modify the default behaviour of the automatic update of statistics.
With the trace flag enabled, a sliding scale is used to update statistics more frequently as the amount of data within
the system increases.
Auto Update Statistics Asynchronously
If auto update statistics asynchronously is set to on, the basic behavior of statistics in SQL Server isn't changed
radically. When a set of statistics is marked as out-of-date and a query is then run against those statistics, the statistics
update does not interrupt the execution of the query, like normally happens. Instead, the query finishes execution
using the older set of statistics. Once the query completes, the statistics are updated. The reason this may be attractive
is that when statistics are updated, query plans in the procedure cache are removed, and the query being run must be
recompiled. So, rather than make a query wait for both the update of the statistics and a recompile of the procedure,
the query completes its run. The next time the same query is called, it will have updated statistics waiting for it, and it
will have to recompile only.
Although this functionality does make recompiles somewhat faster, it can also cause queries that could benefit
from updated statistics and a new execution plan to work with the old execution plan. Careful testing is required
before turning this functionality on to ensure it doesn't cause more harm than good.
