locked
Problem with Running Multiple Execute Package Task in parallel RRS feed

  • Question

  • Hi All,

    We have a scenario where we want to run 3+ Execute Package Tasks in parallel.

    The problem is that while running the Master package which calls 3 other child packages, the child packages are running in sequence rather than parallel.

    I have check the value of the MaxConcurrentExecutions, tried to change the value to 3. But still the same.

    The Execute package task are getting called together, but the execution of the control flow tasks of 2nd and 3rd packages are started. Only after the whole execution of 1st package the 2nd starts and then the 3rd package.

    Any help is much appreciated. Thanks !!

    Tuesday, March 6, 2012 12:34 PM

Answers

All replies

  • Hi,

    Does the statements access same tables? Maybe the other 2 statements are being blocked (while trying to access the same resources as 1st statement)..

    David.

    Tuesday, March 6, 2012 12:47 PM
  • Hi David,

    Yes they do access the same table. But its a read operation and i have specified nolock.

    Also, both the packages are starting their own transactions.

    Please let me know if there is something else to be taken care off.

    Thanks,

    Swapnil

    Tuesday, March 6, 2012 12:52 PM
  • It's a read operation with nolock inside a transaction?? Something else is being made right?

    Try the following, start those 3 statements in Management Studio and check if they are running (in parallel) or if you see some kind of blocking. You can use sp_who2 for that check: http://sqlserverplanet.com/dba/using-sp_who2

    David.

    • Proposed as answer by Eileen Zhao Friday, March 9, 2012 9:09 AM
    Tuesday, March 6, 2012 2:25 PM
  • Set MaxConcurrentExecutables to -1

    Make sure the RetainSameConnection of your CM (Connection Manager) is set to FALSE.


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

    Tuesday, March 6, 2012 5:57 PM
  • Hi Swapnil,

    What is the number of processor do you have on the server that used to run the package? Running more threads than the number of available processors can hinder performance because of the frequent context-switching between threads. For more information about running tasks in parallel, please refer to the following articles:

    http://msdn.microsoft.com/en-us/library/ms141031.aspx
    http://ssis-2008.blogspot.com/2010/08/parallel-processing.html 

    Thanks,
    Eileen

      
    • Marked as answer by Eileen Zhao Wednesday, March 28, 2012 1:00 AM
    Tuesday, March 13, 2012 3:19 AM
  • Hi Swapnil,

    What is the number of processor do you have on the server that used to run the package? Running more threads than the number of available processors can hinder performance because of the frequent context-switching between threads. For more information about running tasks in parallel, please refer to the following articles:

    http://msdn.microsoft.com/en-us/library/ms141031.aspx
    http://ssis-2008.blogspot.com/2010/08/parallel-processing.html 
      

    Even if there's only one core, SSIS should be able to execute the 3 dataflows in parallel (MaxConcurrentExecutables = 1 core + 2 = 3).

    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Wednesday, March 28, 2012 1:00 AM
    Tuesday, March 13, 2012 6:48 AM