Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 225

Figure 8-20
Horizontal
Horizontal partitioning involves moving rows into separate tables. This is the area where SQL Server 2005
has improved the support for partitioning. However, this is only available in the Enterprise and Devel-
oper editions. Advantages to doing this can make large tables or indexes more manageable, because
it gives you the option of managing subsets of data instead of all of it. Another advantage is that the
individual partitions are associated with one file group. This means there can be multiple file groups
matching the number of partitions. Also note that a partitioned table or index work just like their unpar-
titioned counterpart. In fact, according to BOL SQL Server treats all tables as partitioned, even if the table
only has one partition.
WhentoPartition
Creating horizontal partitions is usually done for one of two reasons. One reason is to allow easy man-
agement of subsets of data. The other reason is to improve query performance. Note that a query that
will cause more than one partition to be referenced may have an impact on performance. This is because
these types of queries only use one thread per partition.
Partitioning for Subsets of Data
By dividing a table into multiple partitions allows you to easily move those partitions from one table to
another. You can easily add an existing table to another partitioned table. You can also switch partitions
from one partitioned table to another.
Partitioning for Performance
One obvious use for partitions is performance. However, there are a few things to keep in mind here.
Otherwise it's possible to create a partition plan that actually hurts performance instead of enhancing it.
The first issue involves frequently joining two or more partitioned tables. In this case the tables should
have the same partition column as the column on which the tables are joined. Also, these partitioned
tables should be located together. The easiest way to do that is for each table to use the same partition
function. Doing this allows the query optimizer can process the join faster, because the partitions them-
selves can be joined. Beware however, that if a query joins two tables that are not located together or are
the partition column and the join column are different, the presence of partitions may actually hinder
performance instead of enhancing it.
Another issue to be aware of is the idea of placing your defined file groups on separate hard drives in an
effort to improve I/O performance. According to BOL, when SQL Server performs data sorting for I/O