設置 MAXDOP 的腳本

我需要編寫腳本來設置 MAXDOP。

—-我有這個腳本—-

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

我需要使用上面的optimal_maxdop_setting 輸出來設置maxdop 的腳本

我不想在上面輸出並插入下面的查詢，

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;

嘗試這個：

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

引用自：https://dba.stackexchange.com/questions/186364