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. 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 keys index_description index_max_rows_per_page ---------- ----------------- ---------- ----------------------- auidind au_id clustered, unique 0 aunmind au_lname, au_fname nonclustered 0 index_fillfactor index_reservepagegap index_created index_local ----------------- ---------------------- -------------------- ------------- 0 0 Apr 13 2005 10:30AM Global Index 0 0 Apr 13 2005 10:30AM Global Index (2 rows affected) index_ptn_name index_ptn_seg ------------------ --------------- auidind_384001368 default aunmind_384001368 default (2 rows affected)
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.
You can set update statistics to run automatically at the time that best suits your site and avoid running it at times that hamper your system.
