Parallel Loading Columnstore Indexes

Sunil Agarwal shows how to bulk load with parallelism into a clustered columnstore index from a staging table:

SQL Server 2016 requires following conditions to be met for parallel insert on CCI

  • Must specify TABLOCK
  • No NCI on the clustered columnstore index
  • No identity column
  • Database compatibility is set to 130

While these restrictions are enforced in SQL Server 2016 but they represent important scenarios. We are looking into relaxing these in subsequent releases. Another interesting point is that you can also load into ‘rowstore HEAP’ in parallel as well.

The restriction I’d most like to see reduced would be the “no non-clustered indexes” part.  The rest seem forgivable for most clustered columnstore setups (i.e., fact tables).

Related Posts

Columnstore Functionality Per Edition

Niko Neugebauer looks at how columnstore indexes differ between SQL Server Standard Edition, Express Edition, and Enterprise Edition: One rather small (relatively other features, as I imagine), but an incredibly useful improvement was described in Columnstore Indexes – part 109 (“Trivial Plans in SQL Server 2017”) – is the ability to automatically produce Fully Optimised execution plans […]

Read More

Columnstore Indexes And Partition Operations

Niko Neugebauer continues his columnstore index series, this time looking at how partitioned tables behave: Let’s start with a simple test of merging the 2007 partition with the year 2008, by issuing the following command: 1 2 ALTER PARTITION FUNCTION pfOnlineSalesDate ()   MERGE RANGE (‘2008-01-01’); It might ready you a reasonably huge surprise, but this […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031