To build columnstore indexes as fast a possible, make sure to provide plenty of memory. See the Columnstore FAQs related to index build
here for a discussion of memory-related issues, including what to do if index build runs out of memory or can't get enough memory. To build a single columnstore segment, the system must load a million rows into memory in a row-based format, and then create
the columnstore representation of the segment. Each segment must be built by a single thread. The index build code reduces the degree of parallelism (DOP) if there is not enough memory.
If index build is slow because DOP was reduced automatically, consider the following options:
To get a feel for how much memory may be needed, consider the following example. A 1000-byte row size requires 1GB just to load a row-based copy of one segment into memory. 2 to 4 times this much memory, and in some cases more, may be required to build a
segment. So, if you have a 20 core machine and want to use DOP 20 to do an index build, it may take 20*4GB = 80GB or more, dedicated to this operation alone, to do it fully in parallel.
To determine the runtime DOP of a columnstore index build operation, use sys.dm_exec_query_memory_grants and look at the 'dop' column. If the DOP is lower than expected, make sure nothing else is limiting the DOP (like the
max degree of parallelism sp_configure setting), and then, if possible, provide more memory to SQL Server and try again.
Return to main SQL Server columnstore index tuning page.