我在 Stackoverflow 数据库上进行测试，以找出 SQL Server 不推荐在执行计划中使用索引的可能情况，但是如果我们引入一个，它将有很大帮助！
对于 Group by、Order by Clause 和聚合函数（计数函数 - 表的最小副本），它是否很容易。我写了一个随机查询，其中我知道引入支持性索引肯定会有所帮助，但是缺少索引推荐只会在连接条件下而不是在 order by 子句上。
查询如下：
select top 100 Location from Users U join Badges B
on B.UserId = U.Id
order by Location desc
引入了以下索引以提高性能：
create index Location on Users(Location)
go
create index UsersId on Badges(UserId)
go
优化器使用的索引与上述查询的预期一致：
逻辑读取和时间统计如下：
现在，我想用 Location 列的 Users 表上的索引和 Badges (UserId) 表上的索引来测试性能，这里的性能变得很糟糕（大约需要 7 分钟）：
逻辑读取和时间统计如下：
用户表中的索引被大量使用，从执行计划和逻辑读取中可以明显看出，但是执行聚集索引扫描和表假脱机（惰性假脱机）会导致大部分问题。
以上测试均在 SQL Server 2019 上以 SQL Server 2016 兼容模式（130）进行。
如果有人可以就潜在问题提出建议，那将有很大帮助。
这里还要注意一点，当这两个表中的任何一个都没有非聚集支持索引时，相同的查询会在 9 秒内完成。下面是执行计划：
逻辑读取和时间统计：
出于测试目的，我将兼容性级别更改为 2019(150)，令我惊讶的是 - 之前的查询仅在用户（位置）表上而不在徽章表上具有索引，在 2 秒内完成，这在 SQL Server 2016 中需要 7 分钟兼容性（130）模式：
逻辑统计和时间统计：
在 2019 兼容模式下，Parallelism 之前的所有算子都是批处理模式。
在这方面的任何输入都将帮助我理解这种行为。
由于使用该子句设置的行目标，优化器选择具有惰性假脱机的计划。
TOP您要求的是前 100 行（而不是完整集），因此优化器会尝试找到一个快速返回 100 行的计划，而不是一个可以在最快时间内返回所有匹配项的计划。
优化器的推理基于统计和建模假设，这可能会出错，就像这里一样。
惰性表假脱机是一种性能假脱机。
行目标和性能假脱机因素都计入了显示为最便宜的计划的估计成本。
您可以使用查询提示禁用性能假脱机：
您可以使用查询提示禁用行目标逻辑：
要么会给你一个更像你上次显示的哈希和前 n 排序的计划，但是当兼容性设置为 130 时没有批处理模式。
顺便说一句，惰性假脱机计划的性能非常依赖于您的硬件，尤其是内存和tempdb性能。在我的笔记本电脑上执行一次典型的执行需要 6.5 秒：
在没有非聚集索引的情况下，它运行 6.9 秒：
在 Microsoft SQL Server 2019 CU15 上执行。最大内存设置为 4GB，12 个逻辑处理器可用。