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 does 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 query optimization is the accuracy of the distribution steps. If there are significant changes in the key values in the index, re-run 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]
Because running update statistics is resource intensive, try to run update statistics at a time when the tables you specify are not heavily used. update statistics acquires locks on the remote tables and indexes as it reads the data. If you use trace flag 11209, tables are not locked.
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. For more information see Chapter 4, “Using Statistics to Improve Performance” in the Performance and Tuning Guide: Monitoring and Analyzing.
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 triggers and procedures that use the indexes use the new distribution:
sp_recompile authors
