Problem with Running Multiple Execute Package Task in parallel
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 !!
March 6th, 2012 3:34pm
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.
Free Windows Admin Tool Kit Click here and download it now
March 6th, 2012 5:25pm
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.
March 7th, 2012 8:01am
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
Free Windows Admin Tool Kit Click here and download it now
March 7th, 2012 8:06am
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.
March 7th, 2012 9:39am
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
Free Windows Admin Tool Kit Click here and download it now
March 7th, 2012 1:11pm
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
March 12th, 2012 11:38pm
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.
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2012 3:06am