SQL Server查询优化器如何工作

在众多数据库当中，SQL Server是我们比较常见的数据库。因此对于许多新手来说，对于SQL Server数据库查询优化器如何工作并不清楚。SQL Server 数据库引擎的核心是两个主要组件：存储引擎和查询处理器（也称为关系引擎）。存储引擎：以优化并发性并同时保持数据完整性的方式照顾磁盘和内存之间的数据读取。查询处理器：1.负责通过Query Optimizer设计查询计划;2.由Execution Engine根据该计划执行查询。

　　查询处理器执行以下操作：

1.查询解析

2.将查询绑定到对象

3.生成可能的执行计划

4.每个计划的成本评估

　　执行引擎执行以下操作：

1.查询执行

2.计划缓存

解析将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在编译计划时使用指定的值。如果通过测试可以找到每次生成“足够好的”计划的值，并且鼠标和大象的性能都可以接受，那么这对您来说是一个不错的选择。

但是，请了解您正在引导查询优化器。您说的是您的最佳想法。OPTIMIZE FOR的最大缺点在于数据分布发生变化的表。更改速度越快，此提示可能会过时。如果您提供的价值在一个月或一年中不是最优的，该怎么办?您需要有一种定期检查和修订此方法的方法。

ALTER PROCEDURE Get_OrderID_OrderQty

@ProductID INT

AS

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = @ProductID

OPTION (OPTIMIZE FOR (@ProductID=945));

现实情况是，即使经过30多年的研究，查询优化器还是非常复杂的软件，仍然面临一些技术挑战。结果，即使在为Query Optimizer提供了所需的所有信息之后，甚至在似乎没有任何明显问题的情况下，您仍然可能无法获得有效的计划。

通过上述介绍，SQL Server查询优化器如何工作相信大家已经清楚了吧，想了解更多关于SQL Server数据库信息，请继续关注中培教育。