同一存储过程2016比2008消耗内存多

Question

同一个存储过程在sqlserver 2016比sqlserver 2008运行时，查到消耗内存多；这是什么原因导致的？

Answer 1

引自免费微信小程序：皆我百晓生

面对SQL Server 2016与2008在运行同一存储过程时内存消耗差异的问题，有几点可能的原因和排查步骤可以考虑：

1. 执行计划优化：SQL Server 2016对查询优化器进行了多项改进，这可能导致了不同的执行计划。新的执行计划可能在某些场景下更高效，但在特定情况下也可能消耗更多资源。检查并对比两个版本的执行计划，可以通过SET SHOWPLAN_ALL ON;然后执行存储过程，查看生成的XML执行计划。

2. 内存分配机制：SQL Server 2016在内存管理上做了增强，例如引入了RowStore和ColumnStore索引，这些变化可能影响到内存的使用模式。确保没有无意中启用了不适合工作负载的特性。

3. 编译成本：SQL Server 2016在编译存储过程时可能会使用更多的内存。这通常发生在复杂的查询或大表操作中。可以通过DBCC DROPCLEANBUFFERS清除缓存后再次运行存储过程，观察内存使用情况。

4. 资源调配：检查服务器配置和系统设置，如最大内存限制（max server memory）是否在两个环境中一致。不一致的配置会导致明显的性能差异。

5. 并行处理：SQL Server 2016支持更高级的并行处理，这可能在多核处理器上导致更高的内存消耗。通过SET STATISTICS IO, TIME ON;可以查看并行度信息。

6. 临时对象：检查是否有过多的临时表或表变量被创建，尤其是在并行查询中，每个线程可能都会创建自己的副本，从而增加内存使用。

7. 统计信息更新：确保统计信息在两个环境中都是最新的，过时的统计信息可能导致不理想的执行计划。

排查步骤

• 收集执行计划：使用SET SHOWPLAN_ALL ON;收集执行计划，并对比。
• 监控资源使用：使用sp_whoisactive等工具监控资源使用情况，了解具体是CPU、I/O还是内存瓶颈。
• 调整配置：根据需求调整服务器配置，如最大内存限制。
• 优化查询：分析执行计划，寻找优化点，如添加索引、修改查询逻辑等。

示例代码

-- 收集执行计划
SET SHOWPLAN_ALL ON;
EXEC YourStoredProcedure;

-- 监控活动会话
SELECT * FROM sp_whoisactive;

若以上步骤未能解决问题，进一步的诊断可能需要深入到操作系统层面或硬件配置差异，确保测试环境尽可能接近生产环境。