How to set Max degree of parallelism (MAXDOP)

Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.

Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of  online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).  

select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers
 where status='VISIBLE ONLINE' group by parent_node_id

Depending upon the workload in your environment you may increase or decrease the value.

Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.

To configure Max degree of parallelism follow the below steps.

1.Connect to the Database Engine.

2.From the Standard bar, click New Query.

3.Then execute the following query.

Syntax:

</pre>
sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */

GO

RECONFIGURE WITH OVERRIDE;

GO

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">

 

image

Method2

1.In Object Explorer, right-click a server and select Properties.

2.Click  Advanced  from select a page.

3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s