Chapter 32. Partitioning for manageability (and maybe performance)
Large tables are a bane to database administrators (DBAs). It takes seemingly forever to rebuild indexes on tables with many millions or billions of rows, not to mention the exorbitant space required to do so. For example, you must maintain more than 1 terabyte (TB) of database free space just to rebuild the clustered index on a 1 TB table, because both the old and new index must coexist until the rebuild is done. Developers are challenged to design applications that can load, purge, or archive vast amounts of data efficiently while providing high data availability.
SQL Server’s table and index partitioning feature is a great way to address these manageability and load/purge/archive issues. But you must be aware that partitioning is a fundamental structural change that influences execution plans and query performance. Care is needed to implement partitioning wisely to maximize performance and manageability benefits while avoiding potential problems. Before I discuss important partitioned table design considerations and common pitfalls, let’s first review core partitioning concepts.
