Partitioning best practices May 16, 2008
Posted by msrviking in Performance tuning.trackback
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 ).
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!
Thanks!
Comments»
No comments yet — be the first.