Maximizing Performance using partitioning

Post date: Aug 25, 2014 7:1:6 PM

To improve query performance use partitioning on large tables. For small tables the administrative overhead of maintaining the partitions will outweigh any performance benefits. The goal of table partitioning is to eliminate scanning partitions that contain data that is not needed to satisfy a query. Consider table partitioning on large tables that can be divided into somewhat equal parts based on a defining criteria and the defining criteria is used in query predicates (WHERE clause). 

If the query access pattern (SELECT....WHERE) does not match the partitioning definition the benefit of partition elimination is not achieved. When defining partitioning criteria it is important to not have overlapping ranges if using range partitioning and to ensure list values are unique if using list partitioning.