none
Measuring the memory usage of DataFlow Task

    Dotaz

  • I'm using MS2017RTM. My server has 16GB RAM, SQLServer is using 8GB and the rest is left for SSIS.

    I've a package which reads 10GB table, sorts, counts the rows and inserts the countered rows into a table. I know the sort is unnecessary, but we still have it.

    During the package execution, I observed the following

    Buffers in use:195
    Buffer memory : 3932375040 (~3.6GB)

    Following says :https://docs.microsoft.com/en-us/sql/integration-services/data-flow/data-flow-performance-features?view=sql-server-2017

     The default buffer size is 10 megabytes, with a maximum buffer size of 2^31-1 bytes.

    That is max should be roughly ~2GB.

    In my case it went beyond 2GB, why is that? I have only one package running and only one instance of this package is running.

    7. června 2018 19:21

Všechny reakce

  • Hi bolapati,

    The Buffer memory indicates that the amount of memory that is in use. This may include both physical and virtual memory. 

    The Buffers in use indicates the number of buffer objects, of all types, that all data flow components and the data flow engine is currently in using.

    So, in your scenario, the avg of each buffer size is 3.6GB/195=18.5 Mb.

    And because you are using SSIS 2017, to improve the performance, you can set AutoBufferSize to True.

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

    If you have any questions, please feel free to ask.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    8. června 2018 2:34
  • Thank you for the reply!

    Is there any way that I can limit the number of buffers a task can create?

    8. června 2018 18:30
  • Hi bolapati,

    Just increase your buffer size, so the number of buffers should reduce.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    14. června 2018 9:03