Using SSIS Scale Out to improve the performance of execution log writing

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

First published on MSDN on Jun 15, 2017
As you may already known, SSIS Scale Out is available in SQL Server vNext CTP1 . it provides the capability to parallel package execution by distributing the execution to multiple machines. In addition, you can get better performance of execution log writing from SSIS Scale Out.

For the user who is used to using verbose logging level to run SSIS package, the performance of package execution will be downgraded heavily when verbose logging level is enabled, in this case the SSIS Scale Out is an excellent choice for you even you don’t need to run multiple packages parallelly.

I did a performance test to compare the performance of Scale Out execution and non-Scale Out execution. I designed a package with an empty data flow task in a for loop container as below. The for loop container is to run the empty data flow task for 5000 times.

Executing this package in non-Scale Out mode with none execution logging level it will cost 54 seconds to complete the package execution without writing any execution log. After enabling the verbose logging level, comparing taking more than 130 seconds to complete the package execution in non-Scale Out mode, it only takes around 80 seconds if executing in Scale Out. The performance is much improved.

You can get more detail about how to execute package in Scale Out from here .

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.