Nonclustered Columnstore Index on Base Table Partitioned by Computed Column

Published Mar 23 2019 01:37 PM 152 Views
Microsoft
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.

%3CLINGO-SUB%20id%3D%22lingo-sub-384392%22%20slang%3D%22en-US%22%3ENonclustered%20Columnstore%20Index%20on%20Base%20Table%20Partitioned%20by%20Computed%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384392%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20May%2021%2C%202015%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIf%20you%20have%20SQL%20Server%202012%20and%20created%20a%20NCCI%20on%20a%20base%20table%20partitioned%20by%20a%20computed%20column%2C%20you%20might%20have%20noticed%20that%20it%20produces%20a%20serial%20plan%20instead%20of%20a%20parallel%20plan.%20This%2C%20naturally%2C%20slows%20down%20index%20creation.%20How%20slow%20depends%20on%20your%20database%20size%20and%20number%20of%20partitions.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EYou%20can%20use%20sys.dm_exec_query_memory_grants%20DMV%20to%20find%20out%20the%20degree%20of%20parallelism%20(dop)%20as%20well%20as%20required%20memory.%20Example%20output%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Esession_id%20request_id%26nbsp%3B%20scheduler_id%26nbsp%3B%20dop%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20requested_memory_kb%26nbsp%3B%20granted_memory_kb%26nbsp%3B%26nbsp%3B%26nbsp%3B%20required_memory_kb%26nbsp%3B%26nbsp%3B%20used_memory_kb%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20max_used_memory_kb%26nbsp%3B%26nbsp%3B%20query_cost%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20group_id%26nbsp%3B%26nbsp%3B%26nbsp%3B%20pool_id%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20is_small%20ideal_memory_kb%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------%26nbsp%3B%26nbsp%3B-------------%26nbsp%3B%20------------------%26nbsp%3B------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B-------------------------------%26nbsp%3B%26nbsp%3B%20--------------------------%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20----------------------------%26nbsp%3B%26nbsp%3B%26nbsp%3B-----------------------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B-------------------------------%26nbsp%3B%26nbsp%3B%26nbsp%3B----------------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-----------%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-----------%26nbsp%3B%26nbsp%3B%20%26nbsp%3B-----------%26nbsp%3B-----------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E54%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2012%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20175952%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20187144%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20175952%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20170760%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20187144%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B4814965.07430016%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20175952%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThough%20you%20are%20able%20to%20create%20an%20NCCI%20on%20a%20table%20partitioned%20by%20computed%20column%20in%20SQL%20Server%202012%2C%20we%20recommend%20customers%20to%20not%20use%20NCCI%20on%20tables%20partitioned%20on%20a%20computed%20column.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20SQL%20Server%202014%2C%20if%20you%20try%20to%20create%20a%20nonclustered%20columnstore%20index%20(NCCI)%20on%20base%20tables%20partitioned%20by%20a%20computed%20column%2C%20you%20will%20get%20the%20following%20error%20message%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20Internal%20Query%20Processor%20Error%3A%20The%20query%20processor%20could%20not%20produce%20a%20query%20plan.%20For%20more%20information%2C%20contact%20Customer%20Support%20Services.%20%3C%2FEM%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384392%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2021%2C%202015%20If%20you%20have%20SQL%20Server%202012%20and%20created%20a%20NCCI%20on%20a%20base%20table%20partitioned%20by%20a%20computed%20column%2C%20you%20might%20have%20noticed%20that%20it%20produces%20a%20serial%20plan%20instead%20of%20a%20parallel%20plan.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 01:37 PM
Updated by: