Update Statistics in SQL Server

It's a process of updating the statistics that the Query Optimizer uses to make decisions about query execution plans. Statistics provide information about the distribution of data in a table or index and help the Query Optimizer determine the most efficient way to execute a query.

When a query is executed, the Query Optimizer uses statistics to determine the most efficient way to execute the query by estimating the number of rows that will be returned for each operation in the query plan. The Query Optimizer then selects the plan that it estimates will return the results the quickest.

Statistics are created and updated automatically by SQL Server, but you can manually update them as well. Updating statistics can improve query performance by ensuring that the statistics used by the Query Optimizer are accurate and up to date.

There are two main ways to update statistics in SQL Server:

1. Using the UPDATE STATISTICS statement: This statement updates the statistics for a specific table or index. Here's an example:

UPDATE STATISTICS table_name [index_name] [WITH [FULLSCAN | SAMPLE number { PERCENT | ROWS }]]

2. Using the sp_updatestats stored procedure: This procedure updates the statistics for all tables and indexes in the database.

Microsoft recommends updating statistics regularly to ensure that query optimization is based on accurate and up-to-date data distribution information. You can schedule regular statistics updates as a maintenance task, either by using SQL Server Maintenance Plan or a custom T-SQL script. Microsoft also suggests updating statistics after large data modifications, such as bulk inserts or deletes.

It's important to note that automatic statistics update feature is enabled by default in SQL Server and it kicks in when the statistics have become stale, which is determined by the number of modifications since the last statistics update, the number of rows in the table, and the AUTO_UPDATE_STATISTICS_ASYNC database option.