Post reply

SSIS raw file delivery slow sometimes

  • January 28, 2009 at 7:05 am

    #129838

    We have an SSIS package data flow task currently using default buffer size settings that delivers data to a raw file after a series of lookups, transformations, and a sort. The data flow handles about a million rows of data from the source.

    Half the time this data flow runs, the task is able to deliver the default 10K record batches to the raw file in a matter of seconds; the other half of the time, it takes as much as 6 or 7 minutes to deliver a 10K batch to the file.

    Has anyone else seen this behavior or have any thoughts as to what might be causing it? We have looked at the virus scanning on the machine and have toyed with the settings of the buffer and the size of the batches, but we can't identify an obvious culprit. It does appear that on some nights the server has to page out the buffers, and on other nights it doesn't need to do this.

    I really don't expect a definitive answer to my problem -- I am really just interested in some ideas to investigate so that we can get some kind of consistent behavior out of this flow (preferably the delivery that happens in seconds rather than in minutes!).

    Thanks in advance,

    Jim Duncan

  • January 28, 2009 at 1:24 pm

    #934121

    jlduncan935 (1/28/2009)

    We have an SSIS package data flow task currently using default buffer size settings that delivers data to a raw file after a series of lookups, transformations, and a sort. The data flow handles about a million rows of data from the source.

    Half the time this data flow runs, the task is able to deliver the default 10K record batches to the raw file in a matter of seconds; the other half of the time, it takes as much as 6 or 7 minutes to deliver a 10K batch to the file.

    Has anyone else seen this behavior or have any thoughts as to what might be causing it? We have looked at the virus scanning on the machine and have toyed with the settings of the buffer and the size of the batches, but we can't identify an obvious culprit. It does appear that on some nights the server has to page out the buffers, and on other nights it doesn't need to do this.

    I really don't expect a definitive answer to my problem -- I am really just interested in some ideas to investigate so that we can get some kind of consistent behavior out of this flow (preferably the delivery that happens in seconds rather than in minutes!).

    Thanks in advance,

    Jim Duncan

    Jim,

    When you say raw file, do you mean Flat File Destination or it is Raw File Destination Component?

    Regarding the performance issue, have you checked your package execution log to see if you can come up with some idea where it spends the most time? Also check the Windows Performance Monitor. SQL Server and Integration Services makes available many counters thru it and this is the standard way to investigate/resolve performance issues.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply