Contents
Do I need update statistics after rebuilding index?
You may be surprised to know that index rebuild doesn’t update all statistics. Note that non-index stats means the statistics associated with a column/columns that are automatically created or manually created.
What happens when we rebuild index?
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.
Do you need to update statistics after re-index?
The Microsoft Docs page for SQL Server statistics states: Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations.
What’s the difference between update and rebuild statistics statement?
Moreover, UPDATE STATISTICS statement updates two kinds of statistics such as index and column by default. Also, this operation saves the sample of records of the table. On the Other hand, REBUILDING INDEX will update only index statistics rather than column statistics.
What happens after a reorganize index and update?
Particularly, after a Reorganize Index as the update statistics operation will improve the index use by providing the optimizer with better data when creating an execution plan. Therefore, in our maintenance plan, we will add an Update Statistics task just after the Reorganize Index task.
What are non index statistics in rebuild index?
Note that non-index stats means the statistics associated with a column/columns that are automatically created or manually created. As you can see from above, don’t assume all of your statistics get updated just because you have a maintenance plan to rebuild index. Sometimes, non-index statistics are very critical as well.