TSQL – Get Number Of Cores & MaxDOP In Single Query

This is a handy script to show the number of cores and MaxDOP in a single output, along with a built-in script to change the value. The built-in script shows up as a query result so you can copy/paste it.

SELECT cpu_count AS [Cores] 
        , (SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism') AS [MaxDOP]
        , '/*Next Line For SQLCMD Mode*/
:CONNECT ' + @@SERVERNAME + '
sp_configure ''max degree of parallelism'', ''VALUE''; 
RECONFIGURE;
GO
        ' AS [Configure Script]
FROM [sys].[dm_os_sys_info]