Adding disks when devices are nearly full

If a device is nearly full, recreating 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 1-4 shows a table with nearly full data devices.

Figure 1-4: Partitions almost completely fill the devices

Image shows three devices, each with a partition. Once device is about 40 percent full, and the other two are nearly full.

After adding devices and recreating the clustered index, the result might be similar to the results shown in Figure 1-5.

Figure 1-5: Extent stealing and unbalanced data distribution

Image shows the same devices as the image above, but with an additional two devices and after a user has recreated the clustered index. Now the first device is half full, the next two devices are about 10 percent full, and the last two are about half full, and include a percentage of "stolen pages," which were stolen from devices 2 and 3.

Once the partitions on device2 and device3 use the small amount of space available, they start stealing extents from device4 and device5.

Re-creating the indexes another time may lead to a more balanced distribution. However, if one of the devices is nearly filled by extent stealing, re-creating indexes again does not solve the problem.

Using bulk copy to copy the data out and back in again is most effective solution to this form of imbalance.

To avoid situations such as these, monitor space usage on the devices, and add space early.