• cboneill181 - Monday, January 23, 2017 10:31 AM

    cboneill181 - Monday, January 23, 2017 8:11 AM

    We are currently in the middle of upgrading our databases to SQL Server 2016.  Part of that is converting our SSIS projects to the project deployment model.  We just had a new SSIS server setup, but the packages seem to be running slow when ran from the catalog on this server.  It's a pretty powerful server, 64GB memory, SSD, 2.93 quad core processor.

    Each package is running 5-10 seconds slower on the server than when I run it on my workstation from Visual Studio.  If it was only a couple of packages it wouldn't be a big deal, but we have hundreds of packages running so it's adding a significant amount of time to the nightly job.  I know it's a little bit of a vague question, but I'm hoping to get a little direction on where to start troubleshooting this.

    It looks like one of the issues, and I'm not sure what's causing it, is each step of the job is taking much longer to complete than the packages take to run.  For example, if I look in the integration services catalog, it says in there a package may take a second to complete, but the step that ran that package in the job is taking 5 seconds.  Any ideas what may cause that?

    You know, I have never looked into this.  I decided to look right before putting a reply that was pointing at the server load, but then I realized that wasn't the case.  I see the same thing.  SSIS package runs in 8 seconds, the job runs in 11.
    My best guess for this is one or more of the following:
    1- SQL takes a little time to start the job step (although 3+ seconds seems high)
    2- SQL takes time to log the job information (this feels more likely to me.  I just changed my job to "none" for logging just to see how it affects runtime performance.  The job is non-critical so if it fails, we can just re-run it without investigating the failure too hard)
    3- logging
    4- compilation
    I have an expectation that SQL does a lot of logging for the execution which would all go into the transaction log.  Our job server is set to simple recovery as point in time recovery is not a big deal for the SSIS server as we have backups of all SSIS packages in git.
    When you run it from visual studio, I believe that all 4 of the above things are ignored because Visual studio only starts counting the runtime after compilation, it would not count the logging (job start, transaction begin, transaction end, job end and all of the bits inbetween), it would not need to open up the dtsx as it is already open and it likely wouldn't start counting until the job actually started running.
    On the SQL All Executions report, it is not counting the compilation time, the time it takes for the SQL Agent to get the job step data, the logging time, it is just the time from getting the start package to the package complete.

    Are the job execution times better or worse than when it was in the PACKAGE deployment model on the old server?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.