Routine monitoring for partitioned tables should include the following types of checks, in addition to routine database consistency checks:
Use sp_helpartition to check the balance on partitions. If some partitions are significantly larger or smaller than the average, recreate the clustered index to redistribute data.
Use sp_helpsegment to check the balance of space on underlying disks.
If you recreate the clustered index to redistribute data for parallel query performance, check for devices that are nearing 50% full. Adding space before devices become too full avoids the complicated procedures described earlier in this chapter.
Use sp_helpsegment to check the space available as free pages on each device, or use sp_helpdb to check for free kilobytes.
You might need to recreate the clustered index on partitioned tables because:
Your index key tends to assign inserts to a subset of the partitions.
Delete activity tends to remove data from a subset of the partitions, leading to I/O imbalance and partition-based scan imbalances.
The table has many inserts, updates, and deletes, leading to many partially filled data pages. This condition leads to wasted space, both on disk and in the cache, and increases I/O because more pages need to be read for many queries.
