Strange index rebuild behavior | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Strange index rebuild behavior

I rebuild an index and check for the physical defragmentation, I observe 0% fragmentation. That’s ok, is what I spected. Next I run again the command and check again for the physical defregmentation and I observe 80% fragmentation… this was not spected. why is this happening?? The worst thing is that you can repeat the proces and observe that the fragmentation go from 0% to 80%… it is very strange!!!!. Initially, after the first rebuild the index should keep in 0%. the command used was:
use adventureworks
ALTER INDEX pk_employee_employeeid ON HumanResources.Employee REBUILD
its not clear to me that an index rebuild or even defrag will actually defrag the index
what if there is not free contiguous space to accommodate the index in the first place the defrag command was issued on a table or index, not the entire db
the only way to guarantee contig free space is to have a dedicated filegruop for the index
]]>