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 suggests your working set is indeed smaller than ram. Good.
However be aware that the parallelise feature of mssql is much better that it was but is still moderately crap. Disable it and see if you get a 16X slowdown. My guess is you won't, which means when enabled you won't be getting a 16X speedup (also hyperthreading can actually slow things down - disbale it and see what happens). Edit: I'd not be surprised if you were only getting 4X to 5X speedup (I may be wrong but I do have experience in this).
> With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second.
(giggle) Oh, I don't think so!
> I've seen "sort spills" reach 600K IOPS on my laptop.
Then you're hitting IO and yer cores are useless. It spills to disk because it's overflowed ram - you may have a problem.
> Learn to utilise it better and you'll be better at thinking.
... agreed, but the reverse is true also.
edit 2: Depending on what you're looking for, and if you do it often, check if MSSQL's free-text indexing can index it, if so you may get a far bigger speedup from that than anything else. Maybe. Depending.
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 PCIe gen 4 adapter card with 4 NVMe M.2 ports on it. This would -- in theory -- be able to pump out 28 GB/s of reads, equivalent to a 200 Gbps NIC. Total storage would be 32 TB with CoTS 8TB drives.
That's a mind-blowing amount of IO in a single box!
The hardware has the capability, it's the software and inability to utilise that is the bottleneck now..
SSDs will have higher latency than ram, and I seriously wonder if they actually can hit 7GB/sec, or only theoretical. Ram is faster, always, in latency and bandwidth.
> 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!
> It then parallelises this across all cores/hyperthreads, so I get 100% utilisation on 16 cores.
that suggests your working set is indeed smaller than ram. Good.
However be aware that the parallelise feature of mssql is much better that it was but is still moderately crap. Disable it and see if you get a 16X slowdown. My guess is you won't, which means when enabled you won't be getting a 16X speedup (also hyperthreading can actually slow things down - disbale it and see what happens). Edit: I'd not be surprised if you were only getting 4X to 5X speedup (I may be wrong but I do have experience in this).
> With 8 cores going at once, this is 64 rows per clock, or up to 234 billion rows processed per second.
(giggle) Oh, I don't think so!
> I've seen "sort spills" reach 600K IOPS on my laptop.
Then you're hitting IO and yer cores are useless. It spills to disk because it's overflowed ram - you may have a problem.
> Learn to utilise it better and you'll be better at thinking.
... agreed, but the reverse is true also.
edit 2: Depending on what you're looking for, and if you do it often, check if MSSQL's free-text indexing can index it, if so you may get a far bigger speedup from that than anything else. Maybe. Depending.