The guidelines above for read-only tables also apply to read-mostly tables with very few inserts. The only exceptions are as follows:
If there are inserts to the table, and the clustered index key does not balance new space allocations evenly across the partitions, the disks underlying some partitions may become full, and new extent allocations will be made to a different physical disk. This process is called extent stealing.
In huge tables spread across many disks, a small percentage of allocations to other devices is not a problem. Extent stealing can be detected by using sp_helpsegment to check for devices that have no space available and by using sp_helpartition to check for partitions that have disproportionate numbers of pages.
If the imbalance in partition size leads to degradation in parallel query response times or optimization, you may want to balance the distribution by using one of the methods described in “Steps for partitioning tables”.
If the table is a heap, the random nature of heap table inserts should keep partitions balanced.
Take care with large bulk copy in operations. You can use parallel bulk copy to send rows to the partition with the smallest number of pages to balance the data across the partitions. See “Using bcp to correct partition balance”.
