December 10, 2014 at 1:50 pm
I am trying to optimize the performance of a data flow task in SQL Server 2008 R2.
The data flow has 6 OLE DB Source adapters.
Each source calls a stored proc, and then a UNION ALL transformation brings all that data together (screenshot attached).
There are several transformations that happen, and eventually the data is multicast into 3 different destinations.
When I review traces, I can see that the stored procs are not executing in parallel.
Rather, they are executing consecutively: one stored proc completes and then the other begins, etc.
My goal is to improve performance by having the 6 stored procs execute concurrently (i.e. in parallel).
Currently, the [EngineThreads] property is set to 5.
From my research, I have read that the [EngineThreads] property governs how many threads are able to execute concurrently.
I have read that it is a suggestion to SSIS, and that SSIS may choose to actually use more or fewer threads.
So, my first question is: how do I actually monitor [EngineThread] usage?
Is it the [Threads] column in Windows Task Manager (screenshot attached)?
My second question is about the relationship between Engine Threads and Execution Paths.
Having enabled package logging, I am able to tell that there are 10 execution paths (trees).
Does that mean that there will be 10 threads required?
I have not tinkered with the MaxConcurrentExecutables property, because no tasks in the Control Flow run in parallel.
Many thanks in advance for any answers/opinions.
- Simon
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply