Parallel Task Processing (MaxConcurrentExecutables property) in SSIS

Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors.  To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and Engine Threads.

The MaxConcurrentExecutables property is a property of the package.  This property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package.  The default value is -1, which equates to the number of physical or logical processors plus 2.

The Engine Threads property is a property of each Data Flow task.  This property defines how many threads the data flow engine can create and run in parallel.  The Engine Threads property applies equally to both the source threads that the data flow engine creates for sources and the worker threads that the engine creates for transformations and destinations.  Therefore, setting Engine Threads to 10 means that the engine can create up to ten source threads and up to ten worker threads.

 Let’s see the example, Here I am taking an example of parallel processing. In the package I am taking 18 Execute sql task in sequential container and trying to see the best option for parallel processing.

I will test this task on below scenario

1.       System controlled parallel(default)

2.       System controlled sequential

3.       User controlled parallel

4.       User controlled sequential

And I will capture the time and decide which is the best.

This is my package design.

System controlled parallel (default)

By default MaxConcurrentExecutables properties set as -1.

Now I am executing this package. My system having 4 processes so by default 6 process starts parallel.

As soon as task has completed second task start executing.

 

Finally Package completed successfully.

 

Let’s see time taken to complete the task.

 

It took around 369 seconds.

System controlled sequential

Now I am connecting each task using Precedence constraints

Read More: http://bageshkumarbagi-msbi.blogspot.in/2016/05/precedence-constraints-in-ssis.html

 

It is executing one by one.

Let’s see the time taken by this.

 

Oh!!! My got it took 903 seconds.

User controlled parallel       

Now we are going to control the task execution. I am setting the MaxConcurrentExecutables properties -1 to 18. All tasks will execute at a time.

 

Now I am executing the package.

 

See every task start Executing at a time.

 

It is completed successfully. Let’s see the time taken by it.

User controlled sequential

Now I am setting MaxConcurrentExecutables is 6.

 

Now I am executing sequential

  

Let’s see the time taken by it.

 

Finally we can say that User Control parallel took less time compare to other.