SQL SERVER – Memory Settings Formula (Best Practice)

Here are the formulas for setting memory in SQL Server.

• Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).

• Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).

Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).

SELECT
	SERVERPROPERTY('InstanceName') AS myinstance,
	*
FROM	
	sys.configurations
WHERE name IN
	(
		'max server memory (MB)',
		'min server memory (MB)'
	)
GO

Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment

SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info

Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO

Cheers!