排除查询性能不佳故障：基数估计过程中的常量折叠和表达式计算

项目
12/15/2008

SQL Server 先计算一些常量表达式来提高查询性能，这称为常量折叠。常量属于 Transact-SQL 文本，如 3、'ABC'、'2005-12-31'、1.0e3 或 0x12345678。

可折叠表达式

SQL Server 将常量折叠用于下列类型的表达式：

仅包含常量的算术表达式，如 1+1, 5/3*2。
仅包含常量的逻辑表达式，如 1=1 和 1>2 AND 3>4。
在 SQL Server 中，被看作是可折叠的内置函数包括 CAST 和 CONVERT。通常，如果内部函数只与输入有关而与其他上下文信息（例如 SET 选项、语言设置、数据库选项和加密密钥）无关，则该内部函数是可折叠的。不确定性函数是不可折叠的。确定性内置函数是可折叠的，但也有例外情况。

使用大型对象类型时将出现例外。如果折叠进程的输出类型是大型对象类型（text、image、nvarchar(max)、varchar(max) 或 varbinary(max)），SQL Server 将不折叠该表达式。

不可折叠表达式

所有其他表达式类型都是不可折叠的。特别是下列类型的表达式是不可折叠的：

非常量表达式，例如，结果取决于列值的表达式。
结果取决于局部变量或参数的表达式，例如 @x。
不确定性函数。
用户定义函数（Transact-SQL 函数和 CLR 函数）。
结果取决于语言设置的表达式。
结果取决于 SET 选项的表达式。
结果取决于服务器配置选项的表达式。

可折叠和不可折叠常量表达式示例

请考虑以下查询：

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

如果此查询的 PARAMETERIZATION 数据库选项不设置为 FORCED（默认设置为 SIMPLE），则查询被编译之前，将计算表达式 117.00 + 1000.00 并用计算结果 1117.00 替换该表达式。常量折叠的优点如下：

运行时不必重复计算表达式。
查询优化器可使用计算表达式后所得的值来估计 TotalDue > 117.00 + 1000.00 查询部分的结果集的大小。

另一方面，如果 dbo.f 是用户定义的标量函数，则不折叠表达式 dbo.f(100)，因为 SQL Server 不折叠包含用户定义函数的表达式，即使这些函数是确定性函数也是如此。

基数估计过程中的编译时表达式计算

此外，有些不可进行常量折叠但其参数在编译时已知的表达式（无论其参数是参数变量还是常量）将由优化期间优化器中包括的结果集大小（基数）估计器来计算。

特别地，在编译时将计算下列内置函数和特殊运算符（如果它们的所有输入都已知）：UPPER、LOWER、RTRIM、DATEPART(仅 YY)、GetDate、CAST 和 CONVERT。

如果所有输入都是已知的，则在编译时计算下列运算符：

算术运算符：+、-、*、/、一元运算符、-，
逻辑运算符：AND、OR、NOT
比较运算符：<、>、<=、>=、<>、LIKE、IS NULL、IS NOT NULL

在基数估计过程中，优化器不计算其他任何函数或运算符。

编译时表达式计算示例

请考虑以下 Transact-SQL 存储过程：

USE AdventureWorks
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1

在优化存储过程中的 SELECT 语句期间，优化器尝试计算 OrderDate > @d+1 条件结果集的所需基数。表达式 @d+1 不可进行常量折叠，因为 @d 是一个参数变量。但是，在优化时，该参数值是已知的。这使优化器能够准确估计结果集的大小，这有助于优化器选择较好的查询计划。

现在考虑一个与上面的示例类似的示例，不同之处是在此查询中局部变量 @d2 替换了 @d+1，并且表达式在 SET 语句中计算而不是在查询中计算。

USE AdventureWorks
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END

在 SQL Server 2005 中优化 MyProc2 中的 SELECT 语句时，@d2 的值是未知的。因此，优化器为 OrderDate > @d2 的选择性使用默认估计值（在此示例中为 30%）。

最佳方法：使用常量折叠和编译时表达式计算来生成最佳查询计划

若要确保生成最佳查询计划，最好设计查询、存储过程和批处理，以使查询优化器可根据数据分布统计信息准确估计查询中条件的选择性。否则，在估计选择性时，优化器必须使用默认估计值（如在上一示例中为 30%）。

若要确保优化器的基数估计器能够提供较好的估计值，必须首先确保 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 数据库的 SET 选项设置为 ON（默认设置），或者确保已手动创建查询条件中引用的所有列的统计信息。然后，在设计查询条件时，尽可能遵循下列准则：

避免在查询中使用局部变量，而在查询中使用参数、文字或表达式。
只允许在包含参数的查询中嵌入“基数估计过程中的编译时表达式计算”中列出的运算符和函数。
确保查询条件中仅包含常量的表达式可进行常量折叠或可在编译时计算。
如果必须使用局部变量来计算查询中使用的表达式，请考虑在查询之外的作用域中计算此表达式。例如，执行下列操作之一可能非常有用：
- 将变量的值传递到包含要计算的查询的存储过程，并使该查询使用过程参数而不是局部变量。
- 构造一个字符串，使其包含部分基于局部变量的值的查询，然后使用动态 SQL（EXEC 或 sp_executesql）执行该字符串。
- 参数化查询、使用 sp_executesql 执行该查询并将变量的值作为参数传递给该查询。

请参阅

通过