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

Question: Does reorganizing an index update its statistics?

Answer: No. ALTER INDEX ... REORGANIZE does not update statistics. Rearranging index pages and refreshing the optimizer’s data-distribution statistics are different operations.

I hear this question often in my SQL Server Performance Tuning Practical Workshop. Reorganization works incrementally through the index; do not assume that less fragmentation means its statistics are now current.

-- Substitute your own table and index names after reviewing maintenance needs.
ALTER INDEX [name_of_the_index] ON [table_name] REORGANIZE;
ALTER INDEX ALL ON [table_name] REORGANIZE;
ALTER INDEX ALL ON [table_name] REBUILD;
-- Statistics are a separate action when reorganizing:
UPDATE STATISTICS [table_name] [name_of_the_index];

The first statement reorganizes one index, the second reorganizes the table’s indexes, and the third rebuilds them. These are separate maintenance choices, not a script that needs all three in sequence. Update relevant statistics after a reorganization when the data and workload need it; choose the sample or FULLSCAN based on that need.

A conventional rowstore index rebuild updates that index’s statistics. It does not update every independently created column statistic on the table. Partitioned index rebuilds and resumable rebuilds have sampling differences, so “a rebuild always gives every statistic a full scan” would be too broad.

Reorganization is online. A rebuild can be offline or, where supported, use an explicitly requested online operation. Online operations can still require locks at particular stages. Choose maintenance for a measured problem rather than running it merely because a percentage crosses an arbitrary threshold.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.