我们在azure中有一个分区表（[advertisementunits]），我们正在对其运行查询。我们经常遇到qo不使用覆盖索引的问题。
例子：
declare @__param_0 datetimeoffset(7) = '2019-06-24'
declare @__param_1 datetimeoffset(7) = '2019-06-25'
declare @__p_3 int = '0'
declare @__p_4 int = '1000'
SELECT [a].[Id],[a].[Created], [a].[EarliestStart], etc...
FROM [AdvertisementUnits] AS [a]
WHERE ( ([a].[EarliestStart] >= @__param_0) AND ([a].[EarliestStart] < @__param_1))
ORDER BY [a].[Id]
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY存在覆盖查询：
CREATE NONCLUSTERED INDEX [IX_AdvertisementUnits_EarliestStart_FullInclude] ON [dbo].[AdvertisementUnits]
(
[EarliestStart] DESC
)
INCLUDE([a].[Created], [a].[EarliestStart], etc...)如果我运行这个查询，大约需要300万。如果我对覆盖索引使用查询提示，它小于3s。
慢速查询计划
超快速查询计划
我所做的：
“用fullscan更新statistics广告单元，全部”->没有改进
“alter database scoped configuration clear procedure\u cache；”->没有改善
添加了“选项（重新编译）”->使用索引
删除偏移量->使用索引
使用order by a.earlieststart->索引
真正让我恼火的是，如果没有选项重新编译，qo甚至在更新统计信息和清空查询缓存之后也拒绝使用索引。
我不能在这里使用optionrecompile，因为我们使用ef-core，为一个特定的查询注入它是非常不切实际的。
编辑：增加混乱。。。
如果使用文本值而不是变量，则每次都会生成快速查询计划：
SELECT [a].[Id],[a].[Created], [a].[EarliestStart]
FROM [AdvertisementUnits] AS [a]
WHERE ([a].[EarliestStart] >= '2019-06-14') AND ([a].[EarliestStart] < '2019-06-15')
ORDER BY [a].[Id]
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
2条答案
按热度按时间pxy2qtax1#
我对这个问题还不太了解，但我认为现在的情况是这样的：
查询优化器查看查询，发现有from/through变量要应用于列
EarliestStart，检查列上的统计信息，不“嗅探”变量（这不是一个存储过程），并且，在不了解变量的情况下，读取整个表是最安全的操作，以避免过多的书签查找。当包含with（重新编译）时，它可能会考虑这次使用的实际值，实现（通过表统计）索引扫描+书签查找优于表扫描，并相应地进行。
（抱歉，我不知道为什么缺少order by和offset命令会产生更好的查询。实际操作会产生更多的信息。）
如果您能以某种方式将查询与重新编译连接起来，那么这似乎是一种方法——您可以在查询编译方面获得成功，但这是一个微不足道的查询，不应该花费那么多时间，而且总体而言，它优于连续表扫描。
axzmvihb2#
由于这些响应，我在文档中找到了解释为什么使用“option（recompile）”执行带有局部变量的查询时性能更好的部分。
从官方文件重新编译：
但是，除了使用过程的当前参数值之外，在编译语句时，重新编译查询提示还使用存储过程中任何局部变量的值。
这似乎与忽略局部变量的正常执行不同。
sqlshack.com上的ed pollack
因为局部变量的值在运行时之前是未知的，所以查询优化器需要在执行之前对行数进行非常粗略的估计。
这可以解释观察到的行为。
解决这个问题的唯一方法是：
文字值（不是一个好选项）
存储过程
使用“选项（重新编译）”
如果使用ef-core，唯一的选择就是为某些慢查询注入重新编译
我的个人问题：
我从“azureperformanceinsight”中获取了一些慢查询，并使用局部变量来测试它们。由于上面描述的“特性”，这导致了不同于预期的查询计划。efcore使用“参数化查询”。它的性能类似于存储过程。所以最终覆盖索引实际上一直在prod上使用，只是没有被我的本地错误查询使用。
其他来源：
jes schultz-sqlserver中的参数嗅探
gailshaw-如何混淆sqlserver查询优化器