When to Partition your data

There are two main reasons to use partitioning in a large database environment. These reasons are related to management and performance improvement. Partitioning offers:

• Management at the individual partition level for data loads, indexcreation and rebuilding, and backup/recovery. This can result in less down time because only individual partitions being actively managed are unavailable.
• Increased query performance by selecting only from the relevant partitions. This weeding out process eliminates the partitions that do not contain the data needed by the query through a technique called
  partition pruning.
• When a table reaches a “large” size. Large is defined relative to your environment. Tables greater than  2GB should always be considered for partitioning.
• When the archiving of data is on a  schedule and is repetitive. For instance, data warehouses usually hold data for a specific amount of time (rolling window). Old data is then rolled off to be archived.

Take a moment and evaluate the criteria above to make sure that partitioning is advantageous for your environment. In larger environments partitioning is worth the time to investigate and implement.