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.

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

Solarwinds

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)

Solarwinds
, , , , ,
Previous Post
How to Find Queries Using an Index From SQL Server Plan Cache? – Interview Question of the Week #195
Next Post
What is Read Ahead Read in SQL Server? – Interview Question of the Week #197

Related Posts

2 Comments. Leave new

Leave a Reply

Menu