SQL Server can automatically create and update statistics. There are 3 statistics settings that affect the entire database,
AUTO_CREATE_STATISTICSsetting to ON enables query optimizer to generate single column statistics on query predicate. This statistics is generated (with default sampling) if there is no other existing statistics with the same first column. Turning the setting OFF prevent query optimizer from automatically create statistics and may result sub-optimal query plan.
AUTO_UPDATE_STATISTICSsetting to ON specifies query optimizer to automatically update the statistics (with default sampling) if it is deemed out-of-date. The statistics becomes out-of-date when it reaches the 'change threshold' that caused by data modification through update, insert, delete or merge operations. The permanent table 'change threshold' is calculated as,
For table with number of rows < 500, requires 500 modifications since last statistics updates
For table with number of rows >= 500, requires 500 + 20% of the cardinality changes since last statistics updates
Turning this setting ON generally is good idea to automatically update the statistics as the distribution of the values may have changed since the statistics last update. However, in certain cases when the default sampling (auto update uses default sampling) is not desirable, especially when the statistics have previously been generated/updated with full scan. As data distribution varied between application, this is the time where testing and verification are required to justify if either latest statistics with default sampling or out-of-date full scan statistics produce better query performance.
Query optimizer determines if the statistics is out-of-date and updates the statistics if required before compiling and executing the query. This may result slower query response time especially if the table have large number of rows. This is where
AUTO_UPDATE_STATISTICS_ASYNCcould help. The query compile without waiting for the updates as the statistics update occurs at later times, with the trade off of using an out-of-date statistics for query compiled at the first occurrence. Query compiled after the asynchronous update can benefit from the updated statistics. This setting by default is OFF. This setting should be used with care as it may generated a sub-optimal query plan before the asynchronous statistics update.
The current values of these setting can be retrieved from sys.databases catalog views,
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases
To change these setting, it could be done with
ALTER DATABASET-SQL statement.
ALTER DATABASE TEST SET AUTO_CREATE_STATISTICS ON
Remember these setting affect the entire database. To change
AUTO_UPDATE_STATISTICSon specified statistics, index, table or indexed view, use
sp_autostats. This option is only useful towards turning a specified statistics
AUTO_UPDATE_STATISTICSOFF when database has its
AUTO_UPDATE_STATISTICSenabled (ON). Please notes that it does not recognize the specified statistics
AUTO_UPDATE_STATISTICSON when the database setting
AUTO_UPDATE_STATISTICSis turned off.
To turn the specified statistics
EXEC sp_autostats @tblname = 'YourTable', @flagc = 'OFF', @indname = 'YourStatistics'
Use the same stored procedure without @flagc to returns the result with its current value of
EXEC sp_autostats @tblname = 'YourTable', @indname = 'YourStatistics'
There is another way to disable the automatic update on a table/statistics level. By using
NORECOMPUTEoption during a manual statistics update. This is particular useful when a statistics is previously updated with
FULLSCANand needs to avoid the default sampling during automatic statistics updates.