What is MAXDOP in SQL Server and How can you configure it?

Maximum Degree of Parallelism (MAXDOP) is an option in SQL server which enables you to configure SQL Server to use available CPU/CPUs to run a single statement in parallel execution plan. MAXDOP configuration highly depends on SQL Server edition, CPU type and operating system.

MAXDOP : 0  -- This is default MAXDOP for SQL Server which enables SQL Server to use all the available CPUs for single statement.

MAXDOP: 2 -- You can use this option to limit SQL Server from using all the available CPUs. It doesn't have to be 2, you can setup any value depending upon your performance gain of your statement with different value of MAXDOP.

In my experience, I had to play with MAXDOP for certain statements coming from different servers in order to find best option of MAXDOP.

You can configure MAXDOP differently for each session as well by using hostname etc. using resource Governor or it can also be used in SQL Server Statement by setting up the MAXDOP value in the statement.

How to Change MAXDOP value in SQL Server using T-SQL?

You can use below statement to change MAXDOP value in sql server. Below statement doesn't need SQL Server services restarted in order to take effect.

sp_configure 'show advanced options', 1;
GORECONFIGURE WITH OVERRIDE;
GOsp_configure 'max degree of parallelism', 2;
GORECONFIGURE WITH OVERRIDE;
GO

How to Change MAXDOP value using Management studio?

Step 1:
Right click on SQL Server instance, go to properties

Step 2:
Click on Options and go to Parallelism Option

Step 3:
Find Max Degree of Parallelism and change value to your desired value.

 
Fig 1. How to change MAXDOP value in SQL Server
 
 

2 comments: