Using update statistics

The update statistics command helps the server make the best decisions about which indexes to use when it processes a query, by providing information about the distribution of the key values in the indexes. update statistics is not automatically run when you create or re-create an index on a table that already contains data. It can be used when a large amount of data in an indexed column has been added, changed, or deleted. The crucial element in the optimization of your queries is the accuracy of the distribution steps. Therefore, if there are significant changes in the key values in your index, rerun update statistics on that index.

Only the table owner or the System Administrator can issue the update statistics command.

The syntax is:

update statistics table_name [index_name] 

Try to run update statistics at a time when the tables you need to specify are not heavily used. update statistics acquires locks on the remote tables and indexes as it reads the data. If trace flag 11209 is used, tables will not be locked.

The server performs a table scan for each index specified in the update statistics command.

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

After running update statistics, run sp_recompile so that triggers and procedures that use the indexes will use the new distribution:

sp_recompile authors