Sample Header Ad - 728x90

Does REORGANIZE compact pages and respect fill factor?

-2 votes
1 answer
88 views
I've always been told that REORGANIZE does nothing to fix poor page fullness because all that it does is reorder pages on disk. To investigate this, I read the documentation. However, I've found it contradicting itself. _Optimize index maintenance to improve query performance and reduce resource consumption_ [claims](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#reorganize-an-index) > Reorganizing also compacts index pages to make page density equal to the fill factor of the index. and [the docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#reorganizing-indexes) for ALTER INDEX support this > Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. but this is contradicted by [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#fillfactor--fillfactor) for fill factor > An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine doesn't dynamically keep the specified percentage of empty space in the pages. So, what's going on? Does REORGANIZE compact pages and respect fill factor? Or is it just moving leaf pages and not compacting?
Asked by J. Mini (1237 rep)
Aug 25, 2024, 03:04 AM
Last activity: Aug 25, 2024, 08:34 AM