@TomV @JamesLupolt Added an answer. Thanks for the pointer.

@PaulWhite thanks, as always highly informative.

@PaulWhite Thanks, that's a great answer and touched on a number of things I didn't know about. Yes, I'm reconsidering the 15k partitions. I don't need nearly that many for how I'm currently using that table.

@JamesLupolt Oh one thing I forgot to mention. It's not important to the answer, but to avoid running out of memory when creating the clustered and clustered column store indexes in the setup script, it helps a LOT to create the heap as partitioned up front.

The compile time for e.g. the single row inserts is just embarrassing.

Yikes. I OOMed your machine.

Do you remember how much compile time you were seeing and on what version?

@JamesLupolt 175ms and 30624KB compile memory 2014 SP1 CU3.

That's painful.

For a one row insert it is, yes :)

Well definitely time to re-evaluate how I'm using partitioning then.

It shouldn't be so bad. It's embarrassing for the QO team. There's no way a TRIVIAL plan should need ~250ms of compilation time.

Running the 5 single-row inserts into the heap (partitioned) runs for 22s on my laptop.

I do need a laptop that can accommodate more than 8GB one day.

Though 8GB ought to be enough to create a clustered index on a 327,680 row table :)

Yikes. Yep I'm getting the same timings if I partition the heap first.

It's definitely related to the partition count. I get a total of 13 ms with 5 of that on compile time if I reduce the partition count to 6.

@PaulWhite 8GB ought to be enough for anybody

On 2016 I still get 175 ms of compile time, but the inserts take 0 ms.

@JamesLupolt Yes big numbers were always a problem hence the original 1000 limit.

@TomV Ha, yes. Today's 640 KB.

best christmas gift ever, falke.com/de_de/…

I mean on 2016 I get 0 ms for the inserts even w/ 15k partitions. So probably some improvements in there

@PaulWhite I see. I actually didn't know that they'd be a problem even when empty.

@JamesLupolt Maybe. Sounds like it. I have a CTP 3.x Azure VM somewhere. I'll have a look at some point. Just not right now.

@PaulWhite I'm not yet forty but they absolutely rock

@TomV I'll bear them in mind for the Winter months. I usually buy a new pair each year - they seem reasonable value too.

for once i'm not being cynical

I have reset my cynicism detector accordingly.

Ooooh, life in here

Christmas/New Year interlude :)

dba.stackexchange.com/questions/124647/…

Needs a slap

it's been slow since weeks before christmas, I blame @billinkc for scaring everybody

@Phil had a slap I guess, page not found

Self-deleted.

Seems like a nice person. I'll miss him.

"please just leave" happens to me all the time

@TomV yeah, I felt like he knew me :-)

Now I'll never know why his surname is capitalized.

Don't say that - we'll end up with a YperSillyCUBE™ or something.

Well I'm headed off for a while. Thanks again for your answer @PaulWhite. It covers the intersection of a few different topics in some ways I hadn't know about before.

New Powershell pulled theregister.co.uk/2015/12/24/…

@TomV They make nice socks too. I wasn't aware they made combination shoe-socks.