Thursday, May 12, 2005

SQL PASS 2005 - SSIS Performance

As I'm sure you're aware by now SQL Server Integration Services (SSIS) is the new version of Data Transformation Services in SQL Server 2005. This is not a simple upgrade but a complete re-architecting of the product bringing it almost in line with other enterprise ETL tools.
The presentation from Grant Dickinson of Microsoft and Henk van der Valk of Unisys was a demonstration, from my perspective, not of the new features but the capabilities of the product in the area of scalability. SSIS likes memory and that was made particularly obvious by the charts and graphs we were shown that showed the 64bit server wiping the floor with the 32bit (as you would expect) and in some cases the 32bit server not even completing the process after hitting its 3Gb process limit.
Another key aspect of the improvements in performance is SSIS's ability to parallelise. You could always get DTS to do this but it was never pleasent. SSIS can now scale up incredibly well and again we were shown screenshots of the processor workload during a package run and it was certainly utilising whatever power it could get hold of.
note: I'm one of these people that tends to believe a screenshot rather than not (ever the optimist).
Any ETL fans out there are going to find plenty that is very familar in their world. The components are standard ETL fare (merge, lookup, etc.) but the one that floats my boat? Slowly Changing Dimension, oh yes. All they need to do now is get the thing cluster / scale out and the top end ETL tools will be well within Microsoft's sights.

No comments: