我有一张包含 10 年“包裹扫描”的表格。有人扫描一个包裹，它会记录日期和用户名。现在让我们假设保留 10 年的数据实际上是有目的的。
我有一个页面显示过去一周的摘要，所以很明显我只想阅读 1 周的数据。
这是要在 SSMS 中运行两次的查询，一次使用硬编码的最近日期，另一次使用2013 中的旧日期。它最初是一个参数化查询，但在 SSMS 中我用@p0日期替换：
SELECT [t0].[VerifyDate], [t0].[PackageId], [t0].[Username]
FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]
WHERE ([t1].[PackageStatus] <> 99) AND ([t0].[VerifyDate] > @p0)
ORDER BY [t0].[VerifyDate] DESC
在我执行它之前，我想介绍一下我的日期索引。
现在我的日期索引不在我的PackageVerification桌子上，而是在一个“帮助视图”上，它执行上面看到的相同连接。上面的查询能够神奇地使用这个索引视图，因为我启用了 SCHEMABINDING。
CREATE NONCLUSTERED INDEX [IX_Helper_PackageVerification_USER_SCAN_HISTORY] ON [dbo].[Helper_PackageVerification]
(
[VerifyDate] DESC,
[PackageStatus] ASC
)
INCLUDE (
[VerifyDateDate],
[Username]
)
当我在 SSMS 中使用旧日期和新日期运行查询时，它会按预期使用扫描或搜索。阈值似乎在 2015 年左右。所以任何最近的东西都绝对应该使用搜索。这是结果：
当我从我的应用程序将它作为参数化查询运行时，我总是得到一个完整的扫描，由于某种原因它使用了一个并行计划。
至少它使用了我的辅助索引。
我实际上不确定为什么我没有对此进行参数嗅探。我总是通过一个最近的日期，所以我认为它可能更喜欢扫描，但考虑到情况，我可以选择上述计划。有一百万多行，大约需要 150 毫秒。
顺便说一下，这是一个带有 2vCores 的 SQL Azure 数据库。参数嗅探已启用，参数化设置为simple。
如果我更改查询并使用我运行我的应用程序，OPTION (RECOMPILE)我确实获得了所需的SEEK和仅几毫秒的非常好的性能。重新编译时间似乎可以忽略不计，坦率地说，这是我可以使用的非常好的性能。
当我查看查询存储时，我可以验证 OPTION RECOMPILE 使用查找最近的日期，并扫描旧日期！惊人的。
但是，我以前从未尝试过这个 - 我想如何进一步改进它OPTION (OPTIMIZE FOR @p0 = '4/1/2021')。
我希望这也可以使用搜索，但不需要每次都重新编译。我只是定期更改传递给 OPTIMIZE FOR 的日期 - 可能是上个月的开始。
但是，这是查询存储中的查询。
当将日期参数设置为 21 年 4 月 7 日时，它会全面扫描所有 1+ 百万行！
所以现在我迷路了。我已尝试阅读有关该主题的所有内容，但没有遇到此问题。RECOMPILE 有效，但是当我期望 OPTIMIZE FOR 有效地模拟使用硬编码值在 SSMS 中运行查询时，它似乎没有做任何事情。
查询计划
这第一个计划是唯一出乎意料的计划——它是一个扫描，我想要一个搜索。
优化@p1 = '2021/4/1' - https://www.brentozar.com/pastetheplan/?id=H1JB43AUu 优化两个参数 - https://www.brentozar.com/pastetheplan/?id=rkV9U3AUu 选项重新编译 - https://www.brentozar.com/pastetheplan/?id=SJ5cS3CUd
这些是为了证明优化器知道最近的日期应该是一个搜索！
硬编码 2013 - 扫描 - https://www.brentozar.com/pastetheplan/?id=BkeA42RLu 硬编码 2015 - 搜索 - https://www.brentozar.com/pastetheplan/?id=S1c8r3R8O
我开始怀疑这个版本是否不支持 OPTIMIZE FOR，即使我找不到任何说它不支持的东西
编辑：（在保罗的回答之后）
我尝试了一些额外的东西。首先是我之前没有包含的 VIEW 定义。这是一个 JOIN 并且因为它使用 SCHEMABINDING 优化器能够代替它：
创建视图 [dbo].[Helper_PackageVerification] WITH SCHEMABINDING AS
选择
-- 包验证列 [t0].PackageVerificationId, [t0].Verfied, -- 很久以前的拼写错误！[t0].VerifyDate, -- 这在 [t0] btw [t0].Username 中不可为空，
-- 包列 [t1].PackageId, [t1].PackageStatus, [t1].PackedOnDate
FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]
WHERE (Verfied = 1 AND VerifyDate IS NOT NULL AND PackageStatus <> 99) 去
CLUSTERED 索引已打开PackageVerificationId，主 NON CLUSTERED 索引如上所示。我实际上创建了六个转换索引来查看它会选择哪个。
我硬编码
PackageStatus <> 99. 它最初是一个参数。我尝试将 NOT NULL 添加到视图上的过滤器以查看会发生什么。这确实给了我一个 SEEK，但是一个没用的，因为 SEEK 谓词实际上是 on
VerifyDate IS NOT NULL。
https://www.brentozar.com/pastetheplan/?id=r1HlgF1Dd
您无法将过滤索引添加到索引视图，因此即使视图过滤掉 NOT NULL 日期，它也可能无法匹配。所以这可能是我无法让我的日期用于 SEEK 谓词的最终原因？
- 在这种情况下，我没有尝试直接在查询中使用辅助索引，但我非常希望它可以与 NOEXPAND 一起使用，因为我正在其他地方这样做。