内存优化表须配独立Resource Pool并设DURABILITY=SCHEMA_ONLY以提升写入性能,禁用资源调控器、创建池、绑定数据库、离线重上线生效;不支持ALTER TABLE,索引仅限非主键列且最多8个。

内存优化表必须配独立 Resource Pool 才能扛住高频修改
直接建 MEMORY_OPTIMIZED = ON 表却没配资源治理,高频写入时大概率触发“内存不足”报错——这不是真缺内存,而是 SQL Server 默认把所有内存优化对象塞进同一个隐式资源池,写压力一上来就争抢、卡死、报错 Out of memory in memory-optimized table。
必须显式创建并绑定独立资源池,否则并发一上 50+ 就开始不稳定。
- 先禁用再重建 Resource Governor:
ALTER RESOURCE GOVERNOR DISABLE; GO,然后CREATE RESOURCE POOL CacheDbPool WITH (MAX_MEMORY_PERCENT = 30); GO - 绑定数据库到池:
EXEC sys.sp_xtp_bind_db_resource_pool @database_name = N'YourDB', @pool_name = N'CacheDbPool'; - 关键一步:数据库必须离线再上线才能生效 ——
ALTER DATABASE YourDB SET OFFLINE; ALTER DATABASE YourDB SET ONLINE;
DURABILITY = SCHEMA_ONLY 是高频临时数据的首选
如果数据只是缓存、会话状态、计数器这类不需持久化的场景,DURABILITY = SCHEMA_ONLY 能跳过日志写入和 checkpoint 开销,写入吞吐可提升 3–5 倍。但注意:SQL Server 重启后数据全丢,不能用于需要恢复的业务。
示例建表语句:
CREATE TABLE dbo.SessionState ( SessionId CHAR(32) NOT NULL PRIMARY KEY NONCLUSTERED, Data VARBINARY(8000), LastAccess DATETIME2 ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
- 不要在
DURABILITY = SCHEMA_ONLY表上建索引(它只支持主键,且主键必须是非聚集) - 避免在该表上执行大事务(如批量插入万级行),容易触发内部内存分配失败
- 如果需要部分持久化,改用
DURABILITY = SCHEMA_AND_DATA,但务必确认磁盘 I/O 能跟上 checkpoint 频率
非聚集索引在内存表里只能建在主键之外的列上
内存优化表不支持聚集索引,所有索引都是非聚集的 Bw-tree 结构。你不能像普通表那样对任意列随意建索引,必须满足两个硬约束:
- 主键必须定义为
NONCLUSTERED,且是唯一索引(即使你没写UNIQUE,系统也强制) - 额外索引只能建在非主键列上,且最多 8 个;每列只能出现在一个索引中(不支持包含列
INCLUDE) - 建索引会立即占用内存,高频修改场景下,每个索引都增加锁粒度和版本链开销,建议只建真正用于
WHERE或JOIN的那 1–2 个
错误写法:CREATE INDEX idx_on_key ON dbo.CacheTable ([Key]) —— 如果 [Key] 已是主键列,这条命令会静默失败(不报错但不生效)。
ALTER TABLE 对内存优化表基本不可用
想加列、改类型、加索引?别试 ALTER TABLE ... ADD COLUMN —— 内存优化表不支持在线结构变更。所有 DDL 操作都会导致表重建,期间整个表被锁死,高频写入服务直接中断。
可行路径只有两条:
- 新建兼容结构的表 → 用
INSERT INTO ... SELECT迁移数据 → 重命名切换(需应用层配合停写窗口) - 用
SCHEMA_ONLY表做双写缓冲:新旧结构并存,由应用控制读写路由,逐步灰度迁移
最易被忽略的一点:哪怕只是给 SCHEMA_AND_DATA 表加一个非空默认值列,也会触发全量数据重写和 checkpoint,高峰期务必避开。










