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, re-create the clustered index to redistribute data.
Use sp_helpsegment to check the balance of space on underlying disks.
If you re-create 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 sp_helpdb for free kilobytes.
In addition, run update partition statistics, if partitioned tables undergo the types of activities described in “Updating partition statistics”.
You might need to re-create 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 read for many queries.
