Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VIII.6.1. Understanding SQL Server Partitioning

In most cases, database application performance barriers can be linked to a CPU-, memory-, or disk-based resource challenge. When faced with a disk-based resource obstacle, partitioning is one technique that you can use to distribute the workload and thereby boost throughput. When partitioning is in place, SQL Server is also more likely to take advantage of hardware that features multiple CPUs.

Generally speaking, to partition data (or an index) is to spread the information across multiple physical disk objects. Typically, these objects are found on separate disk drives, although this isn't mandatory. Disk drives and related information storage technology performance continue to advance; combining these with the sophisticated partitioning algorithms present in SQL Server can yield some dramatic improvements in responsiveness.

When setting up a partitioning plan, the database administrator is called upon to identify criteria that will help SQL Server determine how to distribute data onto multiple disk objects. It's important that the database administrator work closely with business and other technology domains to better understand the expected information workflows, making it possible to design an accurate and efficient partitioning strategy.

Before heading down the partitioning path, make sure that your performance bottlenecks (either ...