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.
