Add a T-SQL command for "empty space compaction" in the SQL Servers Database files - by Franz Robeller

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 532533 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 2/10/2010 5:57:42 AM
Access Restriction Public


SQL Server does not do any kind of "empty space compaction", in order to be able to get a large continious block of empty space. This is importand to make sure that e.g. a INDEX REBUILD command, or e.g. the allocation for a new table or index does not get so many fragments.
Currently it is not guranteed that e.g. a index rebuild will result in less fragments.
Sign in to post a comment.
Posted by Ryan [MSFT] on 1/3/2012 at 12:31 PM
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.
Posted by Franz Robeller on 2/15/2010 at 4:26 AM
Hi Aaron,

actually a ALTER INDEX... REBUILD does NOT reduce the amount of fragments of a Index, if the empty space is highly fragmented. In order for this command to help reduce the fragments of a index, the empty space needs to be compacted, which is not the case today. This is similiar with a file system defragemtnation, there the last step is to compact the empty space!

Posted by AaronBertrand on 2/10/2010 at 10:24 PM
I'm not sure what could be gained that can't already be gained by either ALTER INDEX ... REBUILD or ALTER INDEX ... REORGANIZE.