Chapter 11: Optimizing SSIS Packages

 Download CD Content

Overview

After completing this chapter, you will be able to:

• Generally understand the SSIS engine components and processes.

• Review the memory buffer architecture.

• Monitor package execution trees and their impact on package performance.

• Manage buffer properties.

• Manage task and component parallelism.

• Understanding performance management essentials.

• Use iterative optimization techniques to simplify and tune a package design.

Microsoft SQL Server 2005 Integration Services (SSIS) goes a long way toward ensuring optimal performance with little direct input from the person designing packages. As much as possible, the execution engine will make intelligent decisions about the appropriate use of buffer allocation, memory usage, and whether tasks can be processed in parallel or serial fashion. SSIS also affords many opportunities to affect the way a package executes. A basic understanding of the SSIS run time and data pipeline engines and their core components will help you understand better how system resources are used to perform package execution. The memory buffer architecture allows data flow to be managed in discrete steps as set and row operations. Your choices of tasks, transformations, and precedence constraints will affect the engine's ability to reuse buffers and block related operations only when necessary.

When you design data integration solutions, your design decisions not only determine how successfully your solution meets functional requirements but also how well your solution will perform. To make the right performance design decisions, you need to understand the performance architecture of your data integration tool and, just as important, the techniques that enable you to maximize the tool's use of system resources such as memory, disk or physical storage, and CPU.

SSIS provides full-featured data integration and workflow engines coupled with a rich development environment for building high-performance data integration solutions. SSIS provides a variety of optimization opportunities to help you maximize resource use while successfully meeting the needs of your specific data integration scenario.