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»»

Sub partitions Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
Hello there,

Does SQL Server 2008 R2 support Sub partitions?

Thanks
Post #1496289
Posted Thursday, September 19, 2013 4:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
What do you mean by 'sub partitions'? What are you looking to do?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496295
Posted Thursday, September 19, 2013 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
I have a big table which is currently partitioned on a column called SupplierName. We get new data every week, for each supplier, and inserting data into this massive table takes a long time. Currently, we drop indexes, load data and then recreate indexes.

If I could further partition data under each Supplier then I could just switch partitions instead of loading data
Post #1496298
Posted Thursday, September 19, 2013 4:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
Ah, no.

That said, sounds like you might want to re-do the partitioning on the table and instead partition on date rather than supplier. Then you have your fast load and fast delete (if necessary) based on time.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496307
Posted Thursday, September 19, 2013 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
Thanks.

Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?

Post #1496319
Posted Thursday, September 19, 2013 6:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
Partitioning isn't about performance anyway. It's about maintainability, fast loads, etc. You may need to change nonclustered indexes, or you may not. Can't tell without a lot more information.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496348
Posted Thursday, September 19, 2013 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
Thanks for your responses. They were very helpful
Post #1496527
Posted Thursday, September 19, 2013 4:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
sf.onlineforums (9/19/2013)
Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?


That's indeed a valid concern. If your queries typically go by supplier disregarding the data, you will need to have indexes on supplier which is not partitioned-aligned. since else the data for the supplier is spread out all over the partitions, and performance will suffer.

Then again, if you are not loading or dropping data by supplier, I am not sure that there is much point of partitioning by supplier at all.

if you have a limited number of supplieries, one option would be to construct a an artifical key which is a combination of supplier and date, and now you can have the sub-partitions you desired. But you will need to modify you queries to use a range on this artificial key to query for a certain supplier.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496638
Posted Saturday, September 21, 2013 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
Thanks Erland.

Loading data by date, every week, is the main reason for redesigning partitions to be based on dates. I like your idea of generating an artificial key. Do you recommend partitioning the table on artificial key or just creating indexes would do the same thing?
Post #1497122
Posted Saturday, September 21, 2013 2:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
Welcome to 'it depends'

If you want to do fast loads and fast deletes (switch partitions in/out), then partition. If you want to do index maintenance at the partition level, then partition. If not, then probably no need to partition



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1497125
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse