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!
- Old file group, IN_ROW_DATA (normal)
- Old file group, ROW_OVERFLOW_DATA (normal)
- New file group, IN_ROW_DATA (normal)
- New file group, ROW_OVERFLOW_DATA (normal)
- New file group, IN_ROW_DATA (again!)
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?