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
Last activity: Aug 25, 2024, 08:34 AM