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


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.

 


Comments (1)

  1. ArthurZ says:

    A very welcome feature, but too short of a post to my liking at least.
    This feature needs more in-depth examples as the use case scenarios as not all workloads qualify for Scale Out.
    Please post like a user Case Scenario or case study.
    Thanks!

Skip to main content