One of the main important aspects in ETL design is to increase the performance. Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors. There are 2 properties relevant to parallel process, they are MaxConcurrentExecutables and EngineThreads.
MaxConcurrentExecutables: 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 number of physical or logical processors plus 2. Here I am showing how this is working in real scenario. As below image show the package MaxConcurrentExecutables is set to -1. In this case my laptop has 4 logical processors. So there should be max 6 concurrent processes.
The package that shows in the below image has 10 data flow task, each data flow is independent to each other’s. Here we can see that only 6 data flows run, other 4 are not started.
Once changed the MaxConcurrentExecutables to 10, we can see all the 10 data flow tasks are running parallel.
EngineThreads: This property is for each Data Flow Task. This property defines how many threads a data flow engine can create and run parallel.
When execute the package with 10 EngineThreads, within a few seconds only 6 data flow started to work.
Once I changes it to 30, we can see that all the data flow task are stared to work within the same amount of time.
Cheers!
Hi Uma .. many thanks for the post. One question if I have is with regard to the engine thread. When set to 10 why did only 6 threads execute?
ReplyDeleteHi Aarion,
DeleteSorry for the late reply, in Engine Thread what I am trying to show is, few processes were stared within particular time, but once I increased the Engine Thread, more processes were started within the same amount of time. Counting don’t have any meaning for Engine thread, all about internal engine processing and resources utilization. But, for MaxConcurrentExecutables count is meaning full.
Very useful post..
ReplyDeleteexplained very clearly(pictorial representation).
Thanks a lot.. keep up the good work.
It is very much helpful.
ReplyDeletethanks