SQL Server Integration Services 2012 (SSIS) thread monitoring and parallelism

Hi Friends,

Last week I spent some time with SQL Server Integration Services 2012 parallelism and thread monitoring. Here are some learnings:

MaxConurrentExecutables setting at package level determines the number of parallel threads for control flow. In other words, it controls how many control tasks can run in parallel. The default value of -1 means; SSIS will allocate number of logical processors +2 threads. On my VM with 4 logical processors, I could run 6 parallel tasks.

EngineThreads is a setting for Data Flow which determines the number of parallel operations you can run inside the Data Flow. Note that this is only a suggestion/hint to the SSIS engine.

Monitoring the threads:

In my test, I created a master package which calls 20 child packages and each child package runs a DFT that runs 20 parallel tasks. I set the MaxConcurrentExecutables for the master package to 20. The moment I execute my package, I was spinning more than 400 threads approximately and was benchmarking a few things. But the most important learning I had was to monitor the right process. Yes, you have to monitor different processes depending on where you run the package from. Thanks to my friends at Microsoft & MVP community for pointing me to the right process – Matt Mason, Phil Brammer, Arthur & Greg Low. So, here is the summary:

If you are running the packages from inside visual studio IDE, you need to monitor the threads in dtsdebughost.exe.
If you are running the packages from the command line, you need to monitor the threads in dtexec.exe.
If you are running the packages through the SSIS Catalog (2012), you need to monitor the threads in ISServerExec.exe.

You could use task manager to see the threads or use a tool like procexp.exe from sysinternals suite.