SQL Server Performance

DTS fetch buffer size

Discussion in 'SQL Server DTS-Related Questions' started by ramasubbup, Jan 12, 2006.

  1. ramasubbup New Member


    I have a DTS data transfer task. In the option tab I see the fetch buffer size as 9999.

    BOL says that it is the number of rows to fetch from Source.

    I have to fetch around 45M of records which will take around 75 minutes.
    Is it true that we cannot fetch more than 9999 rows, if I have very good
    Server configuration?

    Any other ways?

    Thanks for your help

  2. satya Moderator

    http://www.sql-server-performance.com/dts.asp fyi.

    It depends on the memory available and processor defined on the SQL server, the default is 0, which means a single batch so if you have 10 million rows they will not be committed until the end.

    Satya SKJ
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ramasubbup New Member

    Thanks Satya.

    What you said is true wrt to batch size.

    I am discussing about the 'fetch buffer size' option and not 'batch size' option

    It BOL says that, it refers to the number of rows to be fetched at source.
    I am not able to increase it to more than 9999.

Share This Page