update index statistics

Description

Updates the statistics for all columns in an index.

Syntax

update index statistics
	table_name [[partition data_partition_name] |
	[ [index_name [partition index_partition_name]]]
	[using step values]
	[with consumers = consumers] [, sampling=N [percent]]
		[, no_hashing | partial_hashing | hashing]
	 	[, max_resource_granularity = N percent]]
	 	[, histogram_tuning_factor = int]
		[, print_progress = int]

Parameters

table_name: 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.

data_partition_name: 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.

index_name: 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.

index_partition_name: 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 0 and M*N steps, depending on the number of frequency cells that update index statistics isolates and whether any range cells exist.

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.

The value for the max parallel degree configuration parameter must be be greater than the value for with consumers. For example, if with consumers is set to 2, then max parallel degree must be at least 3.

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.

Hashing does not currently support sampling.

[no_hashing | partial_hashing | hashing]: indicates the level of hash-based statistics update index statistics gathers. One of:

no_hashing – (the default) update index statistics uses the algorithm from Adaptive Server versions earlier than 15.7, gathering sort-based statistics.

partial_hashing – update index statistics uses the algorithm for columns with fewer than 65536 unique values. If update index statistics encounters unique column counts that are greater than or equal to the 65536 threshold, it uses an extra scan with a sort.

hashing – update index statistics uses low-domain and high-domain hashing to create the histograms.

max_resource_granularity = N percent: limits the amount of tempdb buffer cache used with update index statistics and hashing.

with histogram_tuning_factor = integer: determines update index statistics’s distribution granularity.

print_progress = int: Determines if update index statistics displays progress messages.

0 – (the default) command does not display any progress messages

1 – command displays progress messages

Examples

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

Usage

update index statistics, when used with a table name and an index name, updates statistics for all columns in the specified index. If update index statistics is used with just a table name, it updates statistics for all columns in all indexes of the table.
If you run update index statistics against large tables, the command fails with error number 1105 if tempdb is not large enough to process the command.
Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.
Histogram statistics are created for each column of indexes whose statistics is being updated.
Density statistics are created for all the prefix subsets of the columns of index (es) whose statistics are being updated.
If you use update index statistics on a specific partition, you update global statistics implicitly as well.
The partition statistics serve as input to global statistics creation and enable per-partition DDL operations. Global statistics are used by the optimizer.
update index statistics also regenerates and updates the table statistics stored in systabstats for each data and index partition of the table the command updates. If you run the update index statistics command for a specific data partition, the table statistitics are generated and updated only for that data partition and for any local index partitions. Global indexes are skipped. If you run the update index statistics for a specific index partition, only the table statistics for that index partition are updated.
The with consumers clause is designed for use on partitioned tables on RAID devices, which appear to Adaptive Server as a single I/O device, but which are capable of producing the high throughput required for parallel sorting. See “Parallel Sorting” in the Performance and Tuning Guide.
The update index statistics command generates a series of update statistics operations that use the same locking, scanning, and sorting as the equivalent index-level and column-level command. For example, if the salesdetail table has a nonclustered index named sales_det_ix on salesdetail (stor_id, ord_num, title_id), the update index statistics salesdetail command performs these update statistics operations:
```
update statistics salesdetail sales_det_ix 
update statistics salesdetail (ord_num)
update statistics salesdetail (title_id)
```

Using hash-based statistics

The partial_hashing parameter uses hashing on columns only when it produces histograms that are as accurate and of equivalent quality as those produced by sorting (low-domain cases), otherwise, the partial_hashing parameter uses sorting for high-domain cases.
Hashing may produce less accurate histograms than sorting in some cases (high-domain cases).
Although hash-based statistics do not require the tempdb disk space or procedure cache memory used by sorting, it may use a significant amount of tempdb buffer cache memory.
Large sorts used for the no_hashing parameter may scavenge statements in the statement cache and stored procedures to release procedure cache memory to support the sort.
max_resource_granularity limits the amount of tempdb buffer cache memory used for hashing or partial_hashing. It does not affect the amount of memory used by the no_hashing parameter or sorting.
If you include the partial_hashing parameter, and a previous histogram on a column exists that indicates a high-domain column, then Adaptive Server assumes that sort-based statistics are required on this column. If no previous histogram exists on a column, then Adaptive Server assumes this column is low-domain until the high-domain limit is reached.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be the table owner or a user with update statistics permission on the table to run update index statistics.