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

Artículo
12/17/2024

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 basado en costos (QO). Para obtener información sobre la calidad de servicio, 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 crear y evaluar varios planes de consulta. Uno de los objetivos del optimizador de consultas de SQL Server es dedicar un tiempo razonable en 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. Se notifica un evento de tiempo de espera del optimizador en el plan de consulta como Tiempo de espera en Motivo para la terminación anticipada 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 de QO de SQL Server, se consideran más de medio millón de tareas antes de que se alcance un tiempo de espera.

El tiempo de espera del optimizador está diseñado en 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 el rendimiento de las consultas más lento podría resultar. Cuando se producen estos problemas, comprender el mecanismo de tiempo de espera del optimizador y cómo pueden verse 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 para la optimización. Es decir, es posible que se hayan perdido planes que podrían producir tiempos de ejecución más cortos. La calidad de servicio se detendrá en el umbral y tendrá en cuenta el plan de consulta de menor costo en ese momento, aunque puede haber opciones mejores e inexploradas. 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 óptima.

¿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 combinadas (por ejemplo, ocho o más tablas están unidas).
Consulta lenta

La consulta puede ejecutarse lenta o más lenta que en otra versión o sistema de 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 izquierdo en Microsoft SQL Server Management Studio. Puede ver el valor de Reason For Early Termination of Statement Optimization is TimeOut(Motivo para la terminación anticipada de la optimización de instrucciones) es TimeOut.

¿Qué causa un tiempo de espera del optimizador?

No hay ninguna manera sencilla de determinar qué condiciones provocarían que se alcance o supere el umbral del optimizador. En las secciones siguientes se muestran algunos factores que afectan al número de planes que explora la calidad de servicio 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):
- Combinación Table1 con Table2 y el resultado con Table3
- Combinación Table1 con Table3 y el resultado con Table2
- Combinación Table2 con Table3 y el resultado con Table1
Nota: Cuanto mayor sea el número de tablas, mayor será la cantidad de posibilidades.
¿Qué estructura de acceso de montón o á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 se va a usar?
- Búsqueda de índice
- Examen de índice
- Examen de tablas
¿Qué operador de combinación física se va a usar?
- Combinación de bucles anidados (NJ)
- Combinación hash (HJ)
- Combinación de mezcla (MJ)
- Combinación adaptable (a partir de SQL Server 2017 (14.x))
Para más información, vea Combinaciones.
¿Ejecuta partes de la consulta en paralelo o en serie?

Para obtener más información, consulte 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 consideradas:

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 consideran los factores

Para ilustrarlo, tomemos un ejemplo de una combinación entre tres tablas (t1, t2y t3) y cada tabla tiene un índice agrupado y un índice no agrupado.

En primer lugar, tenga en cuenta los tipos de combinación física. Hay dos combinaciones implicadas aquí. Y, dado que hay tres posibilidades de combinació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:

Unirse t1 con t2 y, a continuación, con t3
Unirse t1 con t3 y, a continuación, con t2
Unirse t2 con t3 y, a continuación, con t1

A continuación, considere los índices agrupados y no clúster que se pueden 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 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 planes posibles.

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

Pedidos de unió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 estos elementos 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 del número de tablas implicadas en una consulta, el número de planes posibles aumenta geométricamente. Además, si incluye la posibilidad de paralelismo y otros factores, puede imaginar cuántos planes posibles se considerarán. Por lo tanto, es más probable que una consulta con una gran cantidad de combinaciones alcance el umbral de tiempo de espera del optimizador que uno con menos combinaciones.

Tenga en cuenta que los cálculos anteriores muestran 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 las estadísticas actualizadas reducirán el número de métodos de acceso físico, ya que puede ser más eficaz usar una búsqueda de índice que un examen. Esto también dará lugar a una selección más pequeña de combinaciones, etc.

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

Nada con el optimizador de consultas es sencillo. 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, se puede reducir el límite de tareas para buscar un plan mejor. Por lo tanto, la estimación de cardinalidad infravalorada (CE) puede ser un escenario para alcanzar un tiempo de espera del optimizador antes. En este caso, el foco de la investigación es CE. Es un caso más raro en comparación con el escenario sobre la ejecución de una consulta compleja que se describe en la sección anterior, pero es posible.

Resoluciones

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 necesite hacer nada sobre esta situación. El plan de consulta con el que SQL Server termina puede ser razonable y la consulta que está ejecutando puede funcionar bien. Es posible que nunca sepa que ha encontrado un tiempo de espera del optimizador.

Pruebe los pasos siguientes si encuentra la necesidad de ajustar 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 de 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 detalladamente la consulta para determinar su complejidad. Después del examen inicial, puede que no sea obvio que la consulta es compleja e implica muchas combinaciones. Un escenario común aquí es que las vistas o las funciones con valores de tabla intervienen. Por ejemplo, en la superficie, la consulta puede parecer sencilla porque combina dos vistas. Pero al examinar las consultas dentro de las vistas, puede encontrar que cada vista combina 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 dentro de él:

Para todos estos escenarios, la resolució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 independientes de consultas (tablas derivadas) con combinaciones de 4 a 5 en cada una. Sin embargo, después de analizar 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. Varios CTE solo aumentarán la complejidad de la consulta. Por lo tanto, es contraproductivo usar CTE al resolver los tiempos de espera del optimizador. Los CTE parecen dividir lógicamente una consulta, 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 siete otras tablas combinadas 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. De forma similar a 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 ...

Unión

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 sola consulta compleja. En el ejemplo siguiente se terminará con un único plan de consulta que implique 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 base determinado que obtiene del paso 1 es mejor para la consulta mediante pruebas, use una de las siguientes opciones para forzar la calidad de servicio para 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 el QO debe tener en cuenta al elegir un plan. Este proceso implica probar la consulta con diferentes opciones de sugerencia. Al igual que sucede con la mayoría de las decisiones con la calidad de servicio, 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 disminuir 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 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 provocar que la mejor opción de combinación no esté disponible y puede ralentizar realmente la consulta. Además, en algunos casos, un optimizador requiere una combinación específica (por ejemplo, un 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 establecer como destino las sugerencias de combinación de 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 hash y bucle y evitar combinar 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: Cambiar la configuración de CE

Intente cambiar la configuración de CE cambiando entre CE heredado y Nuevo CE. Cambiar la configuración de CE puede dar lugar a que el QO elija una ruta de acceso diferente cuando SQL Server evalúe y cree 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 realice un rendimiento más óptimo que el seleccionado mediante la configuración de CE alternativa. Para obtener más información, consulte 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 la base 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, consulte El modelo de mantenimiento 4199 del optimizador de consultas de SQL Server.

Paso 7: Refinar la consulta

Considere la posibilidad de dividir la consulta de varias tablas únicas en varias consultas independientes mediante tablas temporales. Dividir la consulta es solo una de las maneras de simplificar la tarea para el optimizador. Observe 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 la combinación da como resultado 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 ...

Compartir a través de