update index statistics


Updates the statistics for all columns in an index.


update index statistics
	table_name [[partition data_partition_name] |
	[index_name [partition index_partition_name]]]
	[using step values]
	[with consumers = consumers] [, sampling=N percent]



when used with update statistics, table_name is the name of the table with which the index is associated. table_name is required, since Transact-SQL does not require index names to be unique in a database.


is the name of the partition to be updated. Statistics for each local index partition on the data partition is updated. Does not update statistics for global indexes.


is the name of the index to be updated. If an index name is not specified, the distribution statistics for all the indexes in the specified table are updated.


is the name of the index partition to be updated.

using step values

specifies the number of histogram steps. The default value is 20, for columns where no statistics exist. If you need to change the default for this, use sp_configure to modify the number of histogram steps parameter. If statistics for a column already exist in sysstatistics, the default value is the current number of steps.

The steps are applied to each partition of a partitioned table—for example, update index statistics uses the default value of 20 steps for each data and index partition involved in the scan for updating statistics. If global statistics are generated through an index scan of a global index, then 20 steps are applied by default. If partition statistics are generated, either through a data scan or local index scan, then 20 steps are applied by default for each partition.

If the histogram steps specified through using step values is M, and the histogram tuning factor parameter is N, then update index statistics uses between M and M*N steps, depending on the number of frequency cells that update index statistics isolates.

with consumers = consumers

specifies the number of consumer processes to be used for a sort when column_list is provided and parallel query processing is enabled. The consumers option specifies the degree of parallelism applied to a sort performed for statistics update on a single data partition. For example, if update statistics with a column list is applied to a table with three data partitions, data from each of the three partitions is sorted separately and the consumers option is applied during each of the sort. The three sorts themselves are not performed in parallel.

with sampling = N percent

specifies the percentage of the column to be randomly sampled in order to gather statistics. The value for N is any number between 1 and 100.


Example 1

Generates statistics for all columns in all indexes of the authors table:

update index statistics authors

Example 2

Generates statistics for all columns in the au_names_ix index of the authors table:

update index statistics authors au_names_ix

Example 3

Generates statistics on all inner columns of the au_names_ix index using a sampling rate of 20 percent.

update index statistics authors au_names_ix 
    with sampling = 20 percent

The statistics for the leading column of au_names_ix is gathered using a full scan of the index pages; sampling is not applied on this column.

Example 4

Generates statistics for all the columns of an index partition:

update index statistics publishers publish1_idx 
    partition p1



ANSI SQL – Compliance level: Transact-SQL extension.


update index statistics permission defaults to the table owner and is not transferable. The command can also be executed by the database owner, who can impersonate the table owner by running the setuser command.

See also

Commands delete statistics, update all statistics, update statistics, update table statistics

Documentation Performance and Tuning Guide