Category: Integration Services

Andy Brownsword notes a major performance risk in Integration Services:

One particular performance issue with SSIS data flows can fly under the radar – spilling to disk. This isn’t clearly visible through regular debugging or execution so can go unnoticed. And it hurts.

Paging to disk is bad for performance. Disks are much slower to access than memory, so we want to keep our data away when possible.

Andy calls out two reasons why we might find spilling to disk, as well as how to track if this is happening.

Andy Brownsword takes us through a pattern:

We’ve recently looked at how caching can improve performance and I wanted to show how we can eek even more performance out of caches by using a custom approach I’ll term Selective Caching.

I’ll note here that there’s a potential gotcha with this approach which we’ll get to before the end of the post!

Click through for a description of the pattern and when it starts to break down.

Andy Brownsword makes those SSIS jobs run faster:

Last week we looked at using a cache to improve lookup performance. We saw how a cache improves performance by being able to reuse reference data repeatedly. That used a regular cache but it’s not the only option available to us.

In this post we’re going to look at the File Cache option which can achieve the same results – plus a little more.

My experience with file caches is that they’re a bit finicky but when you get them running, they can provide a significant speed-up to data enrichment tasks.

Andy Brownsword speeds things up:

When using data flows in SSIS packages we want the flow to be fast and fluid. Improving performance of the flows will vary in different packages, but one element can consistently help – tuning memory utilisation.

In this post we’ll look at tuning the memory utilisation by altering the number of rows passing through the flow concurrently. Specifically we’re looking at the following properties:

• DefaultBufferSize
• DefaultBufferMaxRows

This is a pretty big deal, finding the right size that allows you to keep data flowing smoothly without having to wait for buffers to fill. The defaults are aggressively low in most cases. And good on Andy for providing a series of tests to give practical numbers.

Andy Brownsword knows that sometimes, the only winning move is not to play:

Last time out we discussed blocking transformations, what they are, the impact of them, and touched on how to deal with them. In this post we’re going a step further to tackle one of them head on.

Here we’ll demonstrate the impact of blocking caused by the Sort transformation, and look at two options for solving this and slashing execution time.

Sorts aren’t the only blocking transformation that you should push back down to your source (if possible), but it is the most common example.