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!)
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?