You should be cautious with SSIS logging. It carries a large I/O overhead and can slow application performance dramatically. Unless absolutely required I would limit SSIS logging t
o OnError and OnTaskFail and OnWarning events
Always consider the ordering of outputs within, for example, a Conditional Split transformation. The first output stream should be used to handle the common cases that apply to the most rows in the data flow. Specify the order in which the conditions are evaluated. Order is significant, because a row is sent to the output corresponding to the first condition that evaluates to true. Handle the exceptions after the common cases.
Increase the values of the DefaultBufferMaxSize and DefaultBufferMaxRows properties of Data Flow tasks. This serves to reduce the number of buffers moving through the data flow. Be careful however, that this does not lead to a situation where the data flow engine is swapping buffers to disk, as this would be counter-productive.
Implement parallel execution. Experiment with the MaxEngineThreads and MaxConcurrentExecutables properties of packages to optimise throughput in SMP architectures. Test throughput and reset values accordingly.
No comments:
Post a Comment