x

MAXDOP for SSIS

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 '12 at 05:03 PM in Default

Raj More gravatar image

Raj More
1.7k 77 82 84

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

3 answers: sort newest

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][1] 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.

[1]: http://msdn.microsoft.com/en-us/library/bb933866.aspx
more ▼

answered Feb 22 '12 at 06:49 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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

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 '12 at 05:34 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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 '12 at 05:24 PM

teshome gravatar image

teshome
11

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x899
x562
x5

asked: Feb 21 '12 at 05:03 PM

Seen: 1782 times

Last Updated: Feb 22 '12 at 06:49 AM