当我在没有OPTION (RECOMPILE). 如果不重新编译，运行需要 3-4 分钟，重新编译需要大约 15-20 秒。
我无法更改查询，我已经更新了所有统计信息并重建了所有索引。只有 1 个索引建议，改进了 9.4%。
我努力了：
-- clear all plans in cache
DBCC FREEPROCCACHE
-- Clear Buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
我还能做什么？我无法编辑查询，因此OPTION (RECOMPILE)对我来说不是有效的解决方案。查询可能会不时发生一些变化，因此“计划指南”不起作用？
请注意，这不是实际查询。实际查询由 Dynamics AX 执行，因此使用api_cursor. 我从中撬出了这个sp_cursorprepare并手动填写了参数。
DECLARE @p1 AS BIGINT = 5637144576
DECLARE @p2 AS NVARCHAR(32) = N'1003'
DECLARE @p3 AS NVARCHAR(32) = N'posp%'
DECLARE @p4 AS BIGINT = 5637144576
DECLARE @p5 AS NVARCHAR(32) = N'sv'
DECLARE @p6 AS NVARCHAR(32) = N'posp'
DECLARE @p7 AS BIGINT = 5637144576
DECLARE @p8 AS NVARCHAR(32) = N'1003'
DECLARE @p9 AS NVARCHAR(32) = N'posp'
DECLARE @p10 AS BIGINT = 5637144576
DECLARE @p11 AS NVARCHAR(32) = N'posp'
DECLARE @p12 AS BIGINT = 5637144576
DECLARE @p13 AS NVARCHAR(32) = N'1003'
DECLARE @p14 AS BIGINT = 5637144576
DECLARE @p15 AS NVARCHAR(32) = N''
DECLARE @p16 AS NVARCHAR(32) = N'sv'
DECLARE @p17 AS INT = 0
DECLARE @p18 AS INT = 0
DECLARE @p19 AS INT = 0
DECLARE @p20 AS INT = 0
DECLARE @p21 AS BIGINT = 5637144576
DECLARE @p22 AS NVARCHAR(32) = N'1003'
DECLARE @p23 AS INT = 2
DECLARE @p24 AS BIGINT = 5637144576
DECLARE @p25 AS NVARCHAR(32) = N'1003'
DECLARE @p26 AS BIGINT = 5637144576
DECLARE @p27 AS INT = 1
DECLARE @p28 AS INT = 101
select @p1, @p2, @p5, @p6, @p7
SELECT *
FROM (
SELECT Row_number() OVER(ORDER BY t2.NAME,t2.itemid) AS rownumber,
t2.itemid AS f1,
t2.product AS f2,
t2.NAME AS f3,
t2.recid AS f4,
t3.unitid AS f5,
t3.recid AS f6,
t4.recid AS f7,
t5.recid AS f8,
t6.recid AS f9,
t7.recid AS f10,
t7.instancerelationtype AS f11
FROM retailitemname T2
CROSS JOIN inventtablemodule T3
LEFT OUTER JOIN inventtable T4
ON (((
t4.partition=@P1)
AND (
t4.dataareaid=@P2))
AND ((
t4.itemid LIKE @P3 ESCAPE '\' )
AND (
t2.itemid=t4.itemid)))
LEFT OUTER JOIN ecoresproducttranslation t5
ON ((
t5.partition=@P4)
AND (((
t5.languageid=@P5)
AND (
FREETEXT(t5.NAME,@P6)))
AND (
t2.product=t5.product)))
LEFT OUTER JOIN inventtable t6
ON (((
t6.partition=@P7)
AND (
t6.dataareaid=@P8))
AND ((
FREETEXT(t6.namealias,@P9))
AND (
t2.itemid=t6.itemid)))
LEFT OUTER JOIN ecoresproduct t7
ON ((
t7.partition=@P10)
AND ((
FREETEXT(t7.searchname,@P11))
AND (
t2.product=t7.recid)))
WHERE ((((
t2.partition=@P12)
AND (
t2.dataareaid=@P13))
AND ((
t2.partition#2=@P14)
OR (
t2.partition#2 IS NULL)))
AND (((
t2.languageid=@P15)
OR (
t2.languageid=@P16))
AND ((((
t4.recid<>@P17)
OR (
t5.recid<>@P18))
OR (
t6.recid<>@P19))
OR (
t7.recid<>@P20))))
AND (((
t3.partition=@P21)
AND (
t3.dataareaid=@P22))
AND ((
t3.moduletype=@P23)
AND (
t2.itemid=t3.itemid)))
AND EXISTS
(
SELECT 'x'
FROM retailitemcategory t8
WHERE ((((
t8.partition=@P24)
AND (
t8.dataareaid=@P25))
AND (
t8.partition#2=@P26))
AND (
t2.itemid=t8.itemid))) )t1
WHERE ((
t1.rownumber>=@P27)
AND (
t1.rownumber<@P28))
OPTION (RECOMPILE)
计划
再次提醒：请注意这些是我创建的查询的计划，不一定是应用程序实际使用游标时使用的计划。
版本：
微软 SQL Server 2014 - 12.0.2000.8 (X64) 2014 年 2 月 20 日 20:04:26 版权所有 (c) 微软公司 Windows NT 6.1（内部版本 7601：Service Pack 1）（管理程序）上的开发人员版（64 位）
MAXDOP设置为 1，因为它是 AX 2012 (R3 CU10) 数据库。我们不希望任何大报告或作业阻塞所有处理器。不知道您可以在计划中看到更多索引建议。但是慢的只有一个建议而快的有 2.MAXDOP 0速度更快但仍然不如重新编译那么快。
当您通过实时服务在 AX 中搜索渠道数据库中不存在的产品时，AX 查询将运行。此搜索包含在 AX 2012 R3 零售版中。没有定制。
目的是优化 SQL Server 上的进程。该查询完全按照它应该做的去做，但如果不重新编译，它就会太慢。它使用了错误的查询计划。如果它使用正确的查询计划，它会很快。所以应该不需要在 AX 中做任何更改。
optimize for ad hoc workloads被激活。
我不确定零售组件是如何工作的，但既然你说你从 cursorprepare 中捕获它并重写它，你似乎暗示它无论如何都是从 AOS 发送的。
我建议你做的一件事是看看它是否有助于执行
然后重新启动中间层服务器。
通常在 AX 环境中（正如您在评论中所解释的那样），公司之间的数据分布存在很大差异，并且在包含数万条记录的公司中执行的查询中重复使用零售中包含 0 条记录的公司的计划是会遇到严重的参数嗅探问题。
请查看SQL Server Parameter Sniffing with Dynamics AX，了解更多详细信息。
请注意，我不确定此设置是否会影响零售组件，但我希望它会，因为您在评论中指出查询实际上是由 AOS 执行的，但该设置可能对您的 Dynamics 安装作为一个整体有所帮助。
您声明不应该有任何理由更改 AX 中的任何内容，但很可能会有，因为您还声明查询在重新编译时运行良好。
链接文章中的相关要点：
如果这没有帮助，您应该查看查询来自的 AX 代码。请通读如何在 AX 中监视长时间运行的查询中的说明，以了解有关如何查找发起查询的调用堆栈的说明。一旦知道查询在代码中的位置，您就可以（或让开发人员）添加 forceliterals 关键字（同样是文档）以禁用参数并填写实际值，以便为每个参数组合编译执行计划。这应该
OPTION (RECOMPILE)非常相似，除了它不会为完全相同的值重新编译。关于您的
MAXDOP评论，这不会有太大区别，因为 AX 创建了一个不支持并行性FAST_FORWARD的游标，正如 Paul white 在这个对我的问题的回答中所解释的那样。