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.
You, Me and Dynamics GP
The premier information site for Microsoft Dynamics GP
