Script to setup MAXDOP [duplicate]

Question

I need to script toi set MAXDOP.

----I have this script----

select cpu_count,
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

I need script to setup maxdop by using optimal_maxdop_setting output above

I do not want to take output above and plug on the query below,

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;

Answer 1

Try this:

set nocount on
declare @cmd nvarchar(100)
declare @maxdop varchar(5)
SELECT @maxdop = convert(varchar(5),CASE 
        WHEN cpu_count / hyperthread_ratio > 8
            THEN 8
        ELSE cpu_count / hyperthread_ratio
        END)
FROM sys.dm_os_sys_info

set @cmd='sp_configure ''show advanced options'', 1;'
print @cmd
EXECUTE sp_executesql @cmd
set @cmd = 'RECONFIGURE WITH OVERRIDE;'
print @cmd
EXECUTE sp_executesql @cmd
set @cmd = 'sp_configure ''max degree of parallelism'',' + @maxdop + ' ; '
print @cmd
EXECUTE sp_executesql @cmd
set @cmd = 'RECONFIGURE WITH OVERRIDE;'
print @cmd
EXECUTE sp_executesql @cmd