Dealing with super huge table which is partially partitioned.

Question

Dealing with super huge table which is partially partitioned.

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

I'm curious... has anyone checked to see if any of that 17TB can be archived and moved out of the main table?

--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: 1004329 More actions · Answer 2

Crud... forum software lost my page on page 2... "bumping" that page with this post to get it to show up.

--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: 1004329 More actions · Answer 3

Also, what would you folks partition the table on? The reason I ask that is that I DO have a partitioned table. It's only about 2TB with 10 years of data but... the reason I partitioned it is because the data is basically a "WORM" table. It's a history table where there are never any updates. I've set it up so that each monthly partition lives in it's own file group and there's only one file in the file group. About 10 hours after the new month starts, I "repack" that previous month and set it to "Read Only" and take a final backup of the filegroup. I only have to actively backup the current month for DR recovery and I can do a "piece meal" restore of 1 of the file groups if something goes haywire with one or even a couple of file groups while everything else is still online. That also means that I don't ever have to worry about even looking at the files for index maintenance (if I actually ever did any... I usually don't but that's a whole 'nuther story) or stats rebuilds.

And, not to band-wagon you too much, I had a developer that worked on code that played against the table insist that his code would run better and faster if I'd take the time to partition the table. It's been partitioned for 8 years before he asked. 😀

Needless to say, I strongly agree with Grant... only "crap code" that does a table scan will likely benefit from partitioning but... it's not actually the partitioning that does it in most cases. In most cases, it's the fact that people finally put the correct index on the table to support the partitioning and then changed the code to take advantage of supposed partition elimination. Adding that correct index and changing the code in an identical fashion will usually work even better than partition will.

So... it's a 17TB table and partitioning can help a whole lot with maintenance, backups, and DR recovery. I just wouldn't get my hopes up about it fixing any performance issues and it stands a very good chance of slowing the code down a little bit more. Partitioning is not a panacea for performance. I know that for sure because I found out the hard way. 😉

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

As you noticed, something went wrong somewhere in 2011.

Did someone forget to:

automate for new partitions?
removal of old data?

Anyway, Whenever I implement a partitioned table, I implement boundary constraints, also for the "last" partition !!

That way, inserts will not succeed if these boundary constraints are no longer met! ( and yes, that is a production risk ! )

Partitions are declared a couple of cycles up front, so whenever a slinding window job fails, the DBA have enough time to get that fixed.

Of course, sliding window and monitoring are automated!

Another helpful thing is the Truncate command has been enhanced for partitions ( SQL 2016 +) !

TRUNCATE TABLE [dbo].[PartitionedPayload] with ( partitions ( 11 ) );

That may come in quiet handy, if you can offload the data ( part of the sliding window technique !! ) ( as off 2011 ) , modify your partition schema to act as it was supposed to and then load your data again into the table. (load the most recent data first (partition wise), so your consumers can use that table asap).

Double check for non-aligned indexes up front !

Load per partition.

Downtime should be requested for, especially with the given size of data.

Prepare the physical files large enough for this operation, so you don't have to rely on runtime extensions. (even with IFI I would do so)

I cannot stress enough, partitionig is an advanced topic !

you really need to test this all up front so you understand what is going on ( or wrong ), why, and how to fix it

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me