Joe Obbish reaches the same conclusions in his thoroughly documented Stackexchange treatment. Check it out - I usually like his stuff more than mine :-)
Why does CCI creation on an empty table request a huge memory grant?
Requesting the actual plan for the CREATE, I find this in the plan XML. 1197064 as the SerialDesiredMemory, the RequiredMemory, the RequestedMemory.
That's a big grant for something that operates on 0 rows. When creating many CCIs concurrently, that's a problem - queries can timeout.
Might have to use Resource Governor creatively to allow high concurrency CCI creation.
Grant request is smaller if only one column in the CCI. So I guess could create a smaller CCI and add columns. That's also kinda painful. I'll ask on #sqlhelp, see if anyone has something clever...
So far the only things to come up on Twitter were the min_grant_percent and max_grant_percent query hints introduced in SQL Server 2012 SP3. But...
"Note These two new query memory grant options aren't available for index creation or rebuild."
Later I found this while looking around.
"Starting with SQL Server 2016, you can create the table as a clustered columnstore index. It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index."
Hmmm. Here's a great post from Pedro Lopes (Twitter @sqlpto), which includes some details for using Extended Events in memory grant investigation.
So far, it looks like creating the clustered columnstore inline with table creation, as below, avoids a memory grant. (That makes sense.) Whereas creating a clustered columnstore index on an empty rowstore table requires a memory grant - at the same size as up to 1 million rows for the table! (That's a surprise.)
If you'll be creating CCIs at fairly high concurrency, you may also want to consider creating the CCI inline with table create, as below, in order to avoid a potential bottleneck in memory grants.