What is the use UPDATE_STATISTICS command?

We add statistics on the columns that don't have statistics in order to boost query performance. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan but in some cases you can improve query performance by using UPDATE_STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

UPDATE_STATISTICS command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

Here is T-SQL Command:
1. Updating All Statistics with sp_updatestats
EXEC sp_updatestats

2. Update all statistics on a table
UPDATE STATISTICS TableName

3. Update the statistics for an index
UPDATE STATISTICS TableName, IndexName

For more information, click here

The SQL Server Query Optimizer has the ability to use statistics on columns that are not indexed in order to create more optimal execution plans. Doing so can increase performance without having the overhead of indexes. Column statistics are generated automatically by SQL Server when queries are being optimized by the Query Optimizer. For example, column statistics are automatically created when there are currently no statistics available for the column being used as a search argument in a WHERE clause or a JOIN clause. There are two cases when SQL Server will not automatically create column statistics, even when they fit the criteria described above. These include: when the costs needed to create the column statistics are larger than the costs of the query plan; and when SQL Server is too busy.

If you want to find out if an index has had its indexes updated or not, you can use the DBCC SHOW_STATISTICS command, like this:

DBCC SHOW_STATISTICS(table_name, index_name)

This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining


Reference: MSDN