What is MaxDop and degree of parallelism and how to set it ?

Definition of MAXDOP 

MAXDOP is a server configuration option that limits the number of processors used for prrallel plan execution.

Definition of  DOP 

DOP is the actual number of processors used by SQL server to execute a query in parallel.

How to set MAXDROP

 

Server level:

Using SSMS

1. Connect to the SQL Server instance in SSMS.

2.Right -click the server name and select properties.

3. Go to Advanced  and find max degree of parallelism.

4. Entered the desired value and click OK.

Using Transact-SQL:

    EXEC sp_configure `max degree of parallelism`,<value>; RECONFIGURE;

Database Level:

Using transact-SQL:

ALTER DATABASE SCOPED COONFIGURATION SET  MAXDOP = <value>;

Workload group level:

Using Transcact-SQL

CREAT WORKLOAD GROUP <workload_group_name > WITH (MAX_DROP=<value>);

Query level:

SELECT*FROM MyTable OPTION (MAXDOP<value>);