Thread by @sqL_handLe

nanannaa nnaanaa nan na na na...
be the #sqlserver query workspace memory grant...
be the memory grant...
be the memory...

memory grants for bulk inserts into CCIs are tricky, especially if the query has significant and highly variable memory-consuming operators (hashes, sorts, etc) that are not involved in the compression.

"regular" memory-consuming operators - sorts, hashes and the like - can spill to temp if they need more memory than allowed by the grant.
rowgroup compression for a bulk insert can't spill to tempdb.

that's why memory conditions can cause DOP of a parallel CCI bulk insert to be downgraded.

that's why memory conditions - including a memory grant timeout - can cause a CCI bulk insert to be downgraded to a trickle insert into delta stores.

that's why scaling parallel bulk CCI inserts for concurrency can be really, really doggone hard.

there's at least two ways that CCI parallel bulk insert works differently than other plan operators wrt workspace memory that make planning for them quite tricky.

take note: scaling #sqlserver DOP 1 CCI bulk inserts is much, much easier than parallel CCI bulk inserts. Far easier to predict and plan. Far easier to investigate when something unexpected happens. Far easier to intervene.

But @gearheadDBA is a plan-ahead kinda guy, and i think he was so forward-thinking he may have already concocted a way to accomodate the current roadblock...