table partitioning

Question

table partitioning

Ratheesh4sql

SSCommitted

Points: 1811
More actions
March 13, 2013 at 7:37 am

#275159

I planning to do table partitioning using the region wise. in my region table there are 18 region are there.... is it required for 18 partition ?
Could any one suggest me to do best in scenario.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Please do NOT just try to implement partitioning without a VERY good knowledge of it and a VERY good understanding of WHY you think you need it in the first place!! It is a complex subsystem and I have lost track of the number of clients and forum posters that have messed things up with it!!

Do your self and your company a HUGE favor and get a professional on board for a few days to help you understand your needs and potential solutions to problems!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 2

Thanks for your suggestion....

Could you please suggest me how to do the table partitioning using number of filegroup....?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why are you partitioning? What's the goal here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 4

planning to do the partitioning for the main table with region wise.....So that we can split the data into a different file group and the data accessible also will be more fast ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What are you trying to achieve by partitioning?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 6

Instead of moving records to a separate table. i am looking for a solution to move items into a different partition once they are old and require archiving...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Cool. In that case the business rules for how, when and by what criteria rows are archived will guide your choice of partition column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 8

Cool. In that case the business rules for how, when and by what criteria rows are archived will guide your choice of partition column.

Thank you.

I want to create 3 partitions Based on the List of region (RegionGroup1Current,RegionGroup2Current,RegionGroupCurrent) and Other 3 for one month old data (RegionGroup1Old,RegionGroup2Current,RegionGroupOld)

Here am looking to keep current data in first 3 partitions and after one month need to move into other 3 partitions based on the date.

Here i need to consider region and date.

Thanks in advance

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

ratheesh4sql (3/15/2013)
I want to create 3 partitions Based on the List of region

So the business rule is that you always archive and delete an entire region at a time. Odd, but if that's the rule then partitioning by region will help with archiving (which you said was the reason for partitioning in the first place)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 10

So the business rule is that you always archive and delete an entire region at a time. Odd, but if that's the rule then partitioning by region will help with archiving (which you said was the reason for partitioning in the first place)

Thanks for your input.

Yeah i am planning to do that way.

Region wise partitioning:

1. RegionGroup1Current,

2. RegionGroup2Current,

3. RegionGroupCurren

But here i bit confused to move the Items after one month to the another 3 partition which i created.

Is it through any job we need to handle ?

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 11

Thanks for your input.

Yeah i am planning to do that way.

Region wise partitioning:

1. RegionGroup1Current,

2. RegionGroup2Current,

3. RegionGroupCurren

But here i bit confused to move the Items after one month to the another 3 partition which i created.

Is it through any job we need to handle ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ratheesh4sql (3/15/2013)
Yeah i am planning to do that way.
Region wise partitioning:
1. RegionGroup1Current,
2. RegionGroup2Current,
3. RegionGroupCurren

So, when you archive, you archive (move to another table) and delete an entire region at a time?

But here i bit confused to move the Items after one month to the another 3 partition which i created.

Err, no, that's not how you do partitioning. Moving data between partitions should be avoided, that's why you always archive (move to another table) and delete an entire partition at a time.

Hence, currently you would be doing your deleting with

DELETE FROM <table name> WHERE Region = 1;

If that's what you use, then you partition by region and when you archive you swap the entire partition to the archive table.

I think you need to go and do a lot more reading on partitioning....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 13

Based on your description, you don't want to partition on region, you want to partition on date. You have a lot more reading to do before you should consider partitioning. Especially if you want to use different filegroups.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 14

Thank you..

http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

http://msdn.microsoft.com/en-us/library/aa964122%28v=sql.90%29.aspx

Based on the article from the link i hope we can do the table partition based on region into 3 partition and after a month we can archive into other three partition using Sliding Window Table Partitioning.....

But here am confused beginning itself to do the partition using Region by group wise

Region ID 1,2,3 = Partition1

Region ID 4,5,6 = Partition2

Region ID 7,8,9 = Partition3