SSIS How to Load CCI Table Efficiently

Introduction

Lately I came across a topic to load data directly into Clustered Columnstore Index (CCI) via SSIS package. It was not working really well for the first time, so I needed to search around and test different SSIS parameters. This article does not discover anything new and is just a short summary for me when I will need this knowledge in the future.

Solution

It is important to set following SSIS OLEDB destination parameters:

092816_1034_SSISHowtoLo1.png
Figure 1 – SSIS Destination Parameters (CCI)

  • Table Lock – Do not use it for CCI as it will block possible parallel inserts.
  • Rows Per batch1048576 is the size of delta store so data isn’t really inserted into delta-store and then compressed but compressed directly.
  • Maximum insert commit size always use 0! – Bit confusing as I would set it to 1048576, but 0 means to deliver data in a single batch, allowing to reach the maximum allowed number of rows per Row Group (2).

Other than that it’s good to check and set DefaultBufferMaxRows and DefaultBufferSize property of the package.
092816_1034_SSISHowtoLo2.png
Figure 2 – SSIS Package Buffer Settings

DefaultMaxBufferSize can be useful when your rows in one batch cannot fit into SSIS package memory.

Conclusion

Loading data into CCI is not definitely fastest. (Although there are improvements in SQL 2016 regarding parallelism.) Table with row heap is always better then CCI destination regarding bulk insert performance. CCI load can be significantly improved trough using right SSIS parameters and parallelizing load procedure (1).

Resources

1 – https://blogs.msdn.microsoft.com/sqlcat/2015/03/11/data-loading-performance-considerations-with-clustered-columnstore-indexes/

2 – http://www.nikoport.com/2015/04/19/clustered-columnstore-indexes-part-51-ssis-dataflow-max-buffer-memory/

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed