Basic SSIS Tuning

I hit the oPASS meeting last night and Brian Knight of End to End Training did a nice job of presenting some tuning examples for SQL Server Integration Services. I can't begin to do his presentation justice but I can pass along a few things I learned.

1) SSIS processes information asynchronously, that is, in parallel, whenever it can. asynchronous processing is good, Ferrari good. Synchronous items in your SSIS package are bad because they slow down the process. Like my friend's 1983 Chevette bad.

Applying a formula to data you need to load is asynchronous because the transformation can occur on multiple rows at once. A sort is synchronous because all the info has to be loaded into memory before the sort can occur.

2) If you have to have an asynchronous activity like a sort, use T-SQL when possible, not SSIS tools. Sorting the source data via a T-SQL at the start of a package is dramatically faster than using an SSIS process later in the package.

Books on Line has more info on asynchronous vs. synchronous items. In Brian's example, he was able to reduce a data load time from 10 seconds to a little over a second. I know, big deal in seconds, but when you scale this to minutes or hours it becomes a big deal fast.

As a preview, SSIS in SQL 2005 is very memory intensive but not a heavy processor user. In SQL 2008, SSIS makes better use of processor threads.

Update: My notes were right, my writing was wrong. The terminology in this post has been fixed.

Location Independent SSIS Packages

Jacob Sebastian of SQLServerCentral.com has a nice post today about creating location independent SSIS packages for SQL Server 2005. If you have more than about 3 SSIS packages take a look at this. You might want to move servers some day.

On a related note, Brian Knight will be at the December 18th oPASS meeting in Orlando speaking on the "12 days of SSIS". The event is at the RDV Sportsplex in Maitland and the fam has given me permission to stay out late that night so I'll be there.

There's something deliciously ironic about a bunch of geeks meeting at a world class sporting complex. Heaven knows I can't talk T-SQL and ice skate at the same time!