question

half fast dba avatar image
half fast dba asked

When an index is reorganized what is compaction.

From https://technet.microsoft.com/en-us/library/ms189858(v=sql.110).aspx We have "Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value" I can understand the physical re-ordering. However the compaction part confuses me. If a page space uses more space than the fill factor then surely compaction down to the fill factor value will cause a split which could affect pages at a higher level.
indexreorganize
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
If the index is being rebuilt, it won't split. It will calculate how much it can put onto a page and then fill it, to the fill factor. This process only occurs during the rebuild (or the initial build) of an index. The data is resorted and then written out to the pages. Processes that would cause a split occur because it's adding data within the range of existing data on a page. This doesn't happen during a rebuild process.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.