Post reply

Slow performance while running DTS package

  • April 16, 2008 at 4:13 pm

    #121143

    Hi

    I have this dts(SQL Server2000) package that performs a series of steps. Recently I was experimenting with the performance if I add some unique keys.

    Now one of the steps(consists of a sp having update statements) refuses to complete. If I run the sp seperately it completes in about 15 min. But while running the DTS package its taking more than 6 hrs.

    I noticed that 'an spid is blocking itself' (not the case when the sp is run seperately) and also this is not for a brief period. It is the case for hours together.

    Please suggest how I might be able to go to the root of the problem?

    Any suggestion might help.

    Thanks in advance

  • April 16, 2008 at 5:23 pm

    #803382

    It could be that the task that executes the stored procedure is being blocked by another task in the package. Are you using workflow to control execution of successive steps? That is, does the task wait for the previous task to complete?

    Greg

  • April 18, 2008 at 11:02 am

    #804490

    Yes thats true.. if I understand correctly then you mean to say that the lock is there because the task is waiting for the previous task to complete. Ok.. I was a bit baffled by that.

    could somebody point me to some resources on the net on 'how to determine wats affecting the performance'?

  • April 23, 2008 at 11:22 am

    #806198

    Turns out that the persormance was affected because the db needs some tuning...

    Thanks

  • April 30, 2008 at 1:21 pm

    #809286

    Sometimes, it depends on where you execute the DTS package. From my experience, it's best to execute the DTS package on destination server where the write operation is performed. What I usually do is to log on the destination server, then run the package. Avoid running the package on your desktop when you're outside the network.

  • May 1, 2008 at 8:29 pm

    #809894

    Though I did execute the dts on the server itself... its a point that I did not know..

    will remember that

    Thanks a lot

Viewing 6 posts - 1 through 5 (of 5 total)

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