none
should we help ssis with parallelism or let it make its own choices RRS feed

  • Question

  • Hi we run 2017 standard. I see this more and more often and wonder.

    Lets say that for a given pkg max parallel dft's is 7 either by default or explicit setting.  Not worrying about engine threads right now.   

    what I see people doing is layering containers such that no container runs more than 7 dfts in parallel.  And only after a container is done can the next container start running.

    I wonder if layering is really necessary given that ssis will do what it can when it can and therefore its overkill trying to help it decide.  Perhaps just putting 14 things in parallel without extra containers is fine since ssis knows already what it can and cant do. 

    Thursday, August 15, 2019 12:04 PM

Answers

  • You don't need to break it up to manually try micro-manage the SSIS threads.  SSIS is perfectly capable of doing it itself. And when you change hardware, you won't need to rework your SSIS packages.

    • Marked as answer by db042190 Friday, August 16, 2019 5:53 PM
    Friday, August 16, 2019 5:52 PM

All replies

  • Hi there,

    I do not see how 7 or any magic figure would make sense.

    It all depends on the payload, machine, etc.

    You can experiment and test. Oftentimes parallelism results in intermittent deadlocks, so be careful.

    By the way, since you are on SSIS 2017 you can take advantage of the Scale Out feature. Not sure if it is offered in Standard https://docs.microsoft.com/en-us/sql/integration-services/scale-out/integration-services-ssis-scale-out?view=sql-server-2017


    Arthur

    MyBlog


    Twitter

    Thursday, August 15, 2019 1:23 PM
    Moderator
  • thx Arthur , we don't have multiple computers to spread the wealth so i'm pretty sure scale out doesn't apply here yet.

    Either way I'm not sure I asked the question clearly.  Assuming we are limited to one pkg and staying in process and not yet in the cloud, are you saying outside of setting max concurrent executables etc, we shouldn't try to help ssis by having containers or paths that architecturally force it to attempt no more than our expected number of concurrent executables?

    For example, lets say we have 20 plants and we've decided that we want 20 similar dft's that in a perfect world would run in parallel.  And we expect 7 concurrent executables will usually be our max.

    What I've seen people do is create container 1 with  7 plant dfts and only after that container finishes let container 2 run with 7 more plants and so on.  What I've also seen is 7 cf "completion" paths where perhaps 3 plants are sequentially run in each cf path one after another.

    My head says "no", throw them all at ssis in parallel and let it decide what it can run simultaneously.  And of course make sure you aren't creating contention on resources by allowing things to happen in parallel (eg they are all loading the same target table simultaneously).  

    Thursday, August 15, 2019 1:49 PM
  • Hi db042190,

    The following link will be helpful.

    SSIS-Parallel Processing

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 2:37 AM
  • thx Mona. To better understand the question pls look at the second diagram in your link where 4 max concurrent executables was set.

    I have seen people break that container up into 3 containers with 2 having 4 tasks each and the 3rd having 2 tasks. 

    they think they are helping ssis to do what it already knows which is to execute only 4 at a time concurrently. 

    Is that a bad idea?  In my mind leaving all 8 in that container shouldn't hurt ssis if its been told to do 4 max at a time or even if it hasn't been told but we know the default is 4 because of the number of processors.

    do you understand what I am asking?


    • Edited by db042190 Friday, August 16, 2019 1:26 PM making post more accurate
    Friday, August 16, 2019 12:09 PM
  • You don't need to break it up to manually try micro-manage the SSIS threads.  SSIS is perfectly capable of doing it itself. And when you change hardware, you won't need to rework your SSIS packages.

    • Marked as answer by db042190 Friday, August 16, 2019 5:53 PM
    Friday, August 16, 2019 5:52 PM