Database Reference
In-Depth Information
Manage Statistics Settings
You can control the auto create statistics setting at a database level. To disable this setting, use the
ALTER DATABASE
command:
ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF;
You can control the auto update statistics setting at different levels of a database, including all indexes and
statistics on a table, or at the individual index or statistics level. To disable auto update statistics at the database level,
use the
ALTER DATABASE
command.
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS OFF;
Disabling this setting at the database level overrides individual settings at lower levels. Auto update statistics
asynchronously requires that the auto update statistics be on first. Then you can enable the asynchronous update.
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON;
To configure auto update statistics for all indexes and statistics on a table in the current database, use the
sp_autostats
system stored procedure.
USE AdventureWorks2012;
EXEC sp_autostats
'HumanResources.Department',
'OFF';
You can also use the same stored procedure to configure this setting for individual indexes or statistics. To disable
this setting for the
AK_Department_Name
index on
AdventureWorks2012
.
HumanResources.Department
, execute the
following statements:
EXEC sp_autostats
'HumanResources.Department',
'OFF',
AK_Department_Name;
You can also use the
UPDATE STATISTICS
command's
WITH NORECOMPUTE
option to disable this setting for all or
individual indexes and statistics on a table in the current database. The
sp_createstats
stored procedure also has
the
NORECOMPUTE
option. The
NORECOMPUTE
option will not disable automatic update of statistics for the database, but it
will for a given set of statistics.
Avoid disabling the automatic statistics features, unless you have confirmed through testing that this brings
a performance benefit. If the automatic statistics features are disabled, then you are responsible for manually
identifying and creating missing statistics on the columns that are not indexed and then keeping the existing statistics
up-to-date. In general, you're only going to want to disable the automatic statistics features for very large tables.
If you want to check the status of whether a table has its automatic statistics turned off, you can use this:
EXEC sp_autostats 'HumanResources.Department';
