what is 'update statistics'

How update statistics works to improve the query performance ?
When to use update statistics ?
Difference between update statistics and update index statistics.

Indexes are of great help in SQL performance. The keys involved in this index are spread all over the table's memory, database server keeps a "statistics" of these key's distribution. This statistics is then used by query optimizer while making the decision about, which index to be used in query or if at all index should be used.

update statistics, updates this "statistics data" and makes sure the database optimizer gets updated information.

Use update statistics, If there is significant change in the key values in your index, or if a great deal of data in an indexed column has been added, changed, or removed.

You can update statistics for all the indexes in a table or specifically only for one index.

--#update statistics for the leading columns of all indexes on the table.
update statistics tableName

--#updates statistics for the leading column of an index.
update statistics tableName indexName

Note that, in above examples only 'leading' columns of the index are considered while updating statistics. To update statistics for all of the index columns use following.

--#update statistics for all the columns of all indexes on the table.
update index statistics tableName

--#updates statistics for all the columns of an index.
update statistics tableName indexName

update statistics note :

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.


Related Posts :
How to get last 'update statistics' run date