Post reply

SQL Server Agent decreasing package performance

  • November 2, 2011 at 6:37 pm

    #246174

    Hi,

    Does anyone have an idea why running SSIS packages through the SQL Server Agent dramatically increases the time it takes to run the package. I'm looking at changes from about 15 seconds to close to 8 minutes.

    We are using package configurations to identify the service account that needs to be used to run the packages and to identify the databases that need to be accessed and written to.

    I'm using SSIS to perform ETL from and to Oracle.

    Please let me know if you have any thoughts or suggestions.

    Thanks-

  • November 3, 2011 at 8:54 am

    #1402685

    I have not seen anything like what you are explaining. Agent isn't in control of the running of an SSIS package it just calls either the SSIS server or runs it directly with DTEXEC. Either way it is out of the way at that point.

    Have you opened the package in BIDS ON the server to see if it still happens there? Also how is memory configured on the SQL box, is there sufficient memory to run the package?

    CEWII

  • November 8, 2011 at 12:21 pm

    #1404767

    Definitely not a SQL Agent issue.

    One or two things are coming to mind that may cause this...the first is drivers (32/64 bit). BIDS is a 32-bit environment, and will use the 32-bit Oracle drivers. If the server is 64-bit though, it will use those drivers when executing through SQL Agent (depending on execution settings of course). You may want to check that. In theory the 64-bit Oracle drivers should perform better, but I have seen some interesting situations.

    The second is the pre-validation phase of your package. This may take some time if the "DelayValidation" property is not set to false on connections etc. Also something you may want to have a look at. Use the SSIS log to your advantage there...

    As mentioned, it could also be resource contention, memory etc.

    Edit: I should maybe have articulated the driver comment a little better. What I really meant to say was that in the scenario above (if you are working in a 64-bit environment), it could make a difference if the 64-bit driver was outdated.

    Hope this helps.

    Martin.

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

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