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?

more ▼

asked Feb 21, 2012 at 05:03 PM in Default

avatar image

Raj More
1.8k 83 89 90

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Parallelism is handled in SSIS by two properties

  1. MaxConcurrentExecutables, a property of the package. It defines how many tasks can run in parallel. The default value is -1 which means the number of processors + 2.

  2. EngineThreads, a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2. However, the engine will not use more threads than it needs, regardless of the value of this property. The engine may also use more threads than specified in this property, if necessary to avoid concurrency issues.

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.

more ▼

answered Feb 22, 2012 at 05:34 AM

avatar image

Usman Butt
14.8k 6 13 21

(comments are locked)
10|1200 characters needed characters left

you can use the option parallelism hint in your query or stored procedure. something like

select * from x option (MAXDOP 6)

more ▼

answered Feb 21, 2012 at 05:24 PM

avatar image


(comments are locked)
10|1200 characters needed characters left

Both @teshome and @Usman are right but.

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.

more ▼

answered Feb 22, 2012 at 06:49 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 21, 2012 at 05:03 PM

Seen: 4151 times

Last Updated: Feb 22, 2012 at 06:49 AM

Copyright 2018 Redgate Software. Privacy Policy