October 10, 2007 at 3:04 pm
Table Partitioning
I have 10 GB ++ database and several huge tables 11 GB ++.
Table A11,491,956 KB
Table B3,434,327 KB
Table C8,988,324 KB
Table D3,264,062 KB
Table E4,579,444 KB
Table F4,966,772 KB
Table G7,788,823 KB
Table H2,159,155 KB
Table I69,221 KB
Table J35,753 KB
etc
Plan to setup table partitioning. Can anyone help and advise for the best practice.
I am new to the table partitioning concept and implemented.
Thank you for your help.
TJ 🙂
October 11, 2007 at 2:23 am
Hi TJ,
much depends on the type of disks you have (whether all your disks are the same speed/ you have some slower ones), whether there is a sensible way to split up your tables into portions that can have read-only/read-write segments, etc. Also, do you need to split later existing partitions. Once you have split the tables, you may consider using aligned indexes if you need to swap out partitions/non-aligned indexes to support some frequent joins that would perform faster if the indexes are partitioned differently.
There is an excellent whitepaper by Kimberly Tripp I can recommend: http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
Regards,
Andras
October 11, 2007 at 2:37 am
Edwin (10/10/2007)
I have 10 GB ++ database and several huge tables 11 GB ++.
Edwin,
I'm a bit confused by youe measurements, but during some discussions with the people at MS I was told that they usually recommend table partitioning for tables of 30 - 40 GB or larger. I'm sure I've seen it also in some documents but can't find them right now.
So partitioning in your case might not have the desired effect unless you expect a lot of grwoth in the future.
Markus
[font="Verdana"]Markus Bohse[/font]
October 12, 2007 at 9:41 am
Kimberly also just wrote a blog post last week about her preferred architecture for partitioning a table into two tables - one with read-only partitions and one with a single read-write partition - and a partitioned view over them all. That way you can have seperate sets of indexes in the read-only and read-write portions of your data. Check it out here.
You also need to be careful when setting up partitioning that your data isn't skewed. The way the query-processor parallelizes plans over partitioned tables in 2005 is to give one thread per partition. This can lead to decreased performance over a non- or single-partitioned table (where all threads go against the same partition in parallel) if one of the partitions is much larger than the others. SS2008 will solve this issue - see my blog post here.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 15, 2007 at 1:52 pm
Thank you for your responses. I am very appriaced your advises.
The actual DB size is 120 GB ++ and it keep growing.
Best regards,
TJ 🙂
October 16, 2007 at 4:38 am
Also, Partitioning takes a lot of planning. If you want to maintain partition alignment so that you can "Switch" partitions in/out, the indexes will need to be partitoined in the same manner (default behavior BTW). If you have requirements for a unique constraint (ie SSN in an employee table) that is not a subset of your partitioning key, your indexes will not be aligned and therefore cannot "Switch" in/out partitions. I have a script to illustrate some of the partitoning features/constraints at http://www.ricksql.com.
Hope this helps!
Rick Heiges, SQL MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply