Database Reference
In-Depth Information
Reset the automatic maintenance of the index so that it is on where it has been turned off.
EXEC sp_autostats
'HumanResources.Department',
'ON';
EXEC sp_autostats
'HumanResources.Department',
'ON',
AK_Department_Name;
Generate Statistics
To create statistics manually, use one of the following options:
•
CREATE STATISTICS
: You can use this option to create statistics on single or multiple columns
of a table or an indexed view. Unlike the
CREATE INDEX
command,
CREATE STATISTICS
uses
sampling by default.
•
sys.sp_createstats
: Use this stored procedure to create single-column statistics for all
eligible columns for all user tables in the current database. This includes all columns except
computed columns; columns with the
NTEXT
,
TEXT
,
GEOMETRY
,
GEOGRAPHY
, or
IMAGE
data type;
sparse columns; and columns that already have statistics or are the first column of an index.
This is mainly meant for backward compatibility, and I don't recommend using it.
Similarly, to update statistics manually, use one of the following options:
•
UPDATE STATISTICS
: You can use this option to update the statistics of individual or all index
keys and nonindexed columns of a table or an indexed view.
•
sys.sp_updatestats
: Use this stored procedure to update statistics of all user tables in the
current database.
You may find that allowing the automatic updating of statistics is not quite adequate for your system. Scheduling
UPDATE STATISTICS
for the database during off-hours is an acceptable way to deal with this issue.
UPDATE STATISTICS
is the preferred mechanism because it offers a greater degree of flexibility and control. It's possible, because of the
types of data inserted, that the sampling method for gathering the statistics, used because it's faster, may not gather
the appropriate data. In these cases, you can force a
FULLSCAN
so that all the data is used to update the statistics just
like what happens when the statistics are initially created. This can be a costly operation, so it's best to be selective
about which indexes receive this treatment and when it is run.
■
in general, you should always use the default settings for automatic statistics. Consider modifying these
settings only after identifying that the default settings appear to detract from performance.
Note
Statistics Maintenance Status
You can verify the current settings for the autostats feature using the following:
sys.databases
•
DATABASEPROPERTYEX
•
sp_autostats
•
