如何调优SQL Server查询

在优化SQL Server查询的道路上，每一个步骤都蕴含着丰富的知识和策略。当我们面对一个具体的问题查询时，首先需要理解查询背后的逻辑和数据库结构。今天，我们将深入如何调优一个特定查询，并理解如何影响查询性能的关键因素。

让我们来看一个实际的查询案例：

该查询从一个名为Sales.SalesOrderDetail的表中选取特定字段，基于ProductID小于一个给定值的条件进行筛选，并按CarrierTrackingNumber排序。这个查询在执行时存在一些性能问题。数据库需要扫描整个非聚集索引，因为没有合适的索引支持查询。这导致了大量的逻辑读和长时间运行。那么，如何优化这个查询呢？

让我们不再纠结为何查询优化器没有选择刚刚创建的非聚集索引。我们已将我们的意图明确传达给查询优化器，通过查询提示向其询问原因。如同我最初所说的，我不想深陷其中，纠结于过多的为什么。

在非聚集索引的叶子节点层面，我们需要包含SELECT列表中请求的额外列来解决这个问题。我们可以通过查看书签查找来确定哪些列当前缺失。比如：CarrierTrackingNumber、OrderQty、UnitPrice以及UnitPriceDiscount等列。

我们决定重新构建非聚集索引。索引的定义如下：

```sql

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)

INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)

WITH (DROP_EXISTING = ON);

GO

```

现在我们已经完成了这个改变，我们可以再次运行查询来验证效果。这次，我们不再添加查询提示，让查询优化器自动做出选择。那么结果如何呢？当我们查看执行计划时，可以看到索引已经被选中。

SQL Server现在会在非聚集索引上进行查找操作，但在执行计划中仍然有一个排序（Sort）运算符。由于硬编码的基数计算为30%，排序操作仍然会蔓延到TempDb。天哪！虽然逻辑读已经降低到757，但执行时间仍然接近800毫秒。那么现在你应该怎么办呢？

我们可以尝试在非聚集索引的导航结构中直接包含CarrierTrackingNumber列，这是SQL Server进行排序运算符的列。当我们在非聚集索引中直接包含这列（作为主键），我们就实现了物理排序，排序（Sort）运算符应该会消失，也不会再蔓延到TempDb。在执行计划中，也不会再有关心错误的基数计算的运算符。我们来试验这个假设，并重新构建非聚集索引：

```sql

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)

INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)

WITH (DROP_EXISTING = ON);

GO

```

从新的索引定义可以看出，我们已经对CarrierTrackingNumber和ProductID列的数据进行了物理预排序。当你重新运行查询并查看执行计划时，你会惊喜地发现排序（Sort）运算符已经消失。SQL Server扫描了非聚集索引的整个叶子层，使用了剩余谓语作为搜索谓语。

这个执行计划很不错！逻辑读降至763，执行时间减少到600毫秒，相比之前有25%的改善！查询优化器为我们推荐了更好的非聚集索引，通过缺失索引建议！让我们信任一次，创建建议的非聚集索引：

```sql

CREATE NONCLUSTERED INDEX [A_helpful_index_name] -- 虽然SQL Server不在乎索引名称，但给索引一个有意义的名称是个好习惯

ON Sales.SalesOrderDetail(ProductID) -- 根据实际情况调整列名

INCLUDE (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal); -- 根据实际需求调整包含的列

GO

```

当你再次运行最初的查询时，你会发现一个令人惊讶的事情：查询优化器使用了我们刚刚创建的非聚集索引，缺失索引建议已经消失！这就是进步，我们满足了查询优化器的需求，创建了真正会被使用的索引。查询优化器：信赖与怀疑之间的微妙平衡

摘要：

在数据库管理的领域中，查询优化器扮演着至关重要的角色。本文旨在揭示查询优化器的双面性：它如何帮助你提高查询效率，又如何可能在某些情况下产生误导。让我们深入这个话题，看看是否应该“永远不相信查询优化器”。

一、查询优化器的价值

查询优化器是数据库管理系统中的核心组件之一，其主要任务是优化SQL查询的执行计划，从而提高查询性能。在构建数据库索引时，查询优化器通过分析和预测数据访问模式来提供有价值的建议，从而帮助数据库管理员和开发者更有效地管理数据和索引。在许多情况下，查询优化器确实能够显著提高数据库的性能。

二、查询优化器的潜在陷阱

尽管查询优化器具有诸多优点，但它也存在一些潜在的问题。在某些情况下，由于数据分布的不均匀性、索引设计的不合理或其他复杂因素，查询优化器可能会提供误导性的建议。过于依赖查询优化器的自动调整功能可能会导致开发人员忽视对数据库性能的深入理解和持续优化。在对查询优化器的建议进行实施之前，进行充分的测试验证是非常重要的。

三、平衡信赖与怀疑

查询优化器是一个强大的工具，它可以帮助我们提高数据库性能。我们也不能完全依赖它。作为数据库管理员和开发者，我们需要对数据库的工作原理有深入的理解，并根据实际情况对查询优化器的建议进行审慎评估。在某些情况下，可能需要手动调整查询或索引设计，以获得最佳性能。我们应该在信赖和怀疑之间寻求微妙的平衡。

结论：没有一种工具是完美的，包括查询优化器。我们应该充分利用其优点，同时保持对其潜在问题的警惕。通过深入理解数据库的工作原理，并结合实际情况进行适当调整，我们可以最大限度地提高数据库性能。希望本文的内容能对大家的学习和实践有所帮助。