Home Forums Data Warehousing Integration Services 15 seconds pending execution RE: 15 seconds pending execution

  • March 13, 2018 at 3:33 pm

    #1983056

    sgmunson - Tuesday, March 13, 2018 1:24 PM

    bmg002 - Thursday, March 8, 2018 10:21 AM

    sgmunson - Thursday, March 8, 2018 9:49 AM

    I'd start with having your network team analyze the traffic and see exactly what's going on.   While awaiting that response, I'd search online for any issues with the various KB articles that are part of all your updates.

    Thanks.  I'll shoot some stuff off to my IT guys and see what they can see.
    I did find some KB's but they were all about the certificate revocation list which takes roughly 15 seconds to time out so that was a big red flag, but they suggested blocking it at the firewall (which IT didn't want to do) or blocking it in the hosts file (which is what I ended up doing) but it made no difference.
    Normally I wouldn't be too concerned about a 15 second delay, but we have some SQL jobs that have 30+ steps in them that are all smaller SSIS packages for moving and transforming data.  That adds a lot of extra time onto a data load.

    Thanks for the tip though; I'll poke the IT guys to check out the network traffic and see what is happening.  If that doesn't help, I may need to look at increasing the complexity of our SSIS packages, but I don't like that solution.

    Certificate revocation list?  Sounds like something that you'd have to prevent from occurring at all, because I suspect that just blocking it doesn't do anything except keeping it from succeeding at all, and thus it times out.  Not sure if that's something you can configure, or it's some security feature that is effectively blocked by some existing firewall rule on protocol or odd port number or some such.

    yeah, from what I've read online on various places (including technet), they indicate adding 127.0.0.1 crl.microsoft.com to your hosts file should result in a quick-fail response to the site and give much better performance, but it seemed to make no difference on my server.  I checked the webpage in IE and it comes back rather promptly (with a 400 error mind you, but it is a fast response), so I am thinking my issues are unrelated to the CRL.  Blocking it in the hosts file seemed to make no difference.
    The other interesting thing I noticed is that a 1 step SQL job (the step just calls a simple SSIS package) runs slowly.  The job step completes in 20 seconds, the job itself completes in 33 seconds.  Prior to the updates, the job step would complete in 5 seconds tops.

    when I look at the sysjobactivity table, prior to the update, I see data like:
    Run_Reuqested_Date - 2018-03-02 20:05:00.000
    Start_execution_date - 2018-03-02 20:05:00.000
    Last_executed_step_date - 2018-03-02 20:05:00.000
    Stop_execution_date - 2018-03-02 20:05:05.000
    After the update, it looks more like:
    Run_requested_date - 2018-03-13 15:05:00.000
    Start_execution_date - 2018-03-13 15:05:00.000
    last_executed_step_date - 2018-03-13 15:05:04.000
    stop_execution_date - 2018-03-13 15:05:32.000

    If I go into the overview of that same SSIS package, I an see the duration of the individual package steps (using today's date) it shows the 3 steps (the whole package, the "clear existing data" step in the package and the "move data" step in the package) is only taking a little over 1 second to complete.  BUT the duration of the whole package is 16.067 seconds.  The duration of the job though is 32 seconds for the above example.
    I can't find anything useful in either the SQL logs or the windows logs either.  I did try doing some stuff with netstat, but it didn't tell me anything interesting.  I tried turning off the firewall on the server (short term) but it made no difference either.

    This one is confusing me as we didn't change the job at all.  Just windows updates (including SQL updates).  It did result in a .NET update... I wonder if that is being problematic...

    As for blocking the CRL, best I could find, it is not a configurable thing as it is baked into .NET.

    Forgot to mention - my IT department told me they see nothing bad happening network wise.  I imagine this is going to be something simple that I am just not thinking to check (like some service got switched from automatic to manual with the update), but I am just running out of ideas on what to look at or for.

    Last edit for this post - I tried running the 64-bit dtexec and it took 17.969 seconds to run the package.  The 32-bit dtexec took 20.453 seconds.  running it in visual studio takes 5 seconds but that is not a realistic scenario.

    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.