Statistics Maintenance

SQL Server allows a user to manually override the maintenance of statistics in an individual database. The four main configurations controlling automatic statistics maintenance behavior of SQL Server are as follows:

·         New statistics on columns with no index (auto create statistics)

o    The auto create statistics feature automatically creates statistics on nonindexed columns when referred to in the WHERE clause of a query.

·         Updating existing statistics (auto update statistics)

·         The degree of sampling used to generate statistics

·         Asynchronous updating of existing statistics (auto update statistics async)

o    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 query, as normally happens. Instead, the query finishes execution using the older set of statistics. Once the query completes, the statistics are updated.

Manual Maintenance

The following are situations in which you need to interfere with or assist the automatic maintenance of statistics:

·         After upgrading from a previous version to SQL Server 2012: Since the statistics maintenance of SQL Server 2012 has been upgraded, 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. The only versions that I know this does not apply to is going from SQL Server 2008 to SQL Server 2008R2.

·         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 in that one case and affect the performance of other SQL Server activities. So, in general, you don’t 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.

·         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 indexes.

ALTER DATABASE AdventureWorks2008R2 SET AUTO_CREATE_STATISTICS OFF

ALTER DATABASE AdventureWorks2008R2 SET AUTO_UPDATE_STATISTICS OFF

ALTER DATABASE AdventureWorks2008R2 SET AUTO_UPDATE_STATISTICS_ASYNC ON