Solved

Paralell SSIS Control Flows: What is the Optimal Parallel to Perform Well

Posted on 2008-10-03
3
484 Views
Last Modified: 2013-11-10
Hi,

We have two parallel control flows (doing the same thing) in a package. In purpose of further improving the performance, I am thinking about making more parallel flows for the package. Does it help with the performance (we have 8 AMD processors)? If so, how many parallel flows are the optimal number?

thanks for the help,
0
Comment
Question by:ebi168
3 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22638657
It depends... in the number of the rows in the pipeline.. hardware...
but see this link and download the word document with the suggested best pratices for improve SSIS performance
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx
Helped?
Regards
0
 

Author Comment

by:ebi168
ID: 22638973
thanks for your comment. I did get more info from coworker that it helps. But now what I am wondering is what is the optimal number of parallelism, based on that we have 8 processors? Having too many parallel things will eventually lower the performance. thanks,
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 22652546
Absolutely - too many will lower performance. The only way to work out "how many" is to look at the current capacity of the machine and the workload that an SSIS thread will consume. It is likely NOT to be a processor issue but a memory issue. Often the capacity of a dedicated SQL box sees relatively low CPU compared to Memory and (disk) I\O, though it is possible to have an underconfigured CPU  environment. That is most likely not your case. So, need to look at other configurations / performance criteria to calculate how many concurrent / parrallel processes are optimal given your environment.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now