Why does performance degrad when inserting into columnstore based on guidance?

Sunil Mathew 0 Reputation points
2024-03-06T10:10:21.3966667+00:00

With reference to this article, https://video2.skills-academy.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance?view=sql-server-2016, it is suggested to have bulk load sizes (no of rows) between 102,400-1,048,576. Based on this, I did some testing by increasing the no of rows/batch from 100,000 to 250,000 in an SSIS package. Surprisingly, the package took 85 minutes to complete instead of 65 minutes for the same volume of data (50 million) before the change. Another execution took 60 minutes now instead of 25 minutes earlier for the same volume 20 million.

I'm using SQL Server 2016 and SSIS 2016, both on the same Azure VM.

The below properties were modified in the package - from 100,000 to 250,000.

defaultBufferMaxRows

FastLoadOptions - ROWS_PER_BATCH

FastLoadMaxInsertCommitSize

Not able to understand why there is a degradation in performance, while following the guidance. Why is this so? And/or any other suggestions to improve performance of such bulk loads via SSIS packages?

Note: there were no OnError, OnWarning or OnInformation messages regarding lack of buffer memory or virtual memory during the package execution. This is also verified in the Azure graphs that show minimum memory available at a minimum of 4.5 GB, in the period of this package execution.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,215 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,503 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 34,836 Reputation points
    2024-03-08T01:44:32.7766667+00:00

    Hi @Sunil Mathew,

    Check this article which talking about Methods to Improve ETL Performance Using SSIS.

    https://www.developer.com/database/top-10-methods-to-improve-etl-performance-using-ssis/

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments