THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search

Enjoy Another Sandwich -- Kent Tegels

Yummy slices of SQL Server between slices of .NET and XML

The Top Five New Features in SSIS 2008 for Developers, Part 1

You have to give credit where credit is due, and I certainly have to give credit to Kirk Haselden et al and their book Microsoft SQL Server 2005 Integration Services. That book -- and some of trial and error -- taught me a lot about how to tune data flow tasks for better performance. The folks at Simple Talk have part of the book online and specifically about tuning data flows (http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/). If you aren't all that familiar with the tuning of SSIS 2005 data flows, it's a great piece to read before going at the rest of this article.

I'd love to say that you don't need to worry about data flow tuning in SQL Sever 2008 Integration Services (SSIS100), but I can't. What I can say is you need to focus less on CPU allocation and utilization. As noted in the aforementioned article, in SSIS 2005 CPUs are allocated statically as the data flow task initializes. Basically, this means that if the optimizer for SSIS 2005 determines that it only needs one or two CPUs to execute the data flow, that is all it would ever use even if more CPUs were available to work the data flow. What change for SSIS100 is that CPU are now dynamic scheduled based on amount of "flow pressure" and all available CPUs are utilized. "Slower" tasks can now get more CPU time.

Why did the SSIS team decide to use static scheduling in SSIS90 but dynamic scheduling in SSIS100? The answer is simple: back in 2002 to 2004 when SQL Server 2005 was being planned and developed, multiple CPU hosts were not as common as they are today. When you only have one or two CPUs to work with, static schedule is easier and has the least execution plan generation time and cost. However, now that most production-level hosts have four or more CPUs to work with, dynamic allocation is a more efficient solution. On multiple CPU machines, this change enhances performance considerably in many cases. On the downside, on single CPU machines, performance may actually degrade some, so it is certainly something you want to know about and consider.


Published Monday, August 18, 2008 4:56 PM by ktegels

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(optional)
(required) 
Submit

About ktegels

Kent Tegels is an Adjunct Professor at Colorado Technical University and a member of the technical staff at PluralSight. He has been recognized by Microsoft with Most Valuable Professional (MVP) status in SQL Server for his community involvement with SQL Server and .NET. Kent holds Microsoft Certifications in Database Administration and Systems Engineering. He has contributed to several books on data access programming and .NET and is well known industry speaker. He lives Sioux Falls, South Dakota.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement