October 25, 2009 at 10:56 pm
Hi
We have some databases which are around 10gb now. Due to performance issues I was instructed to partition the tables. I've gone through some sites & got information about partitioning the existing tables. I have few questions:
1. Can we do partitioning without having multiple filegroups? If so, will there be any performance difference?
2. What is the best way to partition tables with data (if possible without disturbing existing clustered index)?
Rgds
Mohan Kumar VS
October 26, 2009 at 3:17 am
Mohan Kumar-480059 (10/25/2009)
We have some databases which are around 10gb now. Due to performance issues I was instructed to partition the tables. I've gone through some sites & got information about partitioning the existing tables. I have few questions:
1. Can we do partitioning without having multiple filegroups? If so, will there be any performance difference?
Yes you can have as many or as few Filegroups as you like on Partitioned tables. You won't get any performance improvements just by partitioning.
Bear in mind Partitioning is a technology primarily for data Management & is not designed as performance enhancing - though as you have hinted performance gains can be made by splitting the data over filegroups.
However I wouldn't have thought 10GB of data is enough to bother with the hassle of partitioning.
2. What is the best way to partition tables with data (if possible without disturbing existing clustered index)?
I'm not sure there is a "best" way it depends on your data but time based partitioning is often used. In our case more recent data is on filegroups on a very fast disk volume as this tends to be "hot" i.e. it is the data people are using most often.
Older data is in Filegroups on slower, less expensive storage, performance is slower but the data is used less so it's not so important.
Anyway - hope this helps!
S C Penguin
October 26, 2009 at 4:58 am
Thanks a lot.
October 27, 2009 at 5:10 am
1) the first poster is incorrect. you CAN get performance increases from partitioning without multiple filegroups - sometimes vast improvements even. This would come from partition elimination during query execution.
2) the questions you are asking mean you have no idea about partition elimination. This is a VERY advanced and complex feature and you REALLY need to get a professional on board to help you out.
3) Note that partitioning is an Enterprise Edition only feature.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 5:12 am
Oh, and one more thing - 10GB is NOT a large database. My guess is that there are MANY other things you should be doing first to improve your performance before even thinking about partitioning.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 5:21 am
TheSQLGuru (10/27/2009)
1) the first poster is incorrect. you CAN get performance increases from partitioning without multiple filegroups - sometimes vast improvements even. This would come from partition elimination during query execution.
I didn't say you CAN'T get performance gains I was simply pointing out that table partitioning is designed primarily as Management technology rather than a performance enhancing technology.
October 27, 2009 at 8:05 am
SinisterPenguin (10/27/2009)
TheSQLGuru (10/27/2009)
1) the first poster is incorrect. you CAN get performance increases from partitioning without multiple filegroups - sometimes vast improvements even. This would come from partition elimination during query execution.I didn't say you CAN'T get performance gains I was simply pointing out that table partitioning is designed primarily as Management technology rather than a performance enhancing technology.
I guess I must have somehow misinterpreted this statement from you then: "You won't get any performance improvements just by partitioning."
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 8:11 am
TheSQLGuru (10/27/2009)
SinisterPenguin (10/27/2009)
TheSQLGuru (10/27/2009)
1) the first poster is incorrect. you CAN get performance increases from partitioning without multiple filegroups - sometimes vast improvements even. This would come from partition elimination during query execution.I didn't say you CAN'T get performance gains I was simply pointing out that table partitioning is designed primarily as Management technology rather than a performance enhancing technology.
I guess I must have somehow misinterpreted this statement from you then: "You won't get any performance improvements just by partitioning."
Don't get me wrong I'm happy to be corrected - but if you just partition a table & don't make any other changes, i.e. all partitions are still on the same Filegroup how will performance improve?
October 27, 2009 at 8:56 am
Performance will improve because of exactly what I stated: partition elimination. You can have queries that will now touch just a (perhaps tiny) fraction of the table instead of the entire thing --> less IO --> better performance. Also NC indexes will likely be less deep, resulting in gains for index scans and even the already-speedy seek as well.
Despite Microsoft's statement that partitioning is primarily a maintenance feature, most users pursue it primarily for performance reasons. That is my take - YMMV.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 9:04 am
OK - fair enough I stand corrected & I learned something 🙂
October 27, 2009 at 9:19 am
SinisterPenguin (10/27/2009)
OK - fair enough I stand corrected & I learned something 🙂
Which is precisely why we are all here on the SQLServerCentral forum!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 2:54 pm
You can also get a performance gain regarding backups and restores.
If partitioned tables in your PRIMARY filegroup only hold 6 months of data, your filegroup backup may only take 2 hours rather that the 8 or 10 hours for the full DB. You can mix and match full and diff strategies with tlog backups as well. This may be helpful if your SQL server is a busy 24/7 box.
As your partitioned tables cascade data down into an ARCHIVE filegroup once a month, you may decide to back this filegroup up only after the cascade has on, say the morning of the 1st Sunday of the month. This can be two steps of the same job or maintenance plan.
Another easy win is that you can have your trashed DB up and running in only 2 hours by just restoring the PRIMARY filegroup. SQL 2005 or 2008 will complain but will allow you to run with only "half a database". You can then restore the ARCHIVE filegroup out of hours. The term "Hero" springs to mind 😎
Hope this helps.
October 27, 2009 at 3:15 pm
Mark_Pratt (10/27/2009)
You can also get a performance gain regarding backups and restores.If partitioned tables in your PRIMARY filegroup only hold 6 months of data, your filegroup backup may only take 2 hours rather that the 8 or 10 hours for the full DB. You can mix and match full and diff strategies with tlog backups as well. This may be helpful if your SQL server is a busy 24/7 box.
As your partitioned tables cascade data down into an ARCHIVE filegroup once a month, you may decide to back this filegroup up only after the cascade has on, say the morning of the 1st Sunday of the month. This can be two steps of the same job or maintenance plan.
Another easy win is that you can have your trashed DB up and running in only 2 hours by just restoring the PRIMARY filegroup. SQL 2005 or 2008 will complain but will allow you to run with only "half a database". You can then restore the ARCHIVE filegroup out of hours. The term "Hero" springs to mind 😎
Hope this helps.
Good stuff to point out Mark!
One minor point is that I think it is best practice to have no actual user objects/data in the PRIMARY file group. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 4:10 pm
TheSQLGuru (10/27/2009)
Good stuff to point out Mark!
One minor point is that I think it is best practice to have no actual user objects/data in the PRIMARY file group. 🙂
Just trying to explain a complex topic as simply as I could but a good point never the less TSG! :satisfied:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply