調整記憶體設定

建議您為執行 JD Edwards 工作負載的 SQL Server 資料庫設定預設記憶體值。其中包含：

• 設定最大和最小記憶體設定

• 鎖定記憶體中的頁面

設定最大和最小記憶體

設定 SQL Server 資料庫的最大記憶體可確保作業系統和其他程序有足夠的記憶體來執行其動作，而無需分頁到磁碟。設定最大和最小記憶體可防止安裝在相同 EC2 執行個體上的多個 SQL Server 執行個體彼此耗盡記憶體。

可以使用下列指令碼，透過保守值自動設定最大和最小設定。此指令碼會為作業系統保留 1 GB，而在 16 GB 以下的 25% 記憶體和 12.5% 的剩餘記憶體做為額外負荷。SQL Server 最小記憶體設定為最大記憶體的一半。該指令碼假設您在 EC2 執行個體上已安裝單一 SQL Server 資料庫。

DECLARE @OSMemoryTotalKB bigint; 
DECLARE @OSMemoryUnder16GB bigint; 
DECLARE @OSMemoryOver16GB bigint; 
DECLARE @OSOverhead bigint; 
DECLARE @MemoryOverheadLower bigint; 
DECLARE @MemoryOverheadUpper bigint;
DECLARE @MemoryOverheadTotal bigint; 
DECLARE @SQLMaxMemory int; 
DECLARE @SQLMinMemory int;

-- Find how much memory is available on the OS 
SELECT @OSMemoryTotalKB = total_physical_memory_kb from sys.dm_os_sys_memory; 
SET @OSMemoryUnder16GB = IIF(@OSMemoryTotalKB>16777216, 16777216, @OSMemoryTotalKB); 
SET @OSMemoryOver16GB = IIF(@OSMemoryTotalKB>16777216, @OSMemoryTotalKB-16777216, 0);

-- Calculate overhead for the OS 
SET @OSOverhead= 1048576; -- static 1GB reservation

-- Calculate overhead for managing memory 
SET @MemoryOverheadLower = @OSMemoryUnder16GB/4; --reserve 25% of memory under 16GB for overhead 
SET @MemoryOverheadUpper = @OSMemoryOver16GB/8; -- reserve 12.5% of memory over 16GB for overhead 
SET @MemoryOverheadTotal = @OSOverhead + @MemoryOverheadLower + @MemoryOverheadUpper;

-- Calculate remaining memory available for SQL 
SET @SQLMaxMemory = (@OSMemoryTotalKB-@MemoryOverheadTotal)/1024; 
SET @SQLMinMemory = @SQLMaxMemory/2; -- set minimum to half of maximum

Print N'Total Server memory (KB): ' + CAST(@OSMemoryTotalKB as NVARCHAR); 
Print N'Memory Overhead for OS Overhead (KB): ' + CAST(@OSOverhead as NVARCHAR); 
Print N'Memory Overhead for management of lower 16GB (KB): ' + CAST(@MemoryOverheadLower as NVARCHAR); 
Print N'Memory Overhead for management of over 16GB (KB): ' + CAST(@MemoryOverheadUpper as NVARCHAR); 
Print N'Memory Overhead Total: ' + CAST(@MemoryOverheadTotal as NVARCHAR); 
Print N'SQL Minimum Memory (MB): ' + CAST(@SQLMinMemory as NVARCHAR); 
Print N'SQL Maximum Memory (MB): ' + CAST(@SQLMaxMemory as NVARCHAR);

EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE; 
EXEC sp_configure 'min server memory', @SQLMinMemory 
RECONFIGURE; 
EXEC sp_configure 'max server memory', @SQLMaxMemory; 
RECONFIGURE;

鎖定記憶體中的頁面

為了確保 EnterpriseOne SQL Server 資料庫所使用記憶體的穩定性，建議您鎖定記憶體中的頁面。請遵循在 Amazon EC2 上部署 SQL Server 的最佳實務指南來完成此組態。