Do Index Reorganization Update Statistics? – Interview Question of the Week #196

Question: Do Index Reorganization Update Statistics?

Answer: No. Index Reorganization does not update Statistics of your database.

This is one of the most common questions I receive during my SQL Server Performance Tuning Practical Workshop.

Whenever there is index reorganization, it operates one page a time of the index and hence it does not have an idea what the entire index contains. Due to this reason, it has no idea of the content of the index entirely, which is preventing index reorganization to not update the statistics. If you prefer to reorganize your index due to low fragmentation, I strongly suggest that you update your statistics after the index reorganizations.

However, if you are rebuilding your indexes, that particular operation automatically updates the statistics for the column on which index is created. Here is a sample script to reorganize any index in SQL Server.

ALTER INDEX [name_of_the_index] ON [table_name] REORGANIZE; 

If you want to reorganize all the indexes on any table, you can run the following syntax.

ALTER INDEX ALL ON [table_name] REORGANIZE;

However, if you want to rebuild all the indexes on the table, you can run the following command.

ALTER INDEX ALL ON [table_name] REBUILD;

Additionally, please note that index reorganization is an online operation and index rebuilding is an offline operation unless explicitly specified as an online operation.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

Menu
Exit mobile version