Partitioned table maintenance activity requirements depend on the frequency and type of updates performed on the table.
Partitioned tables that require little maintenance include:
Tables that are read-only or that experience very few updates. In the second case, only periodic checks for balance are required
Tables where inserts are well-distributed across the partitions. Random inserts to partitioned heap tables and inserts that are evenly distributed due to a clustered index key that places rows on different partitions do not develop skewed distribution of pages.
If data modifications lead to space fragmentation and partially filled data pages, you may need to re-create the clustered index.
Heap tables where inserts are performed by bulk copy. You can use parallel bulk copy to direct the new data to specific partitions to maintain load balancing.
Partitioned tables that require frequent monitoring and maintenance include tables with clustered indexes that tend to direct new rows to a subset of the partitions. An ascending key index is likely to require more frequent maintenance.
