以下是 SQL Server 内存优化的详细方案，结合核心配置、监控手段和高级技术，提供脚本示例及配置建议。 内存优化作为sqlserver性能优化的方向之一，可以作为了解。
一、核心内存配置优化
设置内存上下限
目标：防止 SQL Server 占用全部系统内存，确保操作系统和其他应用正常运行。建议配置：
max server memory = RAM物理内存的 75%~80%（预留 20%~25% 给 OS 及其他进程）min server memory = RAM物理内存的 10%~20%（避免频繁内存收缩） 脚本示例：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 24576; -- 例如 24GB（适用于 32GB 内存服务器）
EXEC sp_configure 'min server memory (MB)', 4096; -- 最小 4GB
RECONFIGURE;
启用 AWE（大内存支持）
适用场景：32 位 SQL Server 需使用超过 4GB 内存时（64 位无需配置）。配置步骤：
EXEC sp_configure 'awe enabled', 1;
RECONFIGURE;
启用缓冲池扩展（Buffer Pool Extension）
-- 将缓冲池扩展至 SSD（需 SQL Server 2014+）
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'F:\SSD_Cache\BP_Extend.bpe', SIZE = 32 GB);
作用：将冷数据缓存至 SSD，减少物理 I/O 压力，提升读取性能。
二、缓冲池与缓存管理
监控关键内存指标
核心 DMV 查询：
-- 实时内存状态
SELECT
counter_name AS [指标],
cntr_value AS [值(KB)]
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Target Server Memory (KB)', -- 目标内存
'Total Server Memory (KB)', -- 已用内存
'Page life expectancy' -- 页生命周期（秒），>300 为佳
);
输出解读：Page life expectancy 过低（<60s）表明内存压力大，需优化或扩容。
清理缓存策略
场景：计划缓存过多或测试环境需重置状态时。脚本示例：
DBCC FREEPROCCACHE; -- 清除执行计划缓存
DBCC DROPCLEANBUFFERS; -- 清除数据缓存
DBCC FREESYSTEMCACHE('ALL'); -- 清除系统缓存
注意：生产环境谨慎使用，可能引发短期性能波动。
三、索引优化减少内存压力
定期维护索引碎片
脚本示例：
-- 检查碎片率（>30% 建议重建）
SELECT
index_id,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > 30;
参考我另一篇文章：Sql Server索引碎片
四、高级内存优化技术
内存优化表（In-Memory OLTP） 适用场景：高频读写表（如会话状态、实时交易）。 配置步骤：
--创建内存优化文件组：
ALTER DATABASE [DBName]
ADD FILEGROUP [MemOpt_FG] CONTAINS MEMORY_OPTIMIZED_DATA;
--创建容器
ALTER DATABASE [DBName]
ADD FILE (NAME='MemOpt_File', FILENAME='E:\work\develop\sqldata\test') --注意这里是文件夹
TO FILEGROUP [MemOpt_FG];
--创建内存优化表：
CREATE TABLE [dbo].[MemTable] (
ID INT PRIMARY KEY NONCLUSTERED,
Data NVARCHAR(100)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
--查看内存优化表
SELECT object_id,
OBJECT_SCHEMA_NAME(object_id) AS schema_name,
name AS table_name
FROM sys.tables
WHERE is_memory_optimized = 1;
USE [SalesDB];
GO
-- 查看文件组及文件
SELECT
fg.name AS FileGroupName,
f.name AS FileName,
f.physical_name AS FilePath
FROM sys.filegroups fg
LEFT JOIN sys.database_files f ON fg.data_space_id = f.data_space_id;
内存优化表适用于OLTP高频交易系统、实时分析与决策的系统、高并发与资源争用场景等。 目前我的感受下来，内存优化表（MOT），个人认为，一般不会用到。 首先，MOT针对的是高频读写的表，那么针对读，可以使用redis缓存提高查询性能，在进行更新时先更db在删cache。实在业务数据量巨大时，也可以考虑读写分离分库分表，redis 分片集群等。 MOT会把数据写入磁盘的，这样会很吃内存，并且，如果做了MOT还要保证DB和MOT的一致性，如果用了redis，就得保证MOT、redis、db三方的一致性。 鉴于上述，个人认为实在没啥必要使用内存优化表。当然这里仅限个人认为。
五、自动化维护与监控
定期内存巡检脚本
-- 检查内存分配状态
DBCC MEMORYSTATUS; -- 输出缓冲池、计划缓存等详细信息[4,8](@ref)
-- 监控内存等待事件
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%MEMORY%';
自动化索引维护计划使用 SQL Agent 定时执行索引重建任务（每周低峰期）。
六、内存优化临时表
启用内存优化临时表（SQL Server 2019+）
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
作用：将临时表自动提升为内存优化表，减少 TempDB I/O 压力。
⚠️ 关键注意事项
避免过度配置：max server memory 过高可能导致操作系统内存不足引发崩溃。监控 PLE（Page Life Expectancy）：持续低于 300 秒需扩容或优化查询。版本兼容性：内存优化表仅支持 SQL Server 2014 及以上版本。