When you are planning for partitioned tables, consider how to maintain:
Load balance across the disk for partition-based scan performance and for I/O parallelism
Cclustered indexes, which require approximately 120% of the space occupied by the table to drop and re-create the index or to run reorg rebuild
The space planning decisions you make depend on the:
Availability of disk resources for storing tables
Nature of your application mix and of the incoming data (for semantic-partitioned tables)
Estimate the frequency with which your partitioned tables need maintenance: some applications need indexes to be re-created frequently to maintain balance, while others need little maintenance.
For those applications that need frequent load balancing for performance, having space in which to re-create a clustered index or run reorg rebuild provides fastest and easiest results. However, since creating clustered indexes requires copying the data pages, the space available on the segment must be equal to approximately 120% of the space occupied by the table.
See “Determining the space available for maintenance activities”.
The following descriptions of read-only, read-mostly, and random data modification provide a general picture of the issues involved in object placement and in maintaining partitioned tables.
See Chapter 10, “Partitioning Tables and Indexes” in the Transact-SQL Users Guide for information about the specific tasks required during maintenance.
