11.5 Note: Release 11.5 adds the ability to scan partitioned
tables in parallel and to create clustered indexes.
By default, SQL Server stores a heap table’s data in one double- linked chain of database pages. When a transaction inserts a row into the table, it holds an exclusive page lock on the last page of the page chain while inserting the row. As multiple transactions attempt to insert rows into the same table at the same time, performance problems can occur. Because only one transaction at a time can obtain an exclusive lock on the last page, other, concurrent insert transactions block, as shown in Figure 6-2.
Figure 6-2: Page contention during inserts
In release 11.0, SQL Server provides the ability to partition heap tables. A partition is simply another term for a page chain. Partitioning a table creates multiple page chains (partitions) for the table and, therefore, multiple last pages for insert operations.
When a transaction inserts data into a partitioned table, SQL Server randomly assigns the transaction to one of the table’s partitions. Concurrent inserts are less likely to block, since multiple last pages are available for inserts, as shown in Figure 6-3.
Figure 6-3: Addressing page contention with partitions
If a table’s segment spans several physical disks, SQL Server distributes the table’s partitions across those disks when you create the partitions. This can improve I/O performance when SQL Server writes the table’s cached data to disk, since the I/O is distributed over several devices.
SQL Server release 11.0 manages partitioned tables transparently to users and applications. Partitioned tables appear exactly like unpartitioned tables, except when accessed via the dbcc checktable and dbcc checkdb commands or when viewed with the new sp_helpartition procedure.
See Chapter 17, “Controlling Physical Data Placement,” of the Performance and Tuning Guide for information about how to create and administer partitioned tables.
