Partitioned Heap

Question

Partitioned Heap

Jeff Moden

SSC Guru

Points: 1004638
More actions
May 13, 2014 at 12:47 am

#291315

I've not even considered partitioning something like a temporal audit table (INSERTs only, never any updates or deletes except for SWITCH outs by dated partition) as a heap. If you have any experience with partitioned heaps (audit table or not), I'd sure like to know your opinion of them as well as any advantages/disadvantages you may have come across when compared to a partitioned clustered table of the same ilk.
Thanks for any info because there doesn't seem to be much on the subject of partitioned heaps on yabingooglehoo. 🙂
--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)

Viewing 3 posts - 1 through 3 (of 3 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I haven't used partitioning with heaps, but with temporal audit tables, I'd tend to make them clustered on the datetime field anyway and this would then become the natural partitioning key.

Doesn't really add any appreciable overhead to insert performance (as the inserts are all in ascending key order) and when you do need to select from the audit table, range scans on the clustered index are generally really helpful.

With a partitioned heap, your partitioning key is acting a bit like a non-clustered index on the partition ID, so it'll add a little insert overhead, switching out partitions should be efficient enough, but there'd be more select overhead than with a clustered index. Having said that, it would presumably be at least as performant as a non-partitioned heap would have been in the absence of other indexes.

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

Thanks for the reply, Howard. That's pretty much my opinion, as well, and I've also constructed my temporal audit tables in the same manner that you have. It just seems to make sense to do it that way especially to support SWITCH outs of the old data if they ever decide that we can drop data older than 2 years (they won't let me do that but it's a nice thought :-D).

I also agree that SELECTs might suffer a bit but owing to partitioning on the temporal column and the fact that the heap would act kind of like a clustered index with a temporal backbone (date with an IDENTITY tie breaker) that would never actually become fragmented in either case, I thought that INSERTs would benefit from the reduced overhead without affected SELECT performance much (some form of rowid lookup would take place with both except for covering indexes which would be a huge duplication of data on an already large table).

I guess I have some experiments to do.

Thanks again for your time, Howard.

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