January 4, 2012 at 8:24 am
We have around 100 packages. which are running in BIDS there is no SSIS service installed on the machine. source and target DBs are in different machines. BIDS is consuming very little of the available CPU. How do we make BIDS to utilize more CPU. we tried by increasing buffer size and engine threads at data flow level but theres no change in the CPU utilization. Is there any settings we need to change in order to make BIDS to consume more CPU resources. snapshot of the CPU is attached for reference.
Thank you.
January 4, 2012 at 12:14 pm
So you are running all the packages manually and you aren't using all the processor you'd like to be..
Why are you not using an SSIS service?
CEWII
January 4, 2012 at 8:30 pm
Thanks Elliott.
Sorry for not providing the sufficient information.
We have a master package which triggers all the packages in sequencial order since we are using checkpoints.
These packages are running in client's machine some how client is not interested in installing the SSIS service.
January 5, 2012 at 8:23 am
What leads you to think that CPU is a bottleneck?
On most of my SSIS packages (even when run through BIDS) doing data loads, network or I/O are the typical slow down points with memory also being a possibility. But I'm not doing a ton of calculations and these are largely just pull form source, do some transforms and write to destination type packages.
Of course your environment is different; but I was just curious why you think CPU is your performance problem.
Rob
January 5, 2012 at 9:29 am
Sorry, but I'm going to be kind of rigid on this topic. While I understand wanting to be as customer focused and flexible as possible BUT if you want to run SSIS packages I require SSIS to be installed, it isn't negotiable, no SSIS = NO SSIS packages.
I want to point out that the BIDS environment imposes a certain amount of overhead to display the GUI and there are a lot of factors OTHER than CPU that could be a problem. However, the fact that not all the processor is used is not necessarily a problem. I also want to point out something else, when you run an SSIS package off the server (ie on your desktop) if you are pushing data from one location to another it ALL flows through the desktop, so it makes two trips over the wire which is costly in terms of performance.
I have seen cases where SSIS was installed in one place and used communally and there were some advantages to it and most other cases where each server has its own SSIS server, which works well and has its own advantages.
Bottom line, you have deployed a solution that is not very supportable and a position that an SSIS server is required is the most logical.
CEWII
January 5, 2012 at 10:04 am
I'm not in disagreement with you Elliot about having SSIS installed and running on the server and not through BIDS on a local machine.
if you are pushing data from one location to another it ALL flows through the desktop, so it makes two trips over the wire which is costly in terms of performance.
Most definitely so!! A big performance hit.
There is an enhancement/improvement in SSIS 2012 that will allow you to "run your packages remotely" (directly on the server) to avoid this round trip penalty. I've only heard this mentioned on a webcast, so I haven't tried it, nor have the details, but it souonds like a nice improvement. Unfortunately for the OP, he's not on 2012.
Rob
January 5, 2012 at 11:40 am
You can accomplish this RIGHT NOW, its called an SSIS Server..
CEWII
January 5, 2012 at 1:24 pm
Really? for development you can get the immediate BIDS feedbak by running package through a SQL Agent job on the server?
Rob
January 5, 2012 at 3:35 pm
Thought we were talking about production runs not development... However, I am generally willing to take the performance hit to test locally.. Maybe its just me..
CEWII
January 10, 2012 at 11:55 pm
when we run the package and monitor the task manager for CPU and network utilization CPU consumed is less than 10%. though data in the source table is having more than 100k records.
Any how, finally we could convince the client to install ssis service. now we are waiting to run the batch with SSIS service installed in the machine where the packages are run. Will update you with the performance we once execute all the packages. Thanks everyone for taking their time to answer and take this topic forward to get some fruitfull results.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply