Use same partition scheme across multiple tables

Question

Use same partition scheme across multiple tables

senthil kumar d

Old Hand

Points: 395
More actions
August 13, 2013 at 11:50 pm

#297702

Hi All
We have some really large tables(100s of GB) in our Datawarehouse and have decided to partition the tables based on the ID column. Is it sensible to use the same partition scheme across all the tables. If so will it cause any issues when we try to merge/split partitions in the live environment? Else is it safe to just use unique partition schemes for each of the tables?

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

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Both are "safe" approaches in that a given partition scheme can be applied to different tables. You just create a function (from the Books Online):

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

Notice, there's no definition in that function for particular columns, column names, tables or table names. So yeah, you can create a function and then apply it across different tables. Each table is going to be partitioned indepedently from the others, even if they're using the same function. Conversely, for more control, you can create a unique function for each table. It really depends on what you're partitioning and why.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

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

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

senthil kumar d Old Hand Points: 395 More actions · Answer 3

Thanks for the quick response Grant.

I was thinking in those lines till I thought about splitting/merging these ranges. Say for instance I create partition ranges of 10 million intervals and partition several tables based on the ID column and use a single partition function and scheme across the tables. Later when I want to merge/split these ranges will it involve a lock on all the tables involved. In that scenario will it affect the performance of the server in the live environment. That is what I am more concerned about.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Rearranging partitions is a completely different question than sharing a partitioning function. And yeah, changing the partition will cause all sorts of interesting blocking and locking, but that's to be expected.

I'd suggest, based on this follow-up, you need to answer Gail's question. Why are you partitioning? What particular problem are you attempting to solve.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

senthil kumar d (8/14/2013)
Later when I want to merge/split these ranges will it involve a lock on all the tables involved.

Yes. Splitting/merging partitions requires schema modification locks, that's essentially exclusive access to the table.

But why would you be regularly splitting/merging partitions? Implementing sliding window on multiple tables? If so, you'll be doing the split/merge in part of a maintenance window.

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

senthil kumar d Old Hand Points: 395 More actions · Answer 6

Partition maintenance we will not be doing frequently and definitely would require a maintenance window as you suggest.

Purpose for partitioning:

1. Inspite that some of the tables are huge(billions of rows) we use only the latest few million for transformation and loading. Partitioning these tables and using partition elimination to select data out of the tables will be faster and efficient.

2. Some times we retransform/reload the entire table and this takes up a lot of CPU and resources. So partitioning and building a logic to have parallel threads in case of these scenarios will reduce the load on the server and also make the process quicker.

These are just my understanding and could be incorrect!!

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

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

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

senthil kumar d Old Hand Points: 395 More actions · Answer 8

Thanks Gila for the link. I remember reading that post sometime back and Thanks for helping me refresh my memory on that.

I had some inputs from my DBA. We are partitioning the tables, mainly to help our maintenance tasks. – Partitioning the tables will lower the time for maintenance, which will free up system resources for the DW workload. Our tables are not 100’s of GB’s. We have indexes which are using up to ~1,8TB. Rebuilding these indexes are a nightmare in production environment and also having have 1,8TB of free disk space in the filegroup is extremely difficult.

Once we partition the tables the index maintenance on these tables can run only check and cleanup the latest partition for fragmentation. This should lower the index check step by factor many. Then we can of course have a monthly or less frequent check for all the other partitions.