Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Parallel execution 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 EngineThreads.  If you are like me, you probably did not even know about these two properties, and therefore were unaware of the opportunity to make your SSIS packages execute faster.  A description of each property:

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 EngineThreads 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 EngineThreads 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 EngineThreads to 10 means that the engine can create up to ten source threads and up to ten worker threads.  The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.

One other thing to consider: If you are using the Execute Package Task, the child package to be executed can be run in-process or out-of-process by use of the ExecuteOutOfProcess property.  If a child package is executed out-of-process, you will see another dtshost.exe process start.  These processes will remain “live”, using up resources, for quite a while after execution is complete.

If executing in-process, a bug in a task of the child package will cause the master package to fail.  Not so if executing out-of-process.  On 32-bit systems a process is able to consume up to 2GB of virtual memory.  Executing out-of-process means each process can claim its own 2GB portion of virtual memory.  Therefore if you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don’t have the overhead of launching more processes.

More info:

SSIS – An Inside View Part 4

SSIS Nugget: Engine Threads

Improving the Performance of the Data Flow

Comments

Posted by sabotta on 17 November 2011

For a demonstration of how to increase package performance by designing for parallelism, watch this video by Matt Carroll (Senior Development Lead, SSIS Product Team).

technet.microsoft.com/.../ff686759.aspx

Posted by Anonymous on 17 November 2011

Pingback from  Parallel execution in SSIS | SQL Server | Syngu

Posted by sabotta on 2 December 2011

You can also improve package performance by using the new Balanced Data Distributor to parallel data flow buffers to multiple outputs.  For a demo of this new data flow component, see the Balanced Data Distributor video at technet.microsoft.com/.../hh369962 .

Leave a Comment

Please register or log in to leave a comment.