What is recommemded Maximum Degree of Parallelism(MAXDOP) in SQL Server ?

By default , Maximum Degree of Parallelism(MAXDOP) in SQL Server is zero. MAXDOP value is zero means all available process/thread will work together  for each single tasks.To optimize SQL Server for the day to day workload, My recommendation is to configure Maximum Degree of Parallelism(MAXDOP) rather than defaault value.  How many processes/threads do you want to allow to work together for each workload - it is really hard to calculate. Best recommendation is the value of MAXDOP = No of CPU on that server.

Steps for configure MAXDOP:

Step 1.

Following query lists how many CPUs in a particulat instance of SQL Server

Select cpu_count from sys.dm_os_sys_info

 

 

 

 

Step 2. Assume, Server has 8 CPUs 

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

----Use MaxDOP = no of CPU

 

EXEC sp_configure 'max degree of parallelism', 8

GO
RECONFIGURE WITH OVERRIDE
GO

Step 3.
Restart SQL Server Services