DECLARE @configureNumaNodeAffinity BIT = 0;

DECLARE @debug BIT = 0;

DECLARE @physicalMemoryInMb DECIMAL(20, 4) = 1908408;

DECLARE @physicalMemorySource DECIMAL(20, 4);

DECLARE @physicalMemory DECIMAL(20, 4);

DECLARE @recommendedMemory DECIMAL(20, 4);

DECLARE @overheadMemory DECIMAL(20, 4);

DECLARE @cpuArchitecture DECIMAL(20, 4);

DECLARE @numaNodes INT;

DECLARE @numaNodesAfinned TINYINT;

DECLARE @maxWorkerThreadCount INT;

DECLARE @threadStack DECIMAL(20, 4);

SELECT @cpuArchitecture = CASE

WHEN @@VERSION LIKE '%<X64>%' THEN

2

WHEN @@VERSION LIKE '%<IA64>%' THEN

4

ELSE

0.5

END;

SELECT @numaNodes = COUNT(DISTINCT [parent_node_id])

FROM [sys].[dm_os_schedulers]

WHERE [scheduler_id] < 255

AND [parent_node_id] < 64;

SELECT @numaNodesAfinned = COUNT(DISTINCT [parent_node_id])

FROM [sys].[dm_os_schedulers]

WHERE [scheduler_id] < 255

AND [parent_node_id] < 64

AND [is_online] = 1;

SELECT @maxWorkerThreadCount = [max_workers_count]

FROM [sys].[dm_os_sys_info];

SELECT @threadStack = @maxWorkerThreadCount * @cpuArchitecture / 1024.0;

IF @debug = 1

SELECT @physicalMemorySource = @physicalMemoryInMb;

END;

-- Get physical RAM on server

SELECT @physicalMemorySource

= CAST([total_physical_memory_kb] AS DECIMAL(20, 4)) / CAST((1024.0) AS DECIMAL(20, 4))

FROM [sys].[dm_os_sys_memory];

END;

SELECT @physicalMemory = CEILING(@physicalMemorySource / CAST(1024.0 AS DECIMAL(20, 4)));

IF (@physicalMemory <= 2.0)

SELECT @overheadMemory = 0.5;

END;

IF (@physicalMemory > 2.0 AND @physicalMemory < 4.0)

SELECT @overheadMemory = 2.0;

END;

IF (@physicalMemory >= 4.0 AND @physicalMemory <= 16.0)

SELECT @overheadMemory = 1.0 /* Operating System minimum */

+ (@physicalMemory / 4.0);

END;

IF (@physicalMemory > 16.0)

SELECT @overheadMemory = 1.0 /* Operating System minimum */ + 4.0 /* add in reserved for <= 16GB */

+ ((@physicalMemory - 16.0) / 8.0);

END;

SELECT @overheadMemory = @overheadMemory + @threadStack;

DECLARE @editionId BIGINT = CAST(SERVERPROPERTY('EditionID') AS BIGINT);

DECLARE @enterprise BIT = 0;

DECLARE @developer BIT = 0;

DECLARE @override BIT = 0;

IF (@editionId IN ( 1804890536, 1872460670, 610778273 ))

SELECT @enterprise = 1;

END;

IF (@editionId = -2117995310)

SELECT @developer = 1;

END;

IF (@enterprise = 0 AND @developer = 0)

DECLARE @ProductVersion INT = CONVERT(INT, (@@MICROSOFTVERSION / 0x1000000) & 0xff);

IF (@ProductVersion >= 11)

AND (@physicalMemory > 128)

BEGIN

SELECT @overheadMemory = 1.0 + 4.0 + ((128 - 16.0) / 8.0);

-- Set the memory value to the max allowed, if there is enough headroom

IF (@physicalMemory - @overheadMemory >= 128)

SELECT @recommendedMemory = 128,

@overheadMemory = 0,

@override = 1;

END;

IF (@ProductVersion < 11)

AND (@physicalMemory > 64)

BEGIN

SELECT @overheadMemory = 1.0 + 4.0 + ((64 - 16.0) / 8.0);

-- Set the memory value to the max allowed, if there is enough headroom

IF (@physicalMemory - @overheadMemory >= 64)

SELECT @recommendedMemory = 64,

@overheadMemory = 0,

@override = 1;

END;

END;

IF (@override = 0)

SELECT @recommendedMemory = @physicalMemory - @overheadMemory;

END;

IF (@configureNumaNodeAffinity = 1)

SELECT @recommendedMemory = (@recommendedMemory / @numaNodes) * @numaNodesAfinned;

END;

SELECT @@VERSION AS [Version],

CASE

WHEN (@enterprise = 1) THEN

'Enterprise Edition'

WHEN (@developer = 1) THEN

'Developer Edition'

ELSE

'Non-Enterprise Edition'

END AS [Edition],

CAST(@physicalMemorySource AS INT) AS [Physical RAM (MB)],

[c].[value] AS [Configured Value (MB)],

[c].[value_in_use] AS [Running Value (MB)],

CAST(@recommendedMemory * 1024 AS INT) AS [Recommended Value (MB)],

N'EXEC sp_configure ''show advanced options'', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure ''max server memory (MB)'', '

+ CAST(CAST(@recommendedMemory * 1024 AS INT) AS NVARCHAR(20))

+ '; EXEC sp_configure ''show advanced options'', 0; RECONFIGURE WITH OVERRIDE;' AS [Script]

FROM [sys].[configurations] AS [c]

WHERE [c].[name] = N'max server memory (MB)'

OPTION (RECOMPILE);