I have a non-clustered, non-unique index on a foreign key column of type bigint
. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run the same rebuild index offline, the average fragmentation is 25%, with 4 fragments and 28 pages.
I think the FILLFACTOR
is 90. The database is 77GB. I'm not a DBA or similar, I'm a C# dev, so I'm not fully familiar with all the terms. There is no activity on this table, this is occurring in our Dev environment.
This is the query, with names redacted.
ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ONLINE = ON,
SORT_IN_TEMPDB = ON
);
What could be causing this difference? The same situation occurs on multiple tables.
Best Answer
This is by no means a full answer but may move things along a bit if you were to try something similar and report your results.
I couldn't reproduce them. With the following test table
And multiple runs of the following script
I consistently got results like
Online = OFF
Online = ON
At least in the test I did the differences between the two balanced out fragmentation wise (though similarly to your test I did find that rebuilding the index online led to a higher page count.).
I found that the
Online = OFF
version always used uniform extents and had zero single page allocations whereas theOnline = ON
always seemed to put the index root page and first index leaf page in mixed extents.Putting the first index leaf page in a mixed extent and the rest in contiguous uniform extents causes a fragment count of 2.
The
Online = OFF
version avoids the fragment caused by the lone index leaf page but the contiguity of the leaf pages is broken by the index root page that shares the same extents and this too has a fragment count of 2.I was running my test on a newly created database with 1 GB of free space and no concurrent activity. Perhaps the
Online = OFF
version is more vulnerable to concurrent allocations causing it to be given non contiguous uniform extents.