Performance impact of memory grants on data loads into Columnstore tables

Reviewed by: Dimitri Furman, Sanjay Mishra, Mike Weiner, Arvind Shyamsundar, Kun Cheng, Suresh Kandoth, John Hoang Background Some of the best practices when bulk inserting into a clustered Columnstore table are: Specifying a batch size close to 1048576 rows, or at least greater than 102400 rows, so that they land into compressed row groups directly…. Read more

SQL Server 2016 SSIS Data Flow Buffer Auto Sizing capability benefits data loading on Clustered Columnstore tables

  Reviewers: Joe Sack, Arvind Shyamsundar, Murshed Zaman, Sanjay Mishra  The focus of this blog is to introduce SSIS changes in the Data Flow task in SQL Server 2016 that help data loading into Columnstore tables. On SQL Server relational data warehouses, Columnstore indexes are being widely used due to the immense value realized by… Read more

SQL 2016: Columnstore row group Merge policy and index maintenance improvements

                A Columnstore index contains row groups that can that have a maximum of 1,048,576 rows. A row group can be closed and compressed due to multiple reasons before that maximum of 1,048,576 rows is reached. Ideally we want that row count in each row group to be as close to the maximum as… Read more

SQL 2014 Clustered Columnstore index rebuild and maintenance considerations

This article describes the index rebuild process as well as index maintenance for clustered Columnstore indexes and is directed towards SQL 2014. In SQL 2016, there have been several index maintenance enhancements that will be covered in a separate post.   Overview of Columnstore index build or rebuild process Building (or rebuilding) a Columnstore index… Read more

Data Loading performance considerations with Clustered Columnstore indexes

This article describes data loading strategies specific to tables with a Clustered Columnstore index on SQL Server 2014.  For fundamental data  loading strategies, an excellent read is the whitepaper Data Loading Performance Guide and it is greatly recommended. Though that whitepaper doesn’t include Columnstore indexes, many other concepts presented there still hold true for any data… Read more