调整内存设置

我们建议您为运行 JD Edwards 工作负载的 SQL Server 数据库配置默认内存值。这些指令包括：

• 配置最大和最小内存设置

• 在内存中锁定页面

配置最大和最小内存

设置 SQL Server 数据库的最大内存，确保操作系统和其他进程有足够的内存来执行其操作，而无需分页到磁盘。设置最大和最小内存可以防止安装在同一实例上的多个 SQL Server EC2 实例互相缺少内存。

您可以使用以下脚本自动以保守值配置最大和最小设置。该脚本会为操作系统预留 1GB，并预留 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 数据库的内存的稳定性，我们建议您在内存中锁定页面。按照《在亚马逊上部署 SQL Server 的最佳实践 EC2》指南中的步骤完成此配置。