When planning for partitioned tables, the two major issues are:
Maintaining load balance across the disk for partition-based scan performance and for I/O parallelism
Maintaining clustered indexes requires approximately 120% of the space occupied by the table to drop and re-create the index or to run reorg rebuild
How you make these decisions depends on:
The availability of disk resources for storing tables
The nature of your application mix
You need to estimate how often your partitioned tables need maintenance: some applications need frequent index re-creation to maintain balance, while others need little maintenance.
For those applications that need frequent load balancing for performance, having space to re-create a clustered index or run reorg rebuild provides the speediest and easiest method. 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” for more information.
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 “Steps for partitioning tables” for more information about the specific tasks required during maintenance.
