Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partition Existing Table with Data


Partition Existing Table with Data

Author
Message
Mohan Kumar VS
Mohan Kumar VS
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 344
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
SinisterPenguin
SinisterPenguin
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 282
[quote]Mohan Kumar-480059 (10/25/2009)
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?

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
Mohan Kumar VS
Mohan Kumar VS
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 344
Thanks a lot.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6000 Visits: 8314
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 at GMail
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6000 Visits: 8314
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 at GMail
SinisterPenguin
SinisterPenguin
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 282
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.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6000 Visits: 8314
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 at GMail
SinisterPenguin
SinisterPenguin
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 282
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?
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6000 Visits: 8314
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 at GMail
SinisterPenguin
SinisterPenguin
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 282
OK - fair enough I stand corrected & I learned something :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search