max_server_memory.sql

/*
	Max Server Memory Calculator
	https://bornsql.ca/memory/
	Copyright (c) BornSQL.ca
	Written by Randolph West, released under the MIT License
	Last updated: 8 January 2022

	Based on an original algorithm by Jonathan Kehayias:
	https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

	Max Worker Thread Stack calculation based on Tiger Toolbox Maintenance Solution.
	Copyright (c) Microsoft Corporation. All rights reserved.
	https://github.com/Microsoft/tigertoolbox/tree/master/MaintenanceSolution

	SQL Server, on a standalone instance, requires the following reserved RAM for a server:
	- 1 GB of RAM for the OS
	- plus 1 GB for each 4 GB of RAM installed from 4 - 16 GB
	- plus 1 GB for every 8 GB RAM installed above 16 GB RAM

	Memory for the Thread Stack can also be taken into account:
	- 32-bit, reserve 512KB per thread * Max Worker Threads
	- 64-bit, reserve 2MB per thread * Max Worker Threads
	- 128-bit, reserve 4MB per thread * Max Worker Threads

	Thanks to @sqlEmt and @sqlstudent144 for testing.
	Thanks to the Tiger Team for version number and thread stack calculations.

v1.0 - 2016-08-19 - Initial release.

v1.1 - 2016-11-22 - Thread stack reservation; NUMA affinity; new version check.

v1.2 - 2018-09-07 - Removed reference to errant DMV.

v1.3 - 2020-03-17 - Happy St. Patrick's Day.

v1.4 - 2020-06-19 - Fixes to comments and formatting.

v1.5 - 2022-01-08 - Add debug mode.

*/

-- Set this to 1 if you want to configure NUMA Node Affinity
DECLARE @configureNumaNodeAffinity BIT = 0;

-- Set this to 1 if you want to specify a RAM amount
DECLARE @debug BIT = 0;

-- If @debug is set to 1, specify physical memory in MB here
-- For example, if you have 1.82 TB of RAM, use 1908408
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;

-- Get physical RAM on server, or if @debug is set to 1
-- use the value from @physicalMemoryInMb
IF @debug = 1
BEGIN
	SELECT @physicalMemorySource = @physicalMemoryInMb;
END;
ELSE
BEGIN
	-- 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;

-- Convert to nearest GB
SELECT @physicalMemory = CEILING(@physicalMemorySource / CAST(1024.0 AS DECIMAL(20, 4)));

IF (@physicalMemory <= 2.0)
BEGIN
	SELECT @overheadMemory = 0.5;
END;

IF (@physicalMemory > 2.0 AND @physicalMemory < 4.0)
BEGIN
	SELECT @overheadMemory = 2.0;
END;

IF (@physicalMemory >= 4.0 AND @physicalMemory <= 16.0)
BEGIN
	SELECT @overheadMemory = 1.0 /* Operating System minimum */
							 + (@physicalMemory / 4.0);
END;

IF (@physicalMemory > 16.0)
BEGIN
	SELECT @overheadMemory = 1.0 /* Operating System minimum */ + 4.0 /* add in reserved for <= 16GB */
							 + ((@physicalMemory - 16.0) / 8.0);
END;

-- Add in the Max Worker Threads Overhead
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 ))
BEGIN
	SELECT @enterprise = 1;
END;

IF (@editionId = -2117995310)
BEGIN
	SELECT @developer = 1;
END;

-- Check for Standard Edition Limitations
IF (@enterprise = 0 AND @developer = 0)
BEGIN
	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)
BEGIN
	SELECT @recommendedMemory = @physicalMemory - @overheadMemory;
END;

-- Configure NUMA Affinity
IF (@configureNumaNodeAffinity = 1)
BEGIN
	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);
GO