Table Partition in Sql Server

Question

Table Partition in Sql Server

dastagiri16

Hall of Fame

Points: 3544
More actions
July 21, 2020 at 5:29 am

#3771637

Hi Team,
we have a table like 1.5 tb in size and around 800 cr records.
I would like to make it as partitioned table.
to make partition we need to drop and recreate primary and FK constraints will take more time. we need to perform this on more tables.
so is there any approach to minimize the downtime window to complete this activity quickly.please advice

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Not really. You have to rebuild the structures. This means data movement. The more data you have to move, the longer it will take.

One possibility would be to snapshot a moment in time, say with a backup. Migrate all that data into the new structure, with temporary table names. Then, after you get it done, migrate the changed/inserted/deleted data from the existing tables. When that's done, change the table names. That's probably your best bet. You won't be able to use log shipping or anything like that because you're restructuring the tables.

Of course, one question, WHY are you partitioning the data? Is it because you're having a hard time managing it and need a sliding window or similar behavior? Or, do you think partitioning will improve performance through partition elimination? If it's that second thing, stop. For most people, partition elimination is hard. They don't have the structures, or worse yet, the queries, that can support good partition elimination. If you find that your queries today are doing lots of scans, then your queries after you partition the data will be even worse. Unless you're going after partitioning because of data management, I really don't recommend it. It's a poor tool for performance enhancement.

"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

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

Strong second to Grant's post.

I'll also add that partitioning a table can actually make performance worse like it did for me but was acceptable because of what I was trying to accomplish (setup Read_Only partitions for legacy months that would never change to seriously reduce backups and index maintenance).

Yes, it's truly an "It Depends" but if you're trying to use partitioning to make your code run faster, "It Depends" means did you do everything exactly correctly and do you have an extremely limited number of queries that can actually take advantage of the partitioning where it will actually make a difference. The reason why I didn't put a question mark after last thing is because the answer is generally "No" and the reason is "fixing the code and related indees will generally result in much better performance on non-partitioned tables".

I've also found that a lot of people have supposedly proven to themselves that partitioning did actually help performance. It turned out that the actions they took with their code and fixing their indexes was what actually provided the performance increases and, when they reverted back to a monolithic structure but keeping all the improvements they made, their code ran faster still.

--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)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 3

Thanks both of u in details explanation .. the only concern is to improve performance.

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

Then, ever so strongly, I recommend you pursue other approaches. Partitioning almost never succeeds in improving performance. By almost never, I mean, probably, a 99% failure rate. It's purpose is data management. Not performance enhancement. Explore other options. Traditional query tuning. Data cleansing. Secondary storage. Filtered indexes. Filtered statistics. Nested procedures to allow for different query plans for different behaviors on the tables. Elimination of common code smells. Use of better code structures, in and out of the database. Columnstore indexes for analytics. Enhanced statistics management.

Not knowing what your specific pain points are, other than large data volumes, it's hard to provide more than general guidance. However, any, or probably all, of these things, and others, will help you more than partitioning.

"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

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 5

Thanks for your help..sure definitely i will look forward other options

tmisganaw Old Hand Points: 314 More actions · Answer 6

Take a look at Columnstore indexes (https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15)