• Mohammed Mohsin-392707 (11/11/2012)


    Hi All,

    We have bulk load jobs configured using SSIS packages. All the load jobs were working perfectly fine until 2 days back. Suddenly, our load jobs are taking way longer than the normal time. The issue is identified to be the use of UDF as shown below,

    Note: These jobs were working fine since 3 years without any issues. We load around 16 GB of data each day.

    No changes to the server and\or SQL Server itself.

    The function is a scalar UDF which is a performance problem in and of itself. That, notwithstanding, things don't just suddenly start running slower unless something actually did change. Perhaps the target table of your load finally reached a tipping point where the existing statistics are no longer valid causing a change for the worse in the execution plan. Perhaps the server is using more memory for other things than it used to.

    I recommend that you first rework the scalar UDF and the code or process that uses it to be an Inline Table Valued function that returns a scalar value. That will take care of some of the performance problem. Once that is done, I'd re-evaluate any slownness that remains.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)