In SSIS2005, it is very difficult to determine which piece of a data flow task is the bottleneck. There are recommended instructions for measuring performance of the components in the data flow, but this methodology can be very tedious to step through manually. BI Developer Extensions’s Pipeline Component Performance Breakdown feature automates this methodology for you and lets you trend component performance as you try different settings and design alternatives.
(In SSIS2008, look at the built-in PipelineComponentTime event.)
To launch this feature, right click on a Data Flow task on the control flow tab of the SSIS package designer and choose “Performance Breakdown”. (You can also launch this feature by right clicking on the background of the data flow tab’s design surface and choosing the same menu option.)
BI Developer Extensions makes a temporary copy of your package, pulls it apart into a number of testing iterations to measure and isolate component performance, then reports the individual durations of each component.
In the following example, the Trial 1 column represents the first time I ran this feature. Previously, I couldn’t tell whether the source, the lookup, or the destination was the bottleneck, but the grid clearly shows the Lookup is the bottleneck. Upon closer examination, I noticed I had enabled memory restriction for that lookup component causing SSIS to execute singleton SQL queries, one per row in the pipeline, to perform the lookup. I disabled memory restriction which caused the lookup table to be cached in memory once, and performance was drastically improved, as seen in the Trial 2 column.
As the tests are being run, the source components pumped to raw files so that each source is only hit once. Also, the data is only pumped to the destinations once per execution of this feature.
Testing Methodology Example
When the temp package is broken apart into separate test iterations utilizing raw files and rowcount transforms as replacements, a very simple package would be tested in the following way:
Errors: If any errors occur during one of the test iterations, BI Developer Extensions stops and asks you if you would like to troubleshoot the temp copy of the packages. If you accept, it will not delete the temp directory so that you can troubleshoot the error. Please report any problemmatic packages to the issue tracker.