/* 23.06.2021 RTC TFS 65413 Optimize RAM configuration */
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'min server memory (MB)', N'1024'
GO

DECLARE @maxMem INT;
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;

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


select @NumOfCores = 1, @NumOfProcessors = 8

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 @MaxMem = @newMaxMem / 1024

select @maxmem

EXEC sys.sp_configure N'max server memory (MB)', @maxMem
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE

UPDATE HCITools.dbo.HCI_PARAMS
SET HCIP_value = @MaxMem
WHERE HCIP_key = 'MAXSRVMEM'

GO