UPDATE_STATISTICS Command In MS SQL Server

In SQL, the main purpose of statistic is to store statistical information. It mainly refers to the distribution of values in one or more columns of an index or a table. However, the query optimizer of SQL Server uses this statistical information to approximate the cardinality, in the query result to be returned, or the number of rows. This all will help SQL Server Query Optimizer to generate high-quality query execution plan. To increase the query performance, a user can add statistic on the columns that do not have statistics in order.

Now, general MS SQL UPDATE_STATISTICS commands are basically used to update the query optimization statistics on an indexed view or a table view. The query optimizer updates statistics already by default to improve the query plan. However, in some situations, a user can improve the query performance by using UPDATE_STATISTICS command or the stored procedure sp_updatestats to update the statistics more frequently as compared to the default updates.

The main aim of updating statistics is to ensure that all queries that is to be compiled are with up-to-date statistics. Nevertheless, updating statistics can cause queries to recompile. It is always suggested that not to update statistics too frequently. It may cause the performance tradeoff between the time it took to recompile the query and improving query plans.

When to Use UPDATE_STATISTICS Command?

To have a better understanding of the general SQL UPDATE_STATISTIC commands, a user needs to know when to use these UPDATE_STATISTICS commands.

Whenever there is large processing of data, UPDATE_STATISTICS will updates the indexes on the tables.

There is a need to update the indexes when a large amount of deletions, bulk Copy into the tables or any modification is done.

UPDATE_STATISTIC command make sure all queries are to be complied with up-to-date statistics.

It can also use tempdb to sort the sample of rows to generates the statistics.

Moreover, it can also make queries to recompile every time update statistic command executed.

Syntax to execute UPDATE_STATISTIC Command

In order to execute the update query command, one must see the commands given below with its description:

Description of Arguments:

table_or_indexed_view_name

It is basically used to define the name of the table or the indexed view, which contains the statistic of the object.

index_or_statistics_name

It is the name of the index to update statistic or the name of statistic to update. The query optimizer updates all statistics for the indexed view or the table if the name of indexes or statistic is not specified.

FULLSCAN

By scanning all rows in the table or the indexed view, it will compute statistic. Moreover, the FULLSCAN and SAMPLE 100 PERCENT gives the same results. SAMPLE option and FULL SCAN cannot be used together.

SAMPLE number { PERCENT | ROWS }

When the query optimizer needs to updates statistic, it specifies the estimated percentage or number of rows in the table or the indexed view.

PERCENT: number can be from 0 to 100.

ROWS: number can be from 0 to the total number of rows.

RESAMPLE

With the help of most recent sample rate, it updates each statistic. Moreover, this command results in a full-table scan.

ON PARTITIONS ( { | } [, …n] ) ]

The Leaf-level statistic that covers the partition specified in the ON PARTITIONS clause is to be recomputed. After that it is merged to build the global statistic. Apart from this WITH RESAMPLE is also required to merge partition statistic built with different sample rates together.

ALL | COLUMNS | INDEX

It is the command that is used with all existing the statistics, statistic created on one or more columns or statistic created for indexes. If the user does not specify any option, the UPDATE STATISTIC statement will update all the available statistics on the indexed or table view.

NORECOMPUTE

It is the statement that is used to disable the automatic statistics update option. If a user specifies this option, the optimizer will first complete the statistics updates and then, disable the future updates.

INCREMENTAL = { ON | OFF }

In the INCREMENTAL command, when it is ON, as per partition statistics, the statistics are recreated. Additionally, when it is OFF, The SQL Server re-computes the statistics and the statistics tree is dropped. By default, it is OFF.

update_stats_stream_option

It is identified for informational purpose only. Moreover, it is not supported and future compatibility is not guaranteed.

Note: In order to make any changes, it requires ALTER permission on the view or table.

Conclusion

Statistics in SQL Server plays a very important role in executing query efficiently. However, SQL UPDATE STATISTIC commands are used to make sure that all queries should be complied properly. Moreover, it helps in the creation of high-quality query execution plan. Therefore, in this post, when & how one can use UPDATE_STATISTICS commands on SQL Server is discussed. Apart from this, the purpose of each and every argument is also discussed.

ABOUT THE AUTHOR

Naveen Sharma

Naveen Sharma has been a SQL Server DBA for over 2 years and has focused on Database Mirroring, Log Shipping, Replication etc. He is passionate about Microsoft SQL Server and regularly writes and shares his knowledge with SQL geeks