Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Partition Existing Table with Data Expand / Collapse
Author
Message
Posted Sunday, October 25, 2009 10:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
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
Post #808486
Posted Monday, October 26, 2009 3:17 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 4:08 PM
Points: 45, Visits: 242
[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

Post #808546
Posted Monday, October 26, 2009 4:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
Thanks a lot.
Post #808582
Posted Tuesday, October 27, 2009 5:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 4,343, Visits: 6,150
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
Post #809199
Posted Tuesday, October 27, 2009 5:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 4,343, Visits: 6,150
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
Post #809200
Posted Tuesday, October 27, 2009 5:21 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 4:08 PM
Points: 45, Visits: 242
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.
Post #809206
Posted Tuesday, October 27, 2009 8:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 4,343, Visits: 6,150
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
Post #809328
Posted Tuesday, October 27, 2009 8:11 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 4:08 PM
Points: 45, Visits: 242
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?

Post #809337
Posted Tuesday, October 27, 2009 8:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 4,343, Visits: 6,150
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
Post #809374
Posted Tuesday, October 27, 2009 9:04 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 4:08 PM
Points: 45, Visits: 242
OK - fair enough I stand corrected & I learned something
Post #809381
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse