Designing Partitions to Improve Query Performance
Partitioning a table or index may improve query performance, based on the types of queries you frequently run and on your hardware configuration.
Partitioning for Join Queries
If you frequently run queries that involve an equi-join between two or more partitioned tables, their partitioning columns should be the same as the columns on which the tables are joined. Additionally, the tables, or their indexes, should be collocated. This means that they either use the same named partition function, or they use different ones that are essentially the same, in that they:
Have the same number of parameters that are used for partitioning, and the corresponding parameters are the same data types.
Define the same number of partitions.
Define the same boundary values for partitions.
In this way, the SQL Server query optimizer can process the join faster, because the partitions themselves can be joined. If a query joins two tables that are not collocated or are not partitioned on the join field, the presence of partitions may actually slow down query processing instead of accelerate it.
Taking Advantage of Multiple Disk Drives
It may be tempting to map your partitions to filegroups, each accessing a different physical disk drive, in order to improve I/O performance. When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. Under this scenario, SQL Server accesses one drive at a time, and this might reduce performance. A better solution in terms of performance is to stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time. This configuration can be designed regardless of whether all partitions are in one filegroup or multiple filegroups. For more information about how SQL Server works with different RAID levels, see RAID Levels and SQL Server.
Controlling Lock Escalation Behavior
Partitioning tables can improve performance by enabling lock escalation to a single partition instead of a whole table. To reduce lock contention by allowing lock escalation to the partition, use the LOCK_ESCALATION option of the ALTER TABLE statement.