Different results rebuilding an index online and offline
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
CREATE TABLE [dbo].[Table]
(
Col BIGINT
)
CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)
INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2
And multiple runs of the following script
USE FragTest;
DECLARE @DbccPage TABLE (
ParentObject VARCHAR(255),
Object VARCHAR(255),
Field VARCHAR(255),
VALUE VARCHAR(255))
DECLARE @sp_index_info TABLE (
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID TINYINT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID));
DECLARE @I INT = 0
WHILE @I < 2
BEGIN
DECLARE @Online VARCHAR(3) = CASE
WHEN @I = 0 THEN 'OFF'
ELSE 'ON'
END
EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ONLINE = ' + @Online + ',
SORT_IN_TEMPDB = ON
);')
INSERT INTO @sp_index_info
EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );
; WITH T
AS (SELECT *,
PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
FROM @sp_index_info)
SELECT PageType,
MIN(PagePID) AS StartPID,
MAX(PagePID) AS EndPID,
COUNT(*) AS [count],
IndexLevel
FROM T
GROUP BY Grp,
PageType,
IndexLevel
ORDER BY PageType DESC,
StartPID
DECLARE @DynSQL NVARCHAR(4000)
SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
FROM @sp_index_info
WHERE PageType = 10
INSERT INTO @DbccPage
EXEC(@DynSQL)
SELECT VALUE AS SinglePageAllocations
FROM @DbccPage
WHERE VALUE <> '(0:0)'
AND Object LIKE '%IAM: Single Page Allocations%'
SELECT avg_page_space_used_in_percent,
avg_fragmentation_in_percent,
fragment_count,
page_count,
@Online AS [Online],
(SELECT COUNT(*)
FROM @DbccPage
WHERE VALUE <> '(0:0)'
AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
FROM sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
WHERE index_level = 0
DELETE FROM @sp_index_info
DELETE FROM @DbccPage
SET @I = @I + 1
END
I consistently got results like
Online = OFF
PageType StartPID EndPID count IndexLevel
-------- ----------- ----------- ----------- ----------
10 119 119 1 NULL
2 2328 2351 24 0
2 2352 2352 1 1
2 2384 2392 9 0
SinglePageAllocations
----------------------
(0 row(s) affected)
avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count page_count Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734 0 2 33 OFF 0
Online = ON
PageType StartPID EndPID count IndexLevel
-------- ----------- ----------- ----------- ----------
10 115 115 1 NULL
2 114 114 1 0
2 118 118 1 1
2 2416 2449 34 0
SinglePageAllocations
-----------------------
(1:114)
(1:118)
avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count page_count Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875 2.85714285714286 2 35 ON 2
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 the Online = 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.