Nonclustered Columnstore Index on Base Table Partitioned by Computed Column

First published on MSDN on May 21, 2015

If you have SQL Server 2012 and created a NCCI on a base table partitioned by a computed column, you might have noticed that it produces a serial plan instead of a parallel plan. This, naturally, slows down index creation. How slow depends on your database size and number of partitions.

You can use sys.dm_exec_query_memory_grants DMV to find out the degree of parallelism (dop) as well as required memory. Example output:

session_id request_id  scheduler_id  dop     requested_memory_kb  granted_memory_kb    required_memory_kb   used_memory_kb       max_used_memory_kb   query_cost              group_id    pool_id     is_small ideal_memory_kb

--------------  -------------  ------------------ ------    -------------------------------   --------------------------     ----------------------------   -----------------------        -------------------------------   ----------------             -----------    -----------    ----------- -----------------------

54              0                 12                  1         175952                          187144                        175952                           170760                       187144                             4814965.07430016     2             2                0            175952

Though you are able to create an NCCI on a table partitioned by computed column in SQL Server 2012, we recommend customers to not use NCCI on tables partitioned on a computed column.

In SQL Server 2014, if you try to create a nonclustered columnstore index (NCCI) on base tables partitioned by a computed column, you will get the following error message:

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.