/*
derived from http://sqlmax.chuvash.eu/

SQL Max Memory = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * CEILING(NumOfCores/4)) - OS Reserved
NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB
*/
DECLARE @totSysMemKb INT;
DECLARE @NumOfCores INT; 
DECLARE @NumOfProcessors INT; 
DECLARE @NumOfSQLThreads INT; 
DECLARE @isX64 BIT = 1;
DECLARE @ThreadStackSize INT; 
DECLARE @osReserved INT; 
DECLARE @newMaxMem INT; 
DECLARE @appReserved INT = 4*1024*1024; --memory reserved for other apps on the server in Kb

SELECT @totSysMemKb = [total_physical_memory_kb]
FROM [sys].[dm_os_sys_memory];

SELECT @NumOfCores = [cpu_count], @NumOfProcessors = [cpu_count] / [hyperthread_ratio]
FROM [sys].[dm_os_sys_info];

SELECT @isX64 = CHARINDEX('64-bit',CAST(SERVERPROPERTY('Edition') AS VARCHAR(MAX)));

SELECT @ThreadStackSize = CASE WHEN @isX64=1 THEN 4096 ELSE 2048 END;

--force 16 Go, 2 vcpu, 2 cores 
SELECT @totSysMemKb = 16 * 1024 * 1024, @NumOfProcessors = 2, @NumOfCores = 2

SELECT @NumOfSQLThreads = 256 + (@NumOfProcessors - 4) * 8 * (CASE WHEN @NumOfProcessors >4 THEN @NumOfProcessors ELSE 0 END);


SELECT @osReserved = CASE WHEN @totSysMemKb < (20*1024*1024) THEN @totSysMemKb * 0.2 ELSE @totSysMemKb * 0.125 END;

PRINT'
@totSysMemKb:       '+COALESCE(CAST(@totSysMemKb AS VARCHAR(MAX)),'null')+'
@NumOfCores:        '+COALESCE(CAST(@NumOfCores AS VARCHAR(MAX)),'null')+'
@NumOfProcessors:   '+COALESCE(CAST(@NumOfProcessors AS VARCHAR(MAX)),'null')+'
@NumOfSQLThreads:   '+COALESCE(CAST(@NumOfSQLThreads AS VARCHAR(MAX)),'null')+'
@isX64:             '+COALESCE(CAST(@isX64 AS VARCHAR(MAX)),'null')+'
@ThreadStackSize:   '+COALESCE(CAST(@ThreadStackSize AS VARCHAR(MAX)),'null')+'
@osReserved:        '+COALESCE(CAST(@osReserved AS VARCHAR(MAX)),'null')+'
@appReserved:       '+COALESCE(CAST(@appReserved AS VARCHAR(MAX)),'null')+'
';
SELECT @newMaxMem = @totSysMemKb - (@NumOfSQLThreads * @ThreadStackSize) - ((1* 1024 * 1024) * CEILING(@NumOfCores/4)) - @osReserved - @appReserved;

SELECT 
    @newMaxMem AS maxMemKb
    , @newMaxMem / 1024 AS maxMemMb
    , @newMaxMem / 1024 /1024 AS maxMemGb
;