Max degree of parallelism (MAXDOP) in SQL server 2000/2008/2008R2/2012/2014/2014/2017.

As an SQL Server DBA, recently, I have given interview and interviewer asked below questions in Bangalore(India).

1. What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?
2. When SQL Server considers parallel execution plans for queries?
3. How to change max degree of parallelism in SQL server? 
4. Who can change the max degree of parallelism in SQL server? 
5. What permission required change max degree of parallelism in SQL server? 
6. Where we can set of max degree of parallelism in SQL server? 
7. What is the limitation of max degree of parallelism in SQL server?
8. In which scenario, we need to use max degree of parallelism option in SQL server?
9. Examples of max degree of parallelism?

What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?

A max degree of parallelism is a number of processor. which says, how many CPU or Microprocessor will use execution plan parallelly to execute the query.

When the SQL instance run the query in SSMS that use microprocessor or CPU to execute the query. At that time, CPU or microprocessor detect max degree of parallelism.

For example, if the max degree of parallelism value set as 4, and we are running any query, so the 4 CPU will parallel execute the execution plan of that query.

When SQL Server considers parallel execution plans for queries?

When run DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

How to change max degree of parallelism in SQL server?

Right Click on SQL Server Instance à Properties à Advance à max degree of parallelism à change the value  à Ok.

OR

USE AdventureWorks2012 

GO  

EXEC sp_configure 'show advanced options', 1; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO 

EXEC sp_configure 'max degree of parallelism', 4; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO

Who can change the max degree of parallelism SQL server? What permission required change max degree of parallelism SQL server?

User should have sysadmin and serveradmin fixed server roles.

What is the limitation of max degree of parallelism SQL server?

If the affinity mask set as default (option is checked), it will not restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

If affinity mask not set as default (option is Unchecked) it will restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

Where we can set of max degree of parallelism in SQL server?

We can set it on instance level and object level. In below example, we have set on object level.

In which scenario, we need to use max degree of parallelism option in SQL server?

As per the number of query- 

If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value.

For example, you can set the MAXDOP value to 16.  

If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value.

For example, you can set the MAXDOP value to 4. 

As per the Microsoft Stranded- 

MaxDop setting will vary depending upon the number of CPU cores:

CPU Cores (Total)	MaxDop
1	1
2-7	2
8-16	4
>16	8

* Assuming mixed SQL workloads, involving both OLTP and reporting.

Note Any value that you consider using should be thoroughly tested against the specific application activity or pattern of queries before you implement that value on a production server.

Examples:-

To go the table à expend à Indexes à select Index à right click on select index à Properties à Option à change the value (default value is 0).

Example - MAXDOP for Create Index: -

USE AdventureWorks2012; 

GO 

CREATE INDEX IX_Product_NewID ON Product (ID)  WITH (MAXDOP=4); 

GO 

Example - MAXDOP for Rebuild Index: -

USE AdventureWorks2012; 

GO 

Alter INDEX IX_Product_NewID ON Product  REBUILD WITH (MAXDOP=4); 

GO 

Example - MAXDOP for select command: -

USE AdventureWorks2012; 

GO 

Select * from Product (nolock)  ORDER BY ProductID

OPTION (MAXDOP 1)

Note: - If the MAXDOP is set for the query and query execution time is more then check the execution plan of that query, in the plan you can see the parallelism cost in %, and then increase or decrease MAXDOP value. 

Please add if you have some interesting fact on MAXDOP - Jainendra Verma