sqlserver性能优化之内存优化详解

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;

exec sp_configure 'awe enabled', 1;
reconfigure;

-- 将缓冲池扩展至 ssd（需 sql server 2014+）
alter server configuration 
set buffer pool extension on (filename = 'f:\ssd_cache\bp_extend.bpe', size = 32 gb);

-- 实时内存状态
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 为佳
);

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;

--创建内存优化文件组：
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;

-- 检查内存分配状态
dbcc memorystatus;  -- 输出缓冲池、计划缓存等详细信息[4,8](@ref)

-- 监控内存等待事件
select * from sys.dm_os_wait_stats 
where wait_type like '%memory%';

alter database current set memory_optimized_elevate_to_snapshot = on;