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 utiliza el doblado de constantes con los siguientes tipos de expresiones:
- Expresiones aritméticas, como
1+1, 5/3*2, que sólo incluyen constantes. - Expresiones lógicas, como
1=1y1>2 AND 3>4, que sólo 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 doblado 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 (tanto Transact-SQL como 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 definida por el usuario escalar, la expresión dbo.f(100) no se dobla, puesto que SQL Server no dobla expresiones que contemplen 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.
En concreto, las siguientes funciones integradas y operadores especiales se evalúan en tiempo de compilación si se conocen todas sus entradas: UPPER, LOWER, RTRIM, DATEPART (sólo YY), 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: +, -, *, /, - unario
- 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
Estudie este procedimiento almacenado de Transact-SQL:
USE AdventureWorks
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 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
Cuando se optimiza la instrucción SELECT de MyProc2 en SQL Server 2005, 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 Evaluación de la expresión en tiempo de compilación para la estimación de la cardinalidad.
- 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.
Vea también
Conceptos
Solucionar problemas de bajo rendimiento de consultas: estimación de cardinalidad
Otros recursos
Optimizar consultas
Solucionar problemas de consultas