Iterative Design Optimization

At design time, it's very common to define execution trees to match requirements and the designer's thought process. Upon careful review, you can identify unnecessary branches and steps to reduce complexity and improve performance.

The following example shows a data flow with a separate execution tree for erroneous records. Keep in mind that an error can be defined simply as a value that doesn't meet certain criteria. The 10 million rows flowing through the Lookup Error Output pipeline don't meet the same criteria as the 40 million rows flowing through the main pipeline and are corrected by an expression in the Derived Column transformation. These rows flow down the error pipeline and are then joined back to the main pipeline in the Union All transformation. As you see, this data flow task takes 95 seconds to complete.

This all seems fine, but it isn't as efficient as it could be. Branching nonqualifying rows to a separate component for correction seems to make logical sense. However, a separate execution tree requires a separate thread and buffers to manage the data. Because the Derived Column transformation can be used to apply conditional logic to rows matching or not matching any criteria, the separate branch wasn't necessary. The next example shows the same package data flow with no error output from the Lookup transformation. As you see, all rows flow through one pipeline. The 10 million rows that were branched off in the previous example are handled in a conditional expression in the Derived Column transformation, and the other 40 million rows simply flow through, unaffected.

Package redesign can eliminate unnecessary thread and buffers allocation. As a result, it saves memory, disk I/O, and CPU usage. In the preceding example, this step resulted in a 25 percent performance optimization. Imagine how similar efforts could affect the performance of very complex packages with multiple opportunities to save time, effort, and ongoing maintenance costs.

Efficient package design is an iterative process. Be willing to forgo previous thinking to explore alternative solutions.

Logging an Execution Plan

In this exercise, you will view logging options and add an event handler to capture a pipeline execution plan. This package has already been configured for logging. You will review these properties to see where you would make changes in a new package.

View Logging Options and Add an Event Handler

1. With  Package 4.dtsx open in the package designer, view the Properties pane for the package. View the LoggingMode property and verify that it is set to Enabled.

   

2. Right-click over any white space in the package and select Logging from the menu. This opens the Configure SSIS Logs dialog box for the package.

   

3. Note that a text file log was previously added for this package.

4. Using the object list tree view in the Containers pane of this dialog box, select the first data flow task, and then select the Details tab on the right. A list of events supported by the data flow appears in a check box list.

   

5. Scroll down this list and select the PipelineExecutionPlan event.

6. Click the OK button to save changes and close this dialog box.

7. Execute the package and allow it to finish. Stop debugging when execution has completed.

8. The Log Events window is shown on the left side of the package designer. Use this window to find the event named User::PipelineExecutionPlan. Double-click this line to open the Log Entry dialog box.

9. View the Message text to review details for the data flow task execution plan recorded in this log event.

   

10. Click the Done button to close this dialog box after you have finished reviewing the log event details.