re: Improve ETL performance in SSIS by introducing parallelism in the data flow of an SSIS package.

Michael Brönnimann — Tue, 23 Aug 2011 23:08:36 GMT

Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).

There exists also a lightweight more generic and engine focused approach ;-)

The concept of SQL Parallel Boost is a native T-SQL based solution an allows a performance gain up to factor 10 (!) in a 8-16 CPU core environment.

The purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The solutioncan also be used to execute multiple SQL statements in parallel initiated thru the same connection.

The free 'Community Edition' can be downloaded at sqlparallelboost.codeplex.com

re: Improve ETL performance in SSIS by introducing parallelism in the data flow of an SSIS package.

Charl Lamprecht — Mon, 11 Jul 2011 08:10:05 GMT

The performance benefits do become very clear on large servers, but on smaller machines there is a perfomance loss a lot of the time because of contention issues. Nice article!