SQL Server 存储过程减少授予的内存量

Question

执行计划下载链接:https://www.dropbox.com/s/3spvo46541bf6p1/Execution%20plan.xml?dl=0

我正在使用 SQL Server 2008 R2

我有一个非常复杂的存储过程，它在执行时请求太多内存。下面是执行计划的截图:

http://s15.postimg.org/58ycuhyob/image.png

基础查询可能需要大量调整，如大量估计行所示，但这不是重点。无论查询的复杂程度如何，都不应该请求 3 GB 内存 执行时。

我如何防止这种行为？ 我尝试了以下方法:

DBCC FREEPROCCACHE 清除计划缓存。这没有任何成就。

在 SP 和 SQL 级别设置 RECOMPILE 选项。同样，这没有任何作用。

使用 MAXDOP 选项，从 0 到 8。同样的问题。

该查询平均返回大约 1k 行，并且它确实查看了一个包含超过 300 万行的表，其中大约有 4 个表被连接。在大多数情况下，执行查询会在不到 3 秒的时间内返回结果。

编辑:

还有一件事，在这种情况下使用查询提示并不真正可行，因为我们的情况下参数变化很大。

编辑2:

应要求上传执行计划

编辑3:

我试过重建/重组碎片索引。显然，很少，但没有什么太严重的。无论如何，这并没有减少授予的内存量，也没有减少估计的行数(如果这有某种关联的话)。

Answer 1

你说优化查询不是重点，但实际上它只是重点。执行查询时，SQL Server 会在生成执行计划后保留执行查询所需的内存。估计中间结果保存的行越多，估计需要的内存就越多。

因此，重写您的查询和/或创建新索引以获得合适的查询计划。快速浏览一下查询计划会显示一些没有连接谓词的嵌套循环和一些可能只使用了少数记录的表扫描。