[翻译] Sql Server查询优化器是如何工作的
原文: How does the SQL Server Query Optimizer Works
SQL Server数据库引擎的核心是两个主要组件：存储引擎和查询处理器，也称为关系引擎。
- 存储引擎：在保持数据完整性的同时，负责以优化并发的方式在硬盘和内存之间读取数据
- 查询处理器：
- 通过查询优化器，负责设计查询计划（实现什么样的算法和操作符）
- 按照上面的计划，通过执行引擎执行查询
查询处理器做了以下的工作：
- 解析查询
- 绑定查询到对象
- 生成可能的查询计划
- 为每个计划进行成本评估
执行引擎做了以下的工作：
- 执行查询
- 计划缓存
解析时将sql查询翻译成一个初始的树，主要通过名称绑定
搜索空间
我们把一个给定的请求的搜索空间定义为所有这个请求的查询计划的集合，搜索空间内的所有查询计划返回同样的结果。
生成候选执行计划
如上所述，查询优化器的一个基本目的是为你的请求找到一个有效的执行计划。即使对于相对简单的查询，也可能存在大量不同的方式来访问数据以产生相同的最终结果。因此，查询优化器必须从（可能）大量的候选执行计划中找到尽可能做好的计划。它（优化器）做出正确的选择是很重要的，因为不同的查询计划返回结果给用户所需的时间差别很大。
查询优化器必须在优化时间和计划质量之间取得平衡。SQL Server不做详尽的搜索，而是试图尽快找到一个适当高效的计划。
评估每个计划的成本
查询优化器需要估计这些计划的成本，并选择成本最低的那个计划。为了估计计划的成本，它使用考虑使用资源（如I/O，CPU和内存）的成本计算公式来估算计划中每个物理运算符的成本。
基数估计： 查询计划的成本估计主要取决于物理运算符所使用的算法，以及需要处理的记录的估计数量。这个估计数量被称为基数估计。
查询的执行和计划缓存
一旦查询被优化，生成的计划被执行引擎用来检索所需的数据。生成的执行计划可能被存储在内存中的计划缓存中，以便下次进行相同查询时可以重新使用它。
然而，重用现有的执行计划并不总是最好的解决方案。这取决于表中的数据分布，给定查询的最佳执行计划可能因为在查询中提供的参数产生很大的不同，一种叫做参数嗅探的行为可能导致选择次优计划。
重编译
你可以强制指定Sql Server在每次运行时重新编译存储过程。这样的好处是每次运行会创建最合适的查询计划。但是，重新编译是个CPU密集的操作，这对于那些运行频繁的存储过程，或者是被CPU所制约的服务器来说，并不是一个理想的解决方案。另外要注意的是，计划不会存储在缓存中，这使得有问题的计划更难被发现。
ALTER PROCEDURE Get_OrderID_OrderQty
@ProductID INT
WITH RECOMPILE
AS
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;
提示
另一个选项是使用OPTIMIZE FOR查询提示。这告诉SQL Server在编译计划时使用指定的值。如果通过测试，你可以找到一个值，每次产生一个“足够好”的计划，而性能不大不小可以接受，这是一个很好的选择。
ALTER PROCEDURE Get_OrderID_OrderQty
@ProductID INT
AS
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION (OPTIMIZE FOR (@ProductID=945));
事实是，即使经过30多年的研究，查询优化器是仍然面临一些技术挑战的高度复杂的软件。因此，即使你已向查询优化器提供了所需的所有信息，也似乎没有任何明显的问题，你也可能没有获得有效的计划。
相关：
