Transact-SQL User's Guide

Home

Determining what indexes
exist on a table

Chapter 12 Defining Defaults and Rules
for Data

Transact-SQL User's Guide

Chapter 11 Creating Indexes on Tables

Updating statistics about indexes

The update statistics command helps Adaptive Server make the best decisions about which indexes to use when it processes a query, by keeping it up to date about the distribution of the key values in the indexes. Use update statistics when a large amount of data in an indexed column has been added, changed, or deleted.

When Component Integration Services is enabled, update statistics can generate accurate distribution statistics for remote tables. For more information, see the Component Integration Services User's Guide.

Permission to issue the update statistics command defaults to the table owner and is not transferable. Its syntax is:

update statistics table_name [index_name]

If you do not specify an index name, the command updates the distribution statistics for all the indexes in the specified table. Giving an index name updates statistics for that index only.

You can find the names of indexes by using sp_helpindex. Here is how to list the indexes for the authors table:

sp_helpindex authors

index_name index_description  index_keys  index_max_rows_per_page
---------- -----------------  ----------  -----------------------
auidind    clustered, unique  au_id       0
aunmind    nonclustered       au_lname,   0
                              au_fname 
 
(2 rows affected, return status = 0)

To update the statistics for all of the indexes, type:

update statistics authors

To update the statistics only for the index on the au_id column, type:

update statistics authors auidind

Because Transact-SQL does not require index names to be unique in a database, you must give the name of the table with which the index is associated. Adaptive Server runs update statistics automatically when you create an index on existing data.

Updating partition statistics

Like the update statistics command for unpartitioned tables, the update partition statistics command helps Adaptive Server make the best decisions when it processes a query, by keeping it up to date about the number of pages within the partitions. Use update partition statistics when a large amount of data in a partitioned table has been added, changed, or deleted.

Permission to issue the update partiton statistics command defaults to the table owner and is not transferable. Its syntax is:

update partition statistics table_name

For example, suppose the authors table was partitioned as follows:

alter table authors partition 3

Then you run sp_helpartition to see how the partitions were distributed:

sp_helpartition authors

partitionid firstpage   controlpage ptn_data_pages
----------- ----------- ----------- --------------
          1         553         554              1
          2         817         816              1
          3        1009        1008              1
 
(3 rows affected, return status = 0)

Afterwards, you update the statistics for authors as follows:

update partition statistics authors

Using sp_helpartition on authors shows the following update:

partitionid firstpage   controlpage ptn_data_pages
----------- ----------- ----------- --------------
          1         553         554             10
          2         817         816              1
          3        1009        1008              1
 
(3 rows affected, return status = 0)

Dropping and re-creating a clustered index automatically redistributes the data within partitions and updates the partition statistics.


	Expand Search