Solución de problemas de consultas lentas afectadas por el tiempo de espera del optimizador de consultas

Artículo
03/24/2023

Se aplica a: SQL Server

En este artículo se presenta el tiempo de espera del optimizador, cómo puede afectar al rendimiento de las consultas y cómo optimizar el rendimiento.

¿Qué es el tiempo de espera del optimizador?

SQL Server usa un optimizador de consultas (QO) basado en costos. Para obtener información sobre qo, consulte guía de arquitectura de procesamiento de consultas. Un optimizador de consultas basado en costos selecciona un plan de ejecución de consultas con el costo más bajo después de compilar y evaluar varios planes de consulta. Uno de los objetivos de SQL Server Optimizador de consultas es dedicar un tiempo razonable a la optimización de consultas en comparación con la ejecución de consultas. La optimización de una consulta debe ser mucho más rápida que ejecutarla. Para lograr este destino, QO tiene un umbral integrado de tareas que se deben tener en cuenta antes de detener el proceso de optimización. Cuando se alcanza el umbral antes de que QO haya considerado todos los planes posibles, alcanza el límite de tiempo de espera del optimizador. Un evento de tiempo de espera del optimizador se notifica en el plan de consulta como TimeOut en Reason For Early Termination of Statement Optimization (Motivo para la terminación temprana de la optimización de instrucciones). Es importante comprender que este umbral no se basa en la hora del reloj, sino en el número de posibilidades que considera el optimizador. En las versiones actuales SQL Server QO, se tienen en cuenta más de medio millón de tareas antes de que se alcance el tiempo de espera.

El tiempo de espera del optimizador está diseñado para SQL Server y, en muchos casos, no es un factor que afecte al rendimiento de las consultas. Sin embargo, en algunos casos, la elección del plan de consulta SQL puede verse afectada negativamente por el tiempo de espera del optimizador y podría producirse un rendimiento de consulta más lento. Cuando se producen estos problemas, comprender el mecanismo de tiempo de espera del optimizador y cómo se pueden ver afectadas las consultas complejas puede ayudarle a solucionar problemas y mejorar la velocidad de las consultas.

El resultado de alcanzar el umbral de tiempo de espera del optimizador es que SQL Server no ha considerado todo el conjunto de posibilidades de optimización. Es decir, puede haber omitido planes que podrían producir tiempos de ejecución más cortos. Qo se detendrá en el umbral y tendrá en cuenta el plan de consulta de menor costo en ese momento, aunque pueda haber mejores opciones sin explorar. Tenga en cuenta que el plan seleccionado después de alcanzar un tiempo de espera del optimizador puede producir una duración de ejecución razonable para la consulta. Sin embargo, en algunos casos, el plan seleccionado podría dar lugar a una ejecución de consulta poco óptimo.

¿Cómo detectar un tiempo de espera del optimizador?

Estos son los síntomas que indican un tiempo de espera del optimizador:

Consulta compleja

Tiene una consulta compleja que implica una gran cantidad de tablas unidas (por ejemplo, ocho o más tablas están unidas).
Consulta lenta

La consulta puede ejecutarse lentamente o más lentamente de lo que se ejecuta en otra versión o sistema SQL Server.

El plan de consulta muestra StatementOptmEarlyAbortReason=Timeout

El plan de consulta se muestra StatementOptmEarlyAbortReason="TimeOut" en el plan de consulta XML.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

Compruebe las propiedades del operador de plan más a la izquierda en Microsoft SQL Server Management Studio. Puede ver que el valor de Reason For Early Termination of Statement Optimization es TimeOut.

¿Qué provoca un tiempo de espera del optimizador?

No hay ninguna manera sencilla de determinar qué condiciones provocarían que se alcanzara o superara el umbral del optimizador. Las secciones siguientes son algunos factores que afectan a cuántos planes explora qo al buscar el mejor plan.

¿En qué orden deben combinarse las tablas?

Este es un ejemplo de las opciones de ejecución de combinaciones de tres tablas (Table1, Table2, Table3):
- Combinar Table1 con Table2 y el resultado con Table3
- Combinar Table1 con Table3 y el resultado con Table2
- Combinar Table2 con Table3 y el resultado con Table1
Nota: Cuanto mayor sea el número de tablas, mayores son las posibilidades.
¿Qué montón o estructura de acceso de árbol binario (HoBT) se va a usar para recuperar las filas de una tabla?
- Índice agrupado
- Índice no clúster1
- Índice no clúster2
- Montón de tablas
¿Qué método de acceso físico usar?
- Búsqueda de índice
- Examen de índice
- Examen de tabla
¿Qué operador de unión física se va a usar?
- Combinación de bucles anidados (NJ)
- Combinación hash (HJ)
- Combinación de combinación (MJ)
- Combinación adaptable (a partir de SQL Server 2017 (14.x))
Para obtener más información, vea Combinaciones.
¿Ejecutar partes de la consulta en paralelo o en serie?

Para obtener más información, vea Procesamiento de consultas en paralelo.

Aunque los siguientes factores reducirán el número de métodos de acceso considerados y, por tanto, las posibilidades que se consideran:

Predicados de consulta (filtros en la WHERE cláusula )
Existencias de restricciones
Combinaciones de estadísticas bien diseñadas y actualizadas

Nota: El hecho de que QO alcance el umbral no significa que termine con una consulta más lenta. En la mayoría de los casos, la consulta funcionará bien, pero en algunos casos, es posible que vea una ejecución de consulta más lenta.

Ejemplo de cómo se tienen en cuenta los factores

Para ilustrar, vamos a tomar un ejemplo de una combinación entre tres tablas (t1, t2y t3) y cada tabla tiene un índice clúster y un índice no clúster.

En primer lugar, tenga en cuenta los tipos de combinación física. Hay dos combinaciones implicadas aquí. Además, dado que hay tres posibilidades de unión física (NJ, HJ y MJ), la consulta se puede realizar de 3² = 9 maneras.

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

A continuación, considere el orden de combinación, que se calcula mediante Permutaciones: P (n, r). El orden de las dos primeras tablas no importa, por lo que puede haber P(3,1) = 3 posibilidades:

Únase a t1t2 y, a continuación, con t3
Únase a t1t3 y, a continuación, con t2
Únase a t2t3 y, a continuación, con t1

A continuación, tenga en cuenta los índices agrupados y no clúster que se podrían usar para la recuperación de datos. Además, para cada índice, tenemos dos métodos de acceso: buscar o examinar. Esto significa que, para cada tabla, hay 2² = 4 opciones. Tenemos tres tablas, por lo que puede haber 4³ = 64 opciones.

Por último, teniendo en cuenta todas estas condiciones, puede haber 9*3*64 = 1728 posibles planes.

Ahora, supongamos que hay n tablas unidas en la consulta y que cada tabla tiene un índice agrupado y un índice no clúster. Tenga en cuenta los siguientes factores:

Pedidos de combinación: P(n,n-2) = n!/2
Tipos de combinación: 3^n-1
Diferentes tipos de índice con métodos de búsqueda y examen: 4ⁿ

Multiplique todos los anteriores y podemos obtener el número de planes posibles: 2*n!*12^n-1. Cuando n = 4, el número es 82 944. Cuando n = 6, el número es 358 318 080. Por lo tanto, con el aumento en el número de tablas implicadas en una consulta, el número de posibles planes aumenta geométricamente. Además, si incluye la posibilidad de paralelismo y otros factores, puede imaginar cuántos planes posibles se tendrán en cuenta. Por lo tanto, es más probable que una consulta con muchas combinaciones alcance el umbral de tiempo de espera del optimizador que una con menos combinaciones.

Tenga en cuenta que los cálculos anteriores ilustran el peor escenario. Como hemos señalado, hay factores que reducirán el número de posibilidades, como predicados de filtro, estadísticas y restricciones. Por ejemplo, un predicado de filtro y estadísticas actualizadas reducirán el número de métodos de acceso físico porque puede ser más eficaz usar una búsqueda de índice que un examen. Esto también conducirá a una selección más pequeña de combinaciones, etc.

¿Por qué veo un tiempo de espera del optimizador con una consulta simple?

Nada con el optimizador de consultas es simple. Hay muchos escenarios posibles y el grado de complejidad es tan alto que es difícil comprender todas las posibilidades. El Optimizador de consultas puede establecer dinámicamente el umbral de tiempo de espera en función del costo del plan que se encuentra en una fase determinada. Por ejemplo, si se encuentra un plan que parece relativamente eficaz, es posible que se reduzca el límite de tareas para buscar un plan mejor. Por lo tanto, la estimación de cardinalidad subestimada (CE) puede ser un escenario para alcanzar un tiempo de espera del optimizador temprano. En este caso, el foco de la investigación es ce. Es un caso más raro en comparación con el escenario de ejecución de una consulta compleja que se describe en la sección anterior, pero es posible.

Soluciones

Un tiempo de espera del optimizador que aparece en un plan de consulta no significa necesariamente que sea la causa del rendimiento deficiente de las consultas. En la mayoría de los casos, es posible que no tenga que hacer nada sobre esta situación. El plan de consulta con el que termina SQL Server puede ser razonable y la consulta que está ejecutando puede estar funcionando bien. Es posible que nunca sepa que ha encontrado un tiempo de espera del optimizador.

Pruebe los pasos siguientes si encuentra la necesidad de optimizar y optimizar.

Paso 1: Establecer una línea base

Compruebe si puede ejecutar la misma consulta con el mismo conjunto de datos en una compilación diferente de SQL Server, mediante una configuración de CE diferente o en un sistema diferente (especificaciones de hardware). Un principio rector en el ajuste del rendimiento es "no hay ningún problema de rendimiento sin una línea base". Por lo tanto, sería importante establecer una línea base para la misma consulta.

Paso 2: Buscar condiciones "ocultas" que conducen al tiempo de espera del optimizador

Examine la consulta con detalle para determinar su complejidad. Tras el examen inicial, puede que no sea obvio que la consulta sea compleja e implique muchas combinaciones. Un escenario común aquí es que las vistas o las funciones con valores de tabla están implicadas. Por ejemplo, en la superficie, la consulta puede parecer sencilla porque combina dos vistas. Pero al examinar las consultas dentro de las vistas, es posible que cada vista se una a siete tablas. Como resultado, cuando se unen las dos vistas, termina con una combinación de 14 tablas. Si la consulta usa los siguientes objetos, explore en profundidad cada objeto para ver el aspecto de las consultas subyacentes que contiene:

En todos estos escenarios, la solución más común sería volver a escribir la consulta y dividirla en varias consultas. Consulte Paso 7: Refinar la consulta para obtener más detalles.

Subconsultas o tablas derivadas

La consulta siguiente es un ejemplo que combina dos conjuntos de consultas independientes (tablas derivadas) con 4-5 combinaciones en cada una. Sin embargo, después de analizar por SQL Server, se compilará en una sola consulta con ocho tablas unidas.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Expresiones de tabla comunes (CTE)

El uso de varias expresiones de tabla comunes (CTE) no es una solución adecuada para simplificar una consulta y evitar el tiempo de espera del optimizador. Varias CTE solo aumentarán la complejidad de la consulta. Por lo tanto, es contraproducente usar CTE al resolver los tiempos de espera del optimizador. Las CTE parecen interrumpir una consulta de forma lógica, pero se combinarán en una sola consulta y se optimizarán como una única combinación grande de tablas.

Este es un ejemplo de un CTE que se compilará como una sola consulta con muchas combinaciones. Puede parecer que la consulta en el my_cte es una combinación simple de dos objetos, pero de hecho, hay otras siete tablas unidas en el CTE.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Vistas

Asegúrese de que ha comprobado las definiciones de vista y ha obtenido todas las tablas implicadas. Al igual que las CTE y las tablas derivadas, las combinaciones se pueden ocultar dentro de las vistas. Por ejemplo, una combinación entre dos vistas puede ser, en última instancia, una sola consulta con ocho tablas implicadas:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Funciones con valores de tabla (TVF)

Algunas combinaciones pueden estar ocultas dentro de los TFV. En el ejemplo siguiente se muestra lo que aparece como una combinación entre dos TFV y una tabla puede ser una combinación de nueve tablas.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union

Los operadores de unión combinan los resultados de varias consultas en un único conjunto de resultados. También combinan varias consultas en una sola consulta. A continuación, puede obtener una única consulta compleja. El ejemplo siguiente terminará con un único plan de consulta que implica 12 tablas.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

Paso 3: Si tiene una consulta de línea base que se ejecuta más rápido, use su plan de consulta.

Si determina que un plan de línea de base determinado que obtiene del paso 1 es mejor para la consulta a través de pruebas, use una de las siguientes opciones para forzar qo a seleccionar ese plan:

Paso 4: Reducir las opciones de planes

Para reducir la posibilidad de un tiempo de espera del optimizador, intente reducir las posibilidades que qo necesita tener en cuenta al elegir un plan. Este proceso implica probar la consulta con diferentes opciones de sugerencia. Como sucede con la mayoría de las decisiones con QO, las opciones no siempre son deterministas en la superficie porque hay una gran variedad de factores que se deben tener en cuenta. Por lo tanto, no hay una única estrategia correcta garantizada y el plan seleccionado puede mejorar o reducir el rendimiento de la consulta seleccionada.

Forzar un pedido JOIN

Use OPTION (FORCE ORDER) para eliminar las permutaciones de orden:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

Reducir las posibilidades de JOIN

Si otras alternativas no han ayudado, intente reducir las combinaciones del plan de consulta limitando las opciones de operadores de combinaciones físicas con sugerencias de combinación. Por ejemplo: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) o OPTION (MERGE JOIN).

Nota: Debe tener cuidado al usar estas sugerencias.

En algunos casos, limitar el optimizador con menos opciones de combinación puede hacer que la mejor opción de combinación no esté disponible y, en realidad, ralentizar la consulta. Además, en algunos casos, un optimizador requiere una combinación específica (por ejemplo, objetivo de fila) y es posible que la consulta no genere un plan si esa combinación no es una opción. Por lo tanto, después de dirigirse a las sugerencias de combinación para una consulta específica, compruebe si encuentra una combinación que ofrezca un mejor rendimiento y elimine el tiempo de espera del optimizador.

Estos son dos ejemplos de cómo usar estas sugerencias:

Use OPTION (HASH JOIN, LOOP JOIN) para permitir solo combinaciones de hash y bucle y evitar la combinación de combinación en la consulta:
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

Aplicar una combinación específica entre dos tablas:

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

Paso 5: Cambio de la configuración de CE

Intente cambiar la configuración ce cambiando entre el CE heredado y el nuevo CE. El cambio de la configuración de CE puede dar lugar a que la QO elija una ruta de acceso diferente cuando SQL Server evalúa y crea planes de consulta. Por lo tanto, incluso si se produce un problema de tiempo de espera del optimizador, es posible que termine con un plan que funcione de forma más óptima que el seleccionado mediante la configuración de CE alternativa. Para obtener más información, vea Cómo activar el mejor plan de consulta (estimación de cardinalidad).

Paso 6: Habilitar correcciones del optimizador

Si no ha habilitado las correcciones del optimizador de consultas, considere la posibilidad de habilitarlas mediante uno de los dos métodos siguientes:

Nivel de servidor: use la marca de seguimiento T4199.
Nivel de base de datos: use ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON o cambie los niveles de compatibilidad de bases de datos para SQL Server 2016 y versiones posteriores.

Las correcciones de QO pueden hacer que el optimizador tome una ruta de acceso diferente en la exploración del plan. Por lo tanto, puede elegir un plan de consulta más óptimo. Para obtener más información, vea SQL Server modelo de mantenimiento de la marca de seguimiento de revisiones 4199 del optimizador de consultas.

Paso 7: Refinar la consulta

Considere la posibilidad de dividir la consulta de varias tablas en varias consultas independientes mediante tablas temporales. Dividir la consulta es solo una de las maneras de simplificar la tarea para el optimizador. Vea el ejemplo siguiente:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Para optimizar la consulta, intente dividir la consulta única en dos consultas insertando parte de los resultados de la combinación en una tabla temporal:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Share via