3

Maybe is stupid question, but I must ask to be clear with that.

I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks.

What I need to know what I can get if I place partitions on same disk?

3

It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here

You can break your backups by partition. This can be useful if you are struggling to complete your backups in time. See here for details

Index rebuilds can be done at the partition level instead of the whole table.

Large inserts can be done with partition switching.

Queries can also run parallel across partitions.

  • +1 Just note you also get more parallel SGAM threads when you partition so you get a bit more performance there, although I'd imagine in the real world it's minimal. – Ali Razeghi Mar 27 '15 at 19:49
0

James Anderson got most of it. I just want to add one more.

Purging is a huge reason we are working on Partitioning.

If you indexes are aligned, then you can do partition switching. For us that means that we can take older partitions and just switch them out to different table and then drop that table.

The actual switch allows me to remove over a million rows in less than a millisecond.

There are trade offs (some significant ones), but deleting data for large transaction systems is sometimes a very hard story.

My company has a SAN and the System Admins will not even hear of allowing storage anywhere else.

Despite the fact that my partitions will be on the same disk, I still am currently working in implementing it just for the data purging story alone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.