cboneill181 - Monday, January 23, 2017 10:31 AM
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.