SQL Server 2019内存优化TempDB元数据引发内存不足（错误701）排查求助

一、排查TempDB内存高消耗根源的核心脚本 #

• 查询内存优化TempDB元数据的内存使用明细：

SELECT 
    object_name = OBJECT_NAME(object_id),
    memory_used_mb = (memory_used_by_table_kb + memory_used_by_indexes_kb) / 1024.0,
    rows_count = rows,
    object_type = type_desc
FROM sys.dm_db_xtp_table_memory_stats
WHERE database_id = DB_ID('tempdb');

• 查看XTP资源池内存压力与分配情况：

SELECT 
    pool_id,
    name AS pool_name,
    memory_utilization_percentage,
    target_memory_mb = target_memory_kb / 1024,
    used_memory_mb = used_memory_kb / 1024,
    max_memory_mb = max_memory_kb / 1024
FROM sys.dm_resource_governor_resource_pools
WHERE name IN ('default', 'XTP_Pool'); -- 覆盖默认池与可能的专用XTP池

-- 查看XTP内存分配失败详情
SELECT 
    allocation_type,
    failure_count,
    total_allocation_attempts
FROM sys.dm_xtp_memory_consumers
WHERE database_id = DB_ID('tempdb');

• 识别占用TempDB内存的会话与执行语句：

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    executing_sql = t.text,
    tempdb_memory_mb = (SUM(us.user_objects_alloc_page_count + us.internal_objects_alloc_page_count) * 8) / 1024.0
FROM sys.dm_db_session_space_usage us
JOIN sys.dm_exec_sessions s ON us.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE us.session_id <> @@SPID
GROUP BY s.session_id, s.login_name, s.host_name, t.text
HAVING SUM(us.user_objects_alloc_page_count + us.internal_objects_alloc_page_count) > 0
ORDER BY tempdb_memory_mb DESC;

二、检测长事务（XTP垃圾回收阻塞根源） #

长事务会阻止XTP垃圾回收释放内存，以下脚本定位长时间运行的事务，尤其是涉及内存优化对象的：

-- 定位所有长时运行的用户事务
SELECT 
    st.session_id,
    at.transaction_id,
    transaction_duration_min = DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()),
    transaction_state = CASE at.transaction_state
        WHEN 0 THEN '未初始化'
        WHEN 1 THEN '已初始化但未启动'
        WHEN 2 THEN '活跃'
        WHEN 3 THEN '已结束（只读事务）'
        WHEN 4 THEN '已提交'
        WHEN 5 THEN '正在回滚'
        WHEN 6 THEN '已回滚'
    END,
    associated_sql = t.text
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE at.transaction_type = 1 -- 筛选用户事务
AND DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) > 5 -- 运行超过5分钟的事务
ORDER BY transaction_duration_min DESC;

-- 精准定位涉及内存优化表的活跃长事务
SELECT 
    session_id,
    transaction_id,
    object_name = OBJECT_NAME(object_id),
    transaction_begin_time,
    transaction_duration_min = DATEDIFF(MINUTE, transaction_begin_time, GETDATE())
FROM sys.dm_xtp_transactions
WHERE transaction_state = 2 -- 活跃事务
AND DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) > 5
ORDER BY transaction_duration_min DESC;

三、根治问题的最佳实践 #

代码层面优化 #

• 避免使用长时间运行的显式事务，尽量缩短事务生命周期，尤其是涉及内存优化TempDB对象的操作；
• 减少不必要的临时表、表变量创建，使用内存优化表变量时仅在高并发场景下使用，避免滥用；
• 批量操作后及时提交事务，避免内存优化对象的数据长期滞留内存；
• 检查代码中是否存在未关闭的游标、隐式事务升级情况。

服务器设置调整 #

• 精细化配置资源调控器：为XTP创建专用资源池，避免抢占default池内存：

-- 创建XTP专用资源池（建议占总内存40%-50%，根据实际情况调整）
CREATE RESOURCE POOL XTP_Pool WITH (MAX_MEMORY_PERCENT = 45);
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 将TempDB的内存优化对象绑定到专用池
ALTER DATABASE tempdb SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ALTER DATABASE tempdb MODIFY FILEGROUP [MEMORY_OPTIMIZED_DATA] USING (MEMORY_POOL = XTP_Pool);

• 优化TempDB文件配置：数据文件数量与CPU核心数匹配（最多8个），设置相同的初始大小和自动增长值，避免碎片化；
• 升级至SQL Server 2019 CU5及以上版本：该版本优化了XTP垃圾回收的触发逻辑，提升内存释放效率；
• 合理设置实例最大内存：通过sp_configure 'max server memory (MB)'限制SQL Server内存占用，预留足够内存给操作系统。

四、冷门设置与隐藏参数 #

• 跟踪标记1236：启动时添加-T1236可优化内存优化表的垃圾回收效率，减少内存滞留；
• sys.sp_xtp_checkpoint_force_garbage_collection：相较于sys.sp_xtp_force_gc，该存储过程可更彻底清理XTP内存中的checkpoint相关垃圾，适合周期性执行；
• 限制内存优化元数据内存增长：通过修改数据库设置限制内存优化元数据的内存使用（需评估业务场景后操作）。

内容的提问来源于stack exchange，提问作者Orlandis