update statistics

Description

Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition, and resets the data change counters for global nonclustered indexes.

Syntax

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

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.

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.

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_partition_name: is the name of the index partition to be updated.

column_list: is a comma-separated list of columns.

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 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 statistics uses between M and M*N steps, depending on the number of frequency cells that update 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. Sampling applies to all update statistics types:

update statistics table_name

update statistics table_name (col_name)

update index statistics

update all statistics

index: specifies that statistics for all columns in an index are to be updated.

Examples

Example 1

Generates statistics for the price column of the titles table:

update statistics titles (price) using 40 values

Example 2

Updates statistics on the data partition smallsales Adaptive Server creates histograms for each leading column and densities for the composite columns of each local index of the data partition. Statistics are not updated for global indexes:

update statistics titles partition smallsales

Example 3

Updates statistics on the data partition smallsales. Adaptive Server creates histograms on column col1 and creates densities for the composite columns col1 and col2:

update statistics titles partition smallsales (col1, col2)

Usage

Adaptive Server keeps statistics about the distribution of the key values in each index, and uses these statistics in its decisions about which index (es) to use in query processing.

When you create a nonclustered index on a table that contains data, update statistics is automatically run for the new index. When you create a clustered index on a table that contains data, update statistics is automatically run for all indexes.

Running update statistics on an empty table does not affect the system tables.

The optimization of your queries depends on the accuracy of the statistics. If there is significant change in the key values in your index, you should rerun update statistics on that index or column. Use the update statistics command if a great deal of data in an indexed column has been added, changed, or removed (that is, if you suspect that the distribution of key values has changed).

You should also run update statistics on system tables with a large number of rows. If you have permission to run the command on a user table, it is no different with respect to system table. Without statistics, there is always a chance for system stored procedures to perform poorly.

update statistics skips global indexes when you run the command on a data partition.

update statistics, when used with a table name and an index name, updates statistics for the leading column of an index. If update statistics is used with just a table name, it updates statistics for the leading columns of all indexes on the table.

Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.

Specifying more than one column in a column list generates or updates a histogram for the first column, and density statistics for all prefix subsets of the list of columns.

If you use update statistics to generate statistics for a column or list of columns, update statistics must scan the table and perform a sort.

If you use update statistics on a specific partition, you update global statistics implicitly as well.

update statistics 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 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 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 Chapter 24, “Parallel Sorting,” in the Performance and Tuning Guide.

Table 1-35 shows the types of scans performed during update statistics, the types of locks acquired, and when sorts are needed.

**Table 1-35: Locking, scans, and sorts during update statistics**
update statistics specifying		Scans and sorts performed	Locking
Table name
	Allpages-locked table	Table scan, plus a leaf-level scan of each nonclustered index	Level 1; shared intent table lock, shared lock on current page
	Data-only-locked table	Table scan, plus a leaf-level scan of each nonclustered index and the clustered index, if one exists	Level 0; dirty reads
Table name and clustered index name
	Allpages-locked table	Table scan	Level 1; shared intent table lock, shared lock on current page
	Data-only-locked table	Leaf level index scan	Level 0; dirty reads
Table name and nonclustered index name
	Allpages-locked table	Leaf level index scan	Level 1; shared intent table lock, shared lock on current page
	Data-only-locked table	Leaf level index scan	Level 0; dirty reads
Table name and column name
	Allpages-locked table	Table scan; creates a worktable and sorts the worktable	Level 1; shared intent table lock, shared lock on current page
	Data-only-locked table	Table scan; creates a worktable and sorts the worktable	Level 0; dirty reads

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)

update statistics is not run on system tables in the master database during upgrade from earlier versions. Indexes exist on columns queried by most system procedures, and running update statistics on these tables is not required for normal usage. However, running update statistics is allowed on all system tables in all databases, except those that are not normal tables. These tables, which are built from internal structures when queried, include syscurconfigs, sysengines, sysgams, syslisteners, syslocks, syslogs, syslogshold, sysmonitors, sysprocesses, syssecmechs, systestlog and systransactions.

You do not need to run update statistics on Replication Server RSSD tables. Running updates statistics on these tables can result in Replication Server errors if you run it while Replication Server attempts to access the RSSD tables. RSSD tables and their format are specific to Replication Server processing.

update statistics and sampling

Sampling is not performed for leading columns of indexes. If you specify an index in update statistics, such as in the following:

update statistics table_name [index_name] with sampling = N percent

This command creates and updates statistics on the leading column of all indexes on the specified table, or the leading column of a specified index.

When you use the sampling = N percent option with the using steps value , you must specify the sampling = N percent option last:

update statistics titles (type) 
    using 40 value
    with sampling = 10 percent

If you do not, you get an error message:

update statistics titles (type) 
    with sampling = 10 percent 
    using 40 value

Msg 156, Level 15, State 2:
Line 1:
Incorrect syntax near the keyword 'using'.

create index and stored procedures

Adaptive Server automatically recompiles stored procedures after executing update statistics statements. Although ad hoc queries that you start before executing update statistics still continue to work, they do not take advantage of the new statistics.In Adaptive Server versions 12.5 and earlier, update statistics was ignored by cached stored procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

update 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.