An imbalance of pages in partitions usually occurs when partitions have run out of space on the device, and extents have been allocated on another physical device. This is called extent stealing.
Extent stealing can take place when data is being inserted into the table with insert commands or bulk copy and while clustered indexes are being created.
The effects of an imbalance of pages in table partitions is:
The partition statistics used by the optimizer are based on the statistics displayed by sp_helpartition.
As long as data distribution is balanced across the partitions, parallel query optimization will not be affected. The optimizer chooses a partition scan as long as the number of pages on the largest partition is less than twice the average number of pages per partition.
I/O parallelism may be reduced, with additional I/Os to some of the physical devices where extent stealing placed data.
Re-creating a clustered index may not produce the desired rebalancing across partitions when some partitions are nearly or completely full.
See “Problems when devices for partitioned tables are full” for more information.
