• Article

Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation

SQL Server evaluates some constant expressions early to improve query performance. This is referred to as constant folding. A constant is a Transact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

Foldable Expressions

SQL Server uses constant folding with the following types of expressions:

  • Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.

  • Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.

  • Built-in functions that are considered foldable by SQL Server, including CAST and CONVERT. Generally, an intrinsic function is foldable if it is a function of its inputs only and not other contextual information, such as SET options, language settings, database options, and encryption keys. Nondeterministic functions are not foldable. Deterministic built-in functions are foldable, with some exceptions.

An exception is made for large object types. If the output type of the folding process is a large object type (text, image, nvarchar(max), varchar(max), or varbinary(max)), then SQL Server does not fold the expression.

Nonfoldable Expressions

All other expression types are not foldable. In particular, the following types of expressions are not foldable:

  • Nonconstant expressions such as an expression whose result depends on the value of a column.

  • Expressions whose results depend on a local variable or parameter, such as @x.

  • Nondeterministic functions.

  • User-defined functions (both Transact-SQL and CLR).

  • Expressions whose results depend on language settings.

  • Expressions whose results depend on SET options.

  • Expressions whose results depend on server configuration options.

Examples of Foldable and Nonfoldable Constant Expressions

Consider the following query:

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

If the PARAMETERIZATION database option is not set to FORCED for this query (the default is SIMPLE), then the expression 117.00 + 1000.00 is evaluated and replaced by its result, 1117.00, before the query is compiled. Benefits of this constant folding include the following:

  • The expression does not have to be evaluated repeatedly at run time.

  • The value of the expression after it is evaluated is used by the query optimizer to estimate the size of the result set of the portion of the query TotalDue > 117.00 + 1000.00.

On the other hand, if dbo.f is a scalar user-defined function, the expression dbo.f(100) is not folded, because SQL Server does not fold expressions that involve user-defined functions, even if they are deterministic.

Compile-Time Expression Evaluation for Cardinality Estimation

In addition, some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization.

Specifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER, LOWER, RTRIM, DATEPART( YY only ), GetDate, CAST, and CONVERT.

The following operators are also evaluated at compile time if all their inputs are known:

  • Arithmetic operators: +, -, *, /, unary -,

  • Logical Operators: AND, OR, NOT

  • Comparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

No other functions or operators are evaluated by the optimizer during cardinality estimation.

Examples of Compile-Time Expression Evaluation

Consider this Transact-SQL stored procedure:

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

During optimization of the SELECT statement in the procedure, the optimizer tries to evaluate the expected cardinality of the result set for the condition OrderDate > @d+1. The expression @d+1 is not constant-folded, because @d is a parameter. However, at optimization time, the value of the parameter is known. This allows the optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

Now consider an example similar to the previous one, except that a local variable @d2 replaces @d+1 in the query and the expression is evaluated in a SET statement instead of in the query.

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

When the SELECT statement in MyProc2 is optimized in SQL Server, the value of @d2 is not known. Therefore, the optimizer uses a default estimate for the selectivity of OrderDate > @d2, (in this case 30 percent).

Best Practices: Using Constant Folding and Compile-Time Expression Evaluation for Generating Optimal Query Plans

To make sure you generate optimal query plans, it is best to design queries, stored procedures, and batches so that the query optimizer can accurately estimate the selectivity of the conditions in your query, based on statistics about your data distribution. Otherwise, the optimizer must use a default estimate when estimating selectivity (such as 30 percent in the previous example).

To make sure that the cardinality estimator of the optimizer provides good estimates, you should first make sure that the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS database SET options are ON (the default setting), or that you have manually created statistics on all columns referenced in a query condition. Then, when you are designing the conditions in your queries, do the following when it is possible:

  • Avoid the use of local variables in queries. Instead, use parameters, literals, or expressions in the query.

  • Limit the use of operators and functions embedded in a query that contains a parameter to those listed under Compile-Time Expression Evaluation for Cardinality Estimation.

  • Make sure that constant-only expressions in the condition of your query are either constant-foldable, or can be evaluated at compilation time.

  • If you have to use a local variable to evaluate an expression to be used in a query, consider evaluating it in a different scope than the query. For example, it may be helpful to perform one of the following:

    • Pass the value of the variable to a stored procedure that contains the query you want to evaluate, and have the query use the procedure parameter instead of a local variable.

    • Construct a string that contains a query based in part on the value of the local variable, and then execute the string by using dynamic SQL (EXEC or sp_executesql).

    • Parameterize the query and execute it by using sp_executesql, and pass the value of the variable as a parameter to the query.