我是个懒人，我只想干尽可能少的活。当我干活的时候我不想太多。是，你没看错，这看起来很糟糕，作为一个DBA这很不合格。但在今天的文章里，我想给你展示下，当你想对特定查询创建索引设计时，你如何把你的工作和思考过程传达给查询优化器。听起来很有意思？嗯，那就进入我的索引调优世界吧！
有问题的查询
我们来看下列查询：
1 DECLARE @i INT = 999
2 SELECT
3 SalesOrderID,
4 SalesOrderDetailID,
5 CarrierTrackingNumber,
6 OrderQty,
7 LineTotal
8 FROM Sales.SalesOrderDetail
9 WHERE ProductID < @i
10 ORDER BY CarrierTrackingNumber
11 GO
如你所见，这里用了一个本地变量与一个不等于谓语来从Sales.SalesOrderDetail表来获取一些记录。当你执行那个查询，看它的执行计划时，你会发现它有一些严重的问题：
SQL Server需要扫描Sales.SalesOrderDetail表的整个非聚集索引，因为没有支持的非聚集索引。对这个扫描，查询需要1382个逻辑读，运行时间近800毫秒。
查询优化器在查询计划里引入了筛选器（Filter）运算符，它进行逐行比较用来检查符合的行（ProductID < @i）
因为ORDER BY CarrierTrackingNumber，在执行计划里一个排序（Sort）运算符被引入。
排序运算符蔓延到了TempDb，因为不正确的基数计算（Cardinality Estimation）。用了带了本地变量与不等于谓语的组合，SQL Server从表的基数硬码估计30%的行。在我们的情况里估计行数是36395（121317 * 30%）。实际上查询返回120621行，这意味这排序（Sort）运算符必须蔓延到TempDb，因为请求的内存授予太小了。
现在我问你——你能改善这个查询么？你的建议是什么？休息下，想个几分钟。不修改查询本身，你如何改善这个查询？
我们来调试查询！
当然，我们要做索引相关的调整来改善。没有支持的非聚集索引，那只能是查询优化器唯一可以使用计划来运行我们的查询。但对这个指定查询，什么是好的非聚集索引呢？一般来说，我通过看搜索谓语来考虑可能的非聚集速印。在我们的例子里，搜索谓语如下：
WHERE ProductID < @i
我们请求在ProductID列过滤的行。因此我们想在那个列创建支持的非聚集索引。我们建立索引：
1 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
2 GO
在非聚集索引创建后，我们需要验证下改变，因此我们再次执行刚才的查询代码。结果如何捏？查询优化器并没有使用我们刚创建的非聚集索引！我们在搜索谓语上创建了支持的非聚集索引，查询优化器没有引用它？通常人们对此就无辙了。其实我们可以提示查询优化器来使用非聚集索引，来更好的理解“为什么”查询优化器没有自动选择索引：
1 DECLARE @i INT = 999
2
3 SELECT
4 SalesOrderID,
5 SalesOrderDetailID,
6 CarrierTrackingNumber,
7 OrderQty,
8 LineTotal
9 FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
10 WHERE ProductID < @i
11 ORDER BY CarrierTrackingNumber
12 GO
当你现在看执行计划时，你会看到下列的野性——一个并行计划：
查询花费了370109个逻辑读！运行时间基本和刚才的一样。这里到底发生了什么？当你仔细看执行计划，你会发现查询优化器引入了书签查找，因为刚才创建的非聚集索引，对于查询来说，不是一个覆盖非聚集索引。查询越过了所谓的临界点（Tipping Point），因为我们用当前的搜索谓语来获得几乎所有行。因此用非聚集索引和书签查找来组合没有意义。
不去想为什么查询优化器不选择刚才创建的非聚集索引，我们已经把自己的思路表达给了查询优化器本身，通过查询提示进行了询问了查询优化器，为什么非聚集索引没被自动选择。如我刚开始说的：我不想考虑太多。
使用非聚集索引解决这个问题，在非聚集索引的叶子层，我们必须对从SELECT列表的请求的额外列进行包含。你可以再次看下书签查找来看下在叶子层哪些列当前丢失：
CarrierTrackingNumber
OrderQty
UnitPrice
UnitDiscountPrice
我们重建那个非聚集索引：
1 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
2 INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
3 WITH
4 (
5 DROP_EXISTING = ON
6 )
7 GO
我们已经做出了另1个改变，因此我们可以重新运行了查询来验证下。但是这次我们不加查询提示，因为现在查询优化器会自动选择非聚集索引。结果如何捏？当你看执行计划时，索引现在已被选择。
