Saturday, July 21, 2012

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.

No comments:

Post a Comment