Earlier quoted context omitted.
I've not used columnstore in MSSQL but in fact I likely know more about the innards of MSSQL than you. WRT memory, all I mant was that if your working set doesn't fit in RAM, you're screwed regardless. Nothing will save you, so check that first. I don't know what your 70GB means in this context - please check. > It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores. that sugge…
I do know a thing or two about MS SQL optimisation also. 8) So keep in mind that the SSD I'm referring to can read at 7 GB/s. Not seven gigabits... 7 gigabytes per second. I've benchmarked some not-so-old servers (the entire box, not a VM!) as capable of only 35 GB/s streaming reads from main memory! So these days "disk" is more like "5x slower memory". It used to be 100-1000x slower. I told one customer to get a 16x…
> I've benchmarked some not-so-old servers (the entire box, not a VM!) as capable of only 35 GB/s streaming reads from main memory!
Then you have either a low-end server or something is very wrong (I think you benchmarked wrong, see below). You should be getting maybe 15 GB/sec per channel, so 4 channel = 60GB/sec, for a decent server you may well have 6 or 8 channels. (figures may be a bit off but 35 is puzzling me).
You may have measured on a single core, but each core has its own mem reading stuff so it should multiply up if your benchmark uses extra cores.
Anyway, I have to get back to work on my dual-core haswell. Good luck!
(Edit: 15 * 4 = 60, not 80, corrected)