• Artículo

Solucionar problemas de bajo rendimiento de las consultas: doblado de constantes y evaluación de expresiones durante la estimación de la cardinalidad

SQL Server evalúa algunas expresiones constantes con antelación para mejorar el rendimiento de las consultas. Es lo que se conoce como doblado de constantes. Una constante es un literal Transact-SQL, como 3, 'ABC', '2005-12-31', 1.0e3 o 0x12345678.

Expresiones que pueden doblarse

SQL Server usa el doblado de constantes con los siguientes tipos de expresiones:

  • Expresiones aritméticas, como 1+1, 5/3*2, que solo incluyen constantes.

  • Expresiones lógicas, como 1=1 y 1>2 AND 3>4, que solo incluyen constantes.

  • Funciones integradas que SQL Server considera que pueden doblarse, incluidas CAST y CONVERT. Por lo general, una función intrínseca puede doblarse si se trata de una función exclusiva de sus entradas y no contiene ninguna otra información contextual, como opciones SET, configuración de idioma, opciones de la base de datos y claves de cifrado. Las funciones no deterministas no pueden doblarse. Excepto algunas excepciones, las funciones deterministas integradas pueden doblarse.

Los tipos de objetos grandes constituyen una excepción. Si el tipo de salida del proceso de duplicación es un tipo de objeto grande (text, image, nvarchar(max), varchar(max) o varbinary(max)), SQL Server no dobla la expresión.

Expresiones que no pueden doblarse

El resto de tipos de expresiones no pueden doblarse. En concreto, los siguientes tipos de expresiones no pueden doblarse:

  • Expresiones no constantes como una expresión cuyo resultado dependa del valor de una columna.

  • Expresiones cuyos resultados dependan de una variable o parámetro locales, como @x.

  • Funciones no deterministas.

  • Funciones definidas por el usuario (Transact-SQL y CLR)

  • Expresiones cuyos resultados dependan de la configuración de idioma.

  • Expresiones cuyos resultados dependan de las opciones SET.

  • Expresiones cuyos resultados dependan de las opciones de configuración del servidor.

Ejemplos de expresiones constantes que pueden doblarse y que no pueden doblarse

Estudie la siguiente consulta:

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

Si la opción de base de datos PARAMETERIZATION no se establece en FORCED para la consulta (el valor predeterminado es SIMPLE), la expresión 117.00 + 1000.00 se evalúa y sustituye por su resultado, 1117.00, antes de que se compile la consulta. Entre las ventajas de este doblado de constantes figuran las siguientes:

  • La expresión no tiene que evaluarse repetidas veces durante el tiempo de ejecución.

  • El valor de la expresión después de su evaluación lo utiliza el optimizador de consultas para estimar el tamaño del conjunto de resultados del fragmento de la consulta TotalDue > 117.00 + 1000.00.

Por otra parte, si la función dbo.f es una función escalar definida por el usuario, la expresión dbo.f(100) no se dobla, puesto que SQL Server no dobla las expresiones que impliquen a funciones definidas por el usuario, incluso si son deterministas.

Evaluación de la expresión en tiempo de compilación para la estimación de la cardinalidad

Además, durante la optimización el estimador (de cardinalidad) del tamaño del conjunto de resultados que forma parte del optimizador evalúa algunas expresiones cuyas constantes no se doblan pero cuyos argumentos se conocen en tiempo de compilación, tanto si se trata de parámetros como de constantes.

Se evalúan las funciones integradas siguientes y los operadores especiales en tiempo de compilación específicamente, si se conocen todas sus entradas: UPPER, LOWER, RTRIM, DATEPART(YY solo), GetDate, CAST y CONVERT.

Los siguientes operadores también se evalúan en tiempo de compilación si se conocen todas sus entradas:

  • Operadores aritméticos: +, -, *, /, unarios -,

  • Operadores lógicos: AND, OR, NOT

  • Operadores de comparación: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

El optimizador no evalúa ninguna otra función ni operador durante la estimación de la cardinalidad.

Ejemplos de evaluación de expresiones en tiempo de compilación

Observe este procedimiento almacenado de Transact-SQL:

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

Durante la optimización de la instrucción SELECT del procedimiento, el optimizador intenta evaluar la cardinalidad esperada del conjunto de resultados para la condición OrderDate > @d+1. La expresión @d+1 no admite el doblado de constantes porque @d es un parámetro. Sin embargo, el valor del parámetro ya se conoce durante la optimización. Esto permite que el optimizador estime con exactitud el tamaño del conjunto de resultados, lo que ayuda a seleccionar un buen plan de consulta.

Observe ahora un ejemplo similar al anterior, con la diferencia de que se utiliza una variable local, @d2, para sustituir @d+1 en la consulta y de que la expresión se evalúa en una instrucción SET en lugar de en una consulta.

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

Cuando se optimiza la instrucción SELECT de MyProc2 en SQL Server, el valor de @d2 no se conoce. Por lo tanto, el optimizador utiliza una estimación predeterminada para la selectividad de OrderDate > @d2, (en este caso, un 30 por ciento).

Prácticas recomendadas: usar el doblado de constantes y la evaluación de expresiones en tiempo de compilación para generar planes de consulta óptimos

Para asegurarse de que genera planes de consulta óptimos, lo mejor es diseñar las consultas, los procedimientos almacenados y los lotes, de forma que el optimizador de consultas pueda estimar con exactitud la selectividad de las condiciones de la consulta en función de las estadísticas sobre la distribución de los datos. De lo contrario, el optimizador deberá usar una estimación predeterminada para estimar la selectividad (como el 30 por ciento del ejemplo anterior).

Para asegurarse de que el estimador de cardinalidad del optimizador genera unas buenas estimaciones, primero debería comprobar que las opciones SET de base de datos AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS se han establecido en ON (valor predeterminado), o de que ha creado manualmente estadísticas en todas las columnas a las que se hace referencia en la condición de la consulta. A continuación, observe las siguientes recomendaciones (siempre que sea posible) mientras diseña las condiciones de las consultas:

  • Evite el uso de variables locales en las consultas. En su lugar, utilice parámetros, literales o expresiones.

  • Limite el uso de operadores y funciones incrustados en una consulta que contenga un parámetro de los incluidos en Compile-Time Expression Evaluation for Cardinality Estimation.

  • Asegúrese de que las expresiones formadas exclusivamente por constantes de la condición de la consulta admitan el doblado de constantes o se puedan evaluar en tiempo de compilación.

  • Si tiene que utilizar una variable local para evaluar una expresión que se utilizará en una consulta, considere la posibilidad de evaluarla en un ámbito distinto al de la consulta. Por ejemplo, puede resultar útil realizar una de las siguientes acciones:

    • Pase el valor de la variable a un procedimiento almacenado que contenga la consulta que desee evaluar y haga que la consulta utilice el parámetro del procedimiento, en lugar de una variable local.

    • Cree una cadena que contenga una consulta basada parcialmente en el valor de la variable local y luego ejecute la cadena utilizando SQL dinámico (EXEC o sp_executesql).

    • Haga que la consulta tenga parámetros y ejecútela mediante sp_executesql; a continuación, pase el valor de la variable como un parámetro a la consulta.