Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
SQL Server Integration Services (SSIS) allows parallel execution in two different ways. These are controlled by two properties as outlined below.
The first one is MaxConcurrentExecutables, a property of the package. It defines how many tasks (executables) can run simultaneously. It defaults to -1 which is translated to the number of processors plus 2. Please note that if your box has hyperthreading turned on, it is the logical processor rather than the physically present processor that is counted.
Example:
Suppose we have a package with 3 Data Flow Tasks. Each task has 10 flows in the form of “OLE DB Source -> SQL Server Destination”.
Set MaxConcurrentExecutables to 3, then all 3 Data Flow Tasks will run simultaneously.
Now whether all 10 flows in each individual Data Flow Task get started concurrently is a different story. This is controlled by the second property: EngineThreads.
The EngineThreads is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5.
Example:
Again let’s use the above example.
If we set EngineThreads to 10 on all 3 Data Flow Tasks, then all the 30 flows will start off at once.
One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler. So in the above example, a value of 10 for Engine Threads means up to 10 source threads and 10 work threads.
Multi tasking is a double-edge sword. In SSIS, we don’t affinitize the threads that we create to any of the processors. So if the number of threads exceeds the number of available processors, you might end up hurting throughput due to an excessive amount of context switches. Be cautious!
- Runying Mao
Anonymous
May 15, 2007
I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property
Anonymous
May 15, 2007
I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property...
Anonymous
June 01, 2007
Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal
Anonymous
March 01, 2011
i am not able to find maxconcurrentexecutables in SSIS package properties.Can any one help me?
Thanks,
Venkat
Anonymous
March 02, 2011
The comment has been removed
Anonymous
May 06, 2011
The comment has been removed
Anonymous
May 11, 2011
The comment has been removed
Anonymous
August 22, 2011
A free Community Edition of SQL Parallel Boost can be downloaded at sqlparallelboost.codeplex.com
Anonymous
April 05, 2012
The comment has been removed
Anonymous
May 03, 2015
The comment has been removed
Please sign in to use this experience.
Sign in