What MAXDOP setting should be used for SQL Server?

---

You can run the below query and identify the suitable MAXDOP for your SQL Server then  you can set the MAXDOP using sp_configure stored procedure :-

---

Below script to identify the suitable MAXDOP for SQL SERVER based on available CPU

---

select case
when cpu_count / hyperthread_ratio > 8 then 8
else cpu_count / hyperthread_ratio
end as optimal_maxdop_setting from sys.dm_os_sys_info;

 

 

 

 

 

 

---

Script to set the MAXDOP for SQL SERVER

---

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', <OutPut of the above script >;
GO
RECONFIGURE WITH OVERRIDE;
GO

Hope it will help you guys!!!

---