January 17, 2012 at 3:58 pm
Is there any value to partitioning a field that is frequently used in joins?
For ex.
Select * from table1
inner join table2 on table1.ID=table2.ID
where table2.mydate>=<date>
In the example above, I realize that the table2.mydate field would be used to partitio table2.
My question is..would there be any value in partitioning table1 using the ID field as the partition column?
January 18, 2012 at 2:08 am
Not enough info to give a sensible answer IMHO.
Usually you see performance gains when partitioning on a column that is frequently used in WHERE clauses. This helps the optimizer finding the partitions that have to be accessed and eliminate the partitions that don't have to be hit (it's called partition elimination).
If you don't filter the rows based on the partitioning key and don't eliminate partitions, you could end up slowing things down instead of improving performance, because SQL Server would have to read multiple b-tree structures instead of just one.
Partitioning has to be evaluated and tested thoroughly, that's the only way to tell.
-- Gianluca Sartori
January 19, 2012 at 5:15 am
Partitioning is an ADVANCED and COMPLEX feature that was never built in the first place for performance - it was primarily for improving ETL and maintenance. I HIGHLY recommend you get some professional help to assist you in determining your needs regarding partitioning and then with implementation if you pursue it. Many of my clients partition and make things worse when they never needed partitioning in the first place. They simply needed to properly tune and maintain their existing database application.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 19, 2012 at 6:42 am
sdruid71 (1/17/2012)
Is there any value to partitioning a field that is frequently used in joins?For ex.
Select * from table1
inner join table2 on table1.ID=table2.ID
where table2.mydate>=<date>
In the example above, I realize that the table2.mydate field would be used to partitio table2.
My question is..would there be any value in partitioning table1 using the ID field as the partition column?
In general terms the answer would be "no".
Partitioning is rarely done for peformance purposes, more often for admin purposes like helping during purging/archiving processes.
Performance issue can be addressed by having a good indexing strategy.
_____________________________________
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.January 19, 2012 at 8:09 am
Going along with the other responses... Partitioning is used to help better manage the administration of SQL Server. A great example of this is something that we do:
We have a database that takes on about 4GB of new data each month. So, doing a full backup once a week over 3 years of data is a lot (of course the monthly data has grown each month so it was not bad the first year). So, what we have done is partitioned the main table that sees this growth on a date column that basically indicates insertion date. We do not update this table, so at the end of a month we make the filegroup read only, back it up, and then perform a full backup on the read-write partitions. We can move these read only backups to a different site that is optimized for storage and keep our weekly full backups significantly smaller.
NOTE: There is a lot more to this than what I've explained, but I think this generalization gives 1 good example of how partitioning is used.
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply