[orcadk]

I don't agree that most secondary indexes are high cardinality. Some definitely are, but there are plenty of indexes with low cardinality, simply used for quicker scanning of those relevant values.

First up, in his example we're talking about 10k rows with a size of 35 bytes. At 8050 bytes of available space per page, that gives a fan-out of 8050/35 = 230. 10k/230*8kb = 350kb. At 350kb of inserts (hobt data, he doesn't go into secondary indexes), memory is completely irrelevant - the only force going on here is latency on writing the log to disk.

If we had a huge data set (as in, did not fit in memory, at all) with high cardinality - sure, we'd have a lot of cold leaf level pages. With no further info on his case, I can only assume most of the hobt and secondary indexes will fit in memory. At worst we'll have to read a cold leaf level page into memory to perform the addition in-memory.

As is there's no mention of even a clustered index, causing all of this to be heap inserts which is arguably one of the fastest insert methods there are (barring certain very special cases).

[leif]

I said most secondary indexes are high entropy.

If you have a large enough data set that you have to do a leaf read per insert (or close to that often), it will kill your throughput.