Partitioning table techniques which one is best way?

Question

Partitioning table techniques which one is best way?

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
April 7, 2020 at 10:42 am
Go to Answer
#3740988

Hello All,
I am about to do partitioning for my DW tables based on datetime and left range partition for every year.
If we partition with one file group for each year scheme is best or different filegroup & file(.ndf) for each scheme?
Which is the best way to handle this trillion records table?
Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
April 8, 2020 at 5:06 am
Answer
#3741292
When you have lot of budget then we will prefer the consultant, but handling sql 7.0 to till 2017 we need to take over this with being backup and fall back mechanism.
I have asked whether Vertical file-group(FG) partitioning vs Horizontal FG partitioning which is the best? while we doing this pre-prod practice have followed the below steps.
I am creating automated and dynamic horizontal partitioning Proc's to handle the huge size tables ~5GB with configured tables for forecasted key, range as Data architect for the couple upcoming year. let me release all SQL objects packages soon, once tested thoroughly
1. Moved not required historical data to archive table
2. Identified the required historical data to be present in that table obviously trillion to billions reduced
3. All columns required for analytics and maintenance cannot reduce the column so let it be.
4. Created Year partition for 5 years - ofc 6 FG,File and NEXT USED 5+1 calculation
5. Based on high data arrival from staging Data auto partition scheme and Update function automated
6. Compared the Single FG partitioning vs Multiple filegroup partitioning IO stats and query plan
7. Got drastic query performance that is reduced - Logical, scan, CPU time, Query parallelism, index seeks than Single partition vs multi FG.
8. Even for 5 years rolling and remove the old filegroup based on the 5 years retention is simple in 2017 and reclaim the DB files spaces.
9. This execution completed in 24 hours windows.
Thanks for all your advise!!
- This reply was modified 4 years, 7 months ago by Saravanan_tvr.
- This reply was modified 4 years, 7 months ago by Saravanan_tvr.
April 10, 2020 at 12:36 pm

This was removed by the editor as SPAM
April 16, 2020 at 8:34 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

A trillion row table and you folks have no previous experience with partitioning. My recommendation is that you don't try this on your own. Get a consultant that really knows and understands ALL of the caveats and problems that partitioning has, discuss it with them, and then have that consultant be the guy that pulls all of the strings on this project.

It's not a failure on your part to have a consultant come in on this. It could be a (or several) major failure on your part if you try this on your own with your current level of knowledge.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Saravanan_tvr wrote:

When you have lot of budget then we will prefer the consultant, but handling sql 7.0 to till 2017 we need to take over this with being backup and fall back mechanism.
I have asked whether Vertical file-group(FG) partitioning vs Horizontal FG partitioning which is the best? while we doing this pre-prod practice have followed the below steps.
I am creating automated and dynamic horizontal partitioning Proc's to handle the huge size tables ~5GB with configured tables for forecasted key, range as Data architect for the couple upcoming year. let me release all SQL objects packages soon, once tested thoroughly

<li style="list-style-type: none;">
Moved not required historical data to archive table

<li style="list-style-type: none;">
Identified the required historical data to be present in that table obviously trillion to billions reduced

<li style="list-style-type: none;">
All columns required for analytics and maintenance cannot reduce the column so let it be.

<li style="list-style-type: none;">
Created Year partition for 5 years - ofc 6 FG,File and NEXT USED 5+1 calculation

<li style="list-style-type: none;">
Based on high data arrival from staging Data auto partition scheme and Update function automated

<li style="list-style-type: none;">
Compared the Single FG partitioning vs Multiple filegroup partitioning IO stats and query plan

<li style="list-style-type: none;">
Got drastic query performance that is reduced - Logical, scan, CPU time, Query parallelism, index seeks than Single partition vs multi FG.

<li style="list-style-type: none;">
Even for 5 years rolling and remove the old filegroup based on the 5 years retention is simple in 2017 and reclaim the DB files spaces.

<li style="list-style-type: none;">
This execution completed in 24 hours windows.

Thanks for all your advise!!

Congratulations but, I've got to ask because of what you wrote above... the table you did all of this for is only 5GB in size???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 3

I am sorry - >5GB and predicted partition key which we have configured in configuration table.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

And I have to ask - what is your trillion size number - please put it fully here

1.000.000.000 -- for me this is 1 billion

and 5 GB ... that's a tiny table not worth the effort in most cases.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Saravanan_tvr wrote:

I am sorry - >5GB and predicted partition key which we have configured in configuration table.

I have to agree with Frederico... 5GB is very small and not worth the effort to partition. And, with that, I also have to agree with his question about your definition of a "trillion". A "trillion" is defined differently among different cultures. Is your "trillion" a 1 followed by 12 zeros, 9 zeros, or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)