May 29, 2009 at 5:47 am
Hello,
I have following question. We want to implement partitioning fot two tables. We want to create 13 partitions for each month. The question is how all partition operations like join, split, switch etc will work if we have two table partitioned. Can we still benefit from fast actions without locking db and copying rows?
Partition1
tableA_May
tableB_May
partiotion2
tableA_June
tableB_June
etc
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
May 29, 2009 at 6:11 am
I think you will find the response to your doubts in this article.
http://technet.microsoft.com/en-us/library/aa964122(SQL.90).aspx
Hope this helps
Gianluca
-- Gianluca Sartori
May 29, 2009 at 6:19 am
Nice article but I have two partition tables which share the same disks.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
May 29, 2009 at 6:33 am
Oh, I see. Well, I'm no partitioning expert, but I don't think you can partition tables "together", but you will have to partition each table on its own and then join the resulting swhitched partitions from the aux tables.
Maybe I keep on misreading your post, sorry if this is the case.
-- Gianluca Sartori
May 29, 2009 at 6:44 am
It's ok. I have separate partition function and schema for each table. My concern is that during switch affected disk (we defined separate for each file grup) have two partitions for different tables. What Ms sql qill do in such situation? Make fast switch as usually or will copy data?
More detail configuration:
Disk f:
FG1A File group 1 for Table A, with data from May
FG1B File group 1 for Table B, with data from May
Disk g:
FG2A File group 2 for Table A, with data from June
FG2B File group 2 for Table B, with data from June
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
May 29, 2009 at 6:45 am
berto (5/29/2009)Partition1
tableA_May
tableB_May
partiotion2
tableA_June
tableB_June
Hard to follow...
Do you really want to create partitions using pieces of different tables?
Imagine TableA and TableB are actually a piece of Genoa Salami and a piece of Pepperoni.
When you partition them you slice Genoa Salami in small pieces on one side of the chopping board and you slice Pepperoni in small pieces on the other side -you end up neither with Genoa Pepperoni nor Peppelami; just sliced Genoa Salami and sliced Pepperoni. Capici? 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 29, 2009 at 6:54 am
Do you have another solution if you have application with two big tables both can benefit a lot from partitioning. We need 14 partitions for each plus regular 5 drives (tempdb, other data etc. it gives 33 letters so we can't even implement this (no letters available).
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
May 29, 2009 at 7:00 am
Partitioning is a useful resource if properly done -if not it has the potential of becoming you worst nightmare.
Partitioning strategy should at least help in one of the cases below:
1) help during querying.
2) help during archiving/purging.
If during your analysis you are not 100% certain it will help on at least one of the above just forget partitioning.
As a rule of thumbs partition key should be included in most queries to help in the first case and partition key should allow for partition switching/truncate to help in the second case.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 29, 2009 at 7:02 am
I think this is a strategy issue rather than strictly technical.
I suggest you take a look at this
whitepaper from Kimberly Tripp, which focuses more on the strategies regarding filegroups and disks.
I hope you find it helpful as I did.
Gianluca
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply