Why 2 allocation units during online index rebuild to different file group?

Question

I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done yet.

Strangely, the table appears to have too many allocation units!

1. Old file group, IN_ROW_DATA (normal)
2. Old file group, ROW_OVERFLOW_DATA (normal)
3. New file group, IN_ROW_DATA (normal)
4. New file group, ROW_OVERFLOW_DATA (normal)
5. New file group, IN_ROW_DATA (again!)

So two IN_ROW_DATA in the destination file group! One of them has data compression as NONE, one of them has PAGE. I'm doing the index rebuild WITH (DATA_COMPRESSION = PAGE, ONLINE = ON). The table is not partitioned.

You can see for yourself:

SELECT idx.name
, au.allocation_unit_id, au.type_desc, au.container_id, au.filegroup_id, au.used_pages, au.root_page
, par.index_id, par.rows, par.data_compression_desc
FROM sys.system_internals_allocation_units au
LEFT JOIN sys.partitions par ON (au.container_id = par.hobt_id AND au.type IN (1, 3)) OR (au.container_id = par.partition_id AND au.type IN (2))
LEFT JOIN sys.indexes idx ON par.object_id = idx.object_id AND par.index_id = idx.index_id
LEFT JOIN sys.objects obj ON par.object_id = obj.object_id
WHERE obj.name = 'X'
ORDER BY obj.name, idx.name, par.partition_number, au.filegroup_id, au.type_desc

File group 2 is the destination. Both IN_ROW_DATA allocation units have lots of pages in them so it is not like one of them is some kind of dummy. Also, the table is taking more than 2x the space that it should at this point. Clearly, the DATA_COMPRESSION setting didn't take!

Question: Why are there two allocation units in the same partition? Why doesn't my DATA_COMPRESSION setting cause the data to be compressed?

Edit: Lines 1 and 4 seem to correspond to the same data. They are both in the destination and they have the same row count. It seems like all rows are written twice - once compressed and once uncompressed. I can confirm the space is being used (or at least written and marked as allocated).

Edit 2: The INSERT DML plan for my table under rebuild shows an insert into 3 HOBT's. Not just 2 which would be expected during an online index rebuild. There are no non-clustered indexes defined. Here is the plan:

Edit 3: The rebuild has completed. The uncompressed (huge) partition is gone. The only thing left is the compressed one. Unfortunately, the allocated extents of both have been intermixed during writing. Using a visualizer tool that I have written, it looks like this:

(This is a crop of <1% of the whole allocation bitmap). Black = allocated, White = free (formerly the temporary partition).

That is a terrible outcome for sequential IO! So I switched out the table to an empty copy of it to be able to perform an ONLINE = OFF rebuild on it to a fresh file group (it is an insert-only table so I can switch it out whenever I want). That fixed things: The table is now contiguous and no temporary partition was seen.

Still, the question remains: Why does the ONLINE = ON CI rebuild cause all these nasty effects? How to fix it?

Answer 1

How Online Index Operations Work:

Temporary mapping index

Online index operations that create, drop, or rebuild a clustered index also require a temporary mapping index. This temporary index is used by concurrent transactions to determine which records to delete in the new indexes that are being built when rows in the underlying table are updated or deleted. This nonclustered index is created in the same step as the new clustered index (or heap) and does not require a separate sort operation. Concurrent transactions also maintain the temporary mapping index in all their insert, update, and delete operations.

Disk Space Requirements for Index DDL Operations

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. (My note: default is OFF)

Index ID 254 is indeed the mapping index. The Online Indexing Operations in SQL Server 2005 whitepaper has more details.