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.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server Integration Services 2012 (SSIS) thread monitoring and parallelism”

  1. Thanks for this post. I was also looking for where/how I could monitor SSIS threads.
    I am a bit perplexed, however. SSIS package logging tells me that my data flow has 10 executions paths/trees. And yet, when I monitor Task Manager for [Threads], I see 110 threads being spun. Can you help me understand the relationship? Many thanks.

Leave a Reply

Your email address will not be published.