Updating index statistics

The UPDATE STATISTICS command uses the following syntax:

UPDATE ([ TABLE | INDEX |[ ALL ] COLUMN ] STATISTICS [ AND ])...[ FOR table_name[FOR PARTITIONS partition_name, [partition_nam_1, … ]]

The following example demonstrates the use of the UPDATE STATISTICS statement for a single table:

UPDATE INDEX STATISTICS FOR Employee;

The following example updates statistics for indexes and columns for a single employee:

UPDATE TABLE STATISTICS AND INDEX STATISTICS
AND COLUMN STATISTICS FOR Employee ;

The following example updates index statistics of partitions USA_Customer, EUROPE_Customer and ASIA_Customer for table Customer by using FOR PARTITIONS which updates only the local index statistics and not the global index statistics:

To create the new index statistics for all tables in a database, simply use the statement shown in the following example.

Table 24. UPDATE INDEX STATISTICS statement
UPDATE INDEX STATISTICS ;

Notes

To create the new index statistics, SQL makes one pass over each index, reading every index entry and counting unique values. This is usually a CPU-intensive operation. When a table has many indexes, this operation can take quite a bit more time than the default UPDATE STATISTICS.

UPDATE STATISTICS does not lock user data. It only locks the output statistics rows (and also acquires a shared lock on the schema). This means that user-level transactions can freely run concurrently with UPDATE STATISTICS.

To get the best SQL query performance, or if a SQL performance problem occurs, be sure that the database has a full set of SQL statistics. To get a full set of SQL statistics, execute this SQL statement:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS;

At a slightly longer execution time, you can get even better SQL statistics by executing:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS;