jump to navigation

Partitioning best practices May 16, 2008

Posted by msrviking in Performance tuning.

Well, I am in a mood to get more entries into the list (maybe I was waiting for the Friday evening, so that I can fill in the missed entries Wink).

Here is the entry all about. I had to review partition design for one of the fat (oops did I just say fat!), yes, it is a fat fact table in a SSAS instance. The size of the table was somewhere around 360+ GB (data and indexes) and was partitioned and I had to review the partitioning strategy. I had prepared some best practices to check the design compliance, and here is the list

•    Avoid having partitions with more than 20 million rows
•    Combine partitions when a small partition has less than two million rows
•    The filtering key of the query should have the partition key
•    Each filegroup should logically represent only one partition
•    Each filegroup (mapped to partition) should have only one file
•    The filegroups can be on multiple disks
•    Partition function should map the boundary points to appropriate filegroups
•    Size of each partition shouldn’t exceed 250 MB
•    No of partitions in a database should not be greater than 100
•    The partition key of multiple tables (related tables) should necessarily use same partition function to get best alignment
•    The aligned tables should have equi-join predicate on the partitioned column to get collocation
•    The partition key must exist as a single column in the table and must meet a criteria
•    Use CHECK constraints to restrict domain of values to a specific range
•    Paritioned index should use the same partition function to optimize performance
•    Partitioned index can use the same partition scheme to achieve storage alignment
•    The non-volatile partitions can be placed on read-only filegroups
•    Non-clustered indexes should be partitioned to achieve storage alignment and colocation
•    Tempdb should have multiple files (one file per processor)
•    Tempdb should be separated on to a different disk controller with RAID 5
•    Partitioning aggregation cubes

Can you guys let me know what is missing or to be changed in this list?

Happy reading!




No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: