Database Reference
In-Depth Information
Note
if you are attempting to update statistics asynchronously, you must also have AUTO_UPDATE_STATISTICS
set to ON .
Manual Maintenance
The following are situations in which you need to interfere with or assist the automatic maintenance of statistics:
When experimenting with statistics : Just a friendly suggestion—please spare your production
servers from experiments such as the ones you are doing in this topic.
After upgrading from a previous version to SQL Server 2014 : Since the statistics maintenance of
SQL Server 2014 has been upgraded and modified, you should manually update the statistics
of the complete database immediately after the upgrade instead of waiting for SQL Server to
update it over time with the help of automatic statistics. Further, I suggest you use a FULLSCAN
for this statistics update to ensure they are as accurate as possible. The only versions that I
know this does not apply to are the ones from SQL Server 2008 to SQL Server 2008 R2. There
is some debate over whether this is necessary, but, in most situations, it's a safe and prudent
thing to do.
While executing a series of ad hoc SQL activities that you won't execute again : In such cases,
you must decide whether you want to pay the cost of automatic statistics maintenance
to get a better plan for that one case while affecting the performance of other SQL Server
activities. So, in general, you might not need to be concerned with such one-timers. This is
mainly applicable to larger databases, but you can test it in your environment if you think it
may apply.
When you come upon an issue with the automatic statistics maintenance and the only
workaround for the time being is to keep the automatic statistics maintenance feature off :
Even in these cases, you can turn the feature off for the specific database table that faces the
problem instead of disabling it for the complete database. Issues like this can be found in large
data sets where the data is updated a lot but not enough to trigger the threshold update. Also,
it can be used in cases where the sampling level of the automatic updates are not adequate for
some data distributions.
While analyzing the performance of a query, you realize that the statistics are missing for a few
of the database objects referred to by the query : This can be evaluated from the graphical and
XML execution plans, as explained earlier in the chapter.
While analyzing the effectiveness of statistics, you realize that they are inaccurate : This can be
determined when poor execution plans are being created from what should be good sets of
statistics.
SQL Server allows a user to control many of its automatic statistics maintenance features. You can enable
(or disable) the automatic statistics creation and update features by using the auto create statistics and auto update
statistics settings, respectively, and then you can get your hands dirty.
 
 
Search WWH ::




Custom Search