I have a Database server and an SSIS server both are SQL 2008 R2. There are some SSIS processes that take up 100% CPU (8 of 8 cores) when they run.
Is there a way for me to set a MAXDOP on the SSIS box so that the processing only takes 6 cores at the max instead of all 8?
asked Feb 21, 2012 at 05:03 PM in Default
Parallelism is handled in SSIS by two properties
Since, you do not have a dedicated server to SSIS, a low value for MaxConcurrentExecutables could help you out. But what would be the best value, that you have to sort out through testing and according to the workload.
answered Feb 22, 2012 at 05:34 AM
you can use the option parallelism hint in your query or stored procedure. something like
select * from x option (MAXDOP 6)
answered Feb 21, 2012 at 05:24 PM
As @teshome mentioned, you can set max degree of parallelism in each query SSIS is doing, but this will not prevent a SQL Server overloading if too much concurent executables are running on the SSIS side and multiple heavy queries executed against the database server
AS @Usman mentioned, the Engine Threads and MaxConcurentExecutables will allow you to control load of SSIS server and also load of database server in case multiple packages are being executed and sending multiple queries to the database server. Again if will have single not well optimized query it can nearly kill your database server.
So to properly balance the load you should combine both the techniques.
If you have Enterprise version of SQL Server then the best and easiest way how to handle the workload on the server woul be use of Resource Governor. Using it you do not need to touch any of the SSIS packages if you write appropriate classification funtion.
See Managing SQL Server Workloads with Resource Governor for details on Resource Governor.
But even you will use Resource Governor, it is important to write properly tuned queries and allocate the load of SSIS server too.