If a device is nearly full, re-creating a clustered index does not balance data across devices. Instead, the device that is nearly full stores a small portion of the partition, and the other space allocations for the partition steals extents on other devices. Figure 5-4 shows a table with nearly full data devices.
Figure 5-4: Partitions almost completely fill the devices
After adding devices and re-creating the clustered index, the result might be similar to the results shown in Figure 5-5.
Figure 5-5: Extent stealing and unbalanced data distribution
Once the partitions on device2 and device3 use the small amount of space available, they start stealing extents from device4 and device5.
In this case, a second index re-creation step might lead to a more balanced distribution. However, if one of the devices is nearly filled by extent stealing, another index creation does not solve the problem.
Using bulk copy to copy the data out and back in again is the only sure solution to this form of imbalance.
To avoid situations such as these, monitor space usage on the devices, and add space early.
