Package Execution takes 100% CPU

Hi all,

I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).

Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?

Try to redefine your SSIS package...

Regards!

|||Is SSIS taking up the processor, or is it the relational engine? I assume it is the latter.

First off, if SQL Server is sharing the box with other processes, you need to limit the max amount of memory it can take. Otherwise, it is likely to hog it all and starve the other processes.

Second, if you have a lot of rows to update you should bulk insert the changes to a staging table and then run a single UPDATE statement joined between the staging and the target table. Using the OLE DB Command, SSIS can only update a single row at a time, which is very inefficient.

Finally, for a large table you need to be very careful about your indexes. A table with a lot of indexes will be much slower to update than one without. Ideally, you only want one index on this table, the one that will allow you to find the row to update. Obviously, you'll need to balance this with the intended use of the table.

|||Thanks for your answer.