Solucionar problemas de consultas lentas afetadas pelo tempo limite do otimizador de consulta

Artigo
12/14/2024

Aplica-se a: SQL Server

Este artigo apresenta o Tempo Limite do Otimizador, como ele pode afetar o desempenho da consulta e como otimizar o desempenho.

O que é o tempo limite do otimizador?

O SQL Server usa um QO (Otimizador de Consulta) baseado em custo. Para obter informações sobre QO, consulte Guia de arquitetura de processamento de consulta. Um otimizador de consulta baseado em custo seleciona um plano de execução de consulta com o menor custo depois de criar e avaliar vários planos de consulta. Um dos objetivos do SQL Server Query Optimizer é gastar um tempo razoável na otimização de consultas em comparação com a execução de consultas. Otimizar uma consulta deve ser muito mais rápido do que executá-la. Para atingir essa meta, o QO tem um limite interno de tarefas a serem consideradas antes de interromper o processo de otimização. Quando o limite é atingido antes que o QO tenha considerado todos os planos possíveis, ele atinge o limite de tempo limite do otimizador. Um evento de Tempo Limite do Otimizador é relatado no plano de consulta como TimeOut em Motivo do Encerramento Antecipado da Otimização de Instrução. É importante entender que esse limite não se baseia no tempo do relógio, mas no número de possibilidades consideradas pelo otimizador. Nas versões atuais do SQL Server QO, mais de meio milhão de tarefas são consideradas antes que um tempo limite seja atingido.

O Tempo Limite do Otimizador foi projetado no SQL Server e, em muitos casos, não é um fator que afeta o desempenho da consulta. No entanto, em alguns casos, a escolha do plano de consulta SQL pode ser afetada negativamente pelo Tempo Limite do Otimizador e pode resultar em um desempenho de consulta mais lento. Quando você encontra esses problemas, entender o mecanismo de tempo limite do otimizador e como as consultas complexas podem ser afetadas pode ajudá-lo a solucionar problemas e melhorar a velocidade da consulta.

O resultado de atingir o limite de Tempo Limite do Otimizador é que o SQL Server não considerou todo o conjunto de possibilidades de otimização. Ou seja, pode ter perdido planos que poderiam produzir tempos de execução mais curtos. O QO parará no limite e considerará o plano de consulta de menor custo nesse ponto, mesmo que possa haver opções melhores e inexploradas. Lembre-se de que o plano selecionado após um Tempo limite do otimizador ser atingido pode produzir uma duração de execução razoável para a consulta. No entanto, em alguns casos, o plano selecionado pode resultar em uma execução de consulta abaixo do ideal.

Como detectar um tempo limite do otimizador?

Aqui estão os sintomas que indicam um tempo limite do otimizador:

Consulta complexa

Você tem uma consulta complexa que envolve muitas tabelas associadas (por exemplo, oito ou mais tabelas são associadas).
Consulta lenta

A consulta pode ser executada lentamente ou mais lentamente do que em outra versão ou sistema do SQL Server.

O plano de consulta mostra StatementOptmEarlyAbortReason=Timeout

O plano de consulta é mostrado StatementOptmEarlyAbortReason="TimeOut" no plano 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>

Verifique as propriedades do operador de plano mais à esquerda no Microsoft SQL Server Management Studio. Você pode ver que o valor de Motivo para o encerramento antecipado da otimização da instrução é Tempo limite.

O que causa um tempo limite do otimizador?

Não há uma maneira simples de determinar quais condições fariam com que o limite do otimizador fosse atingido ou excedido. As seções a seguir são alguns fatores que afetam quantos planos são explorados pelo QO ao procurar o melhor plano.

Em que ordem as tabelas devem ser unidas?

Aqui está um exemplo das opções de execução de junções de três tabelas (Table1, Table2, Table3):
- Junte Table1 com Table2 e o resultado com Table3
- Junte Table1 com Table3 e o resultado com Table2
- Junte Table2 com Table3 e o resultado com Table1
Nota: Quanto maior o número de mesas, maiores são as possibilidades.
Qual estrutura de acesso de heap ou árvore binária (HoBT) usar para recuperar as linhas de uma tabela?
- Índice clusterizado
- Índice não clusterizado1
- Índice não clusterizado2
- Pilha de tabela
Qual método de acesso físico usar?
- Busca de índice
- Verificação de índice
- Varredura de tabela
Qual operador de junção física usar?
- Junção de loops aninhados (NJ)
- Junção de hash (HJ)
- Junção de mesclagem (MJ)
- Junção adaptável (começando com SQL Server 2017 (14.x))
Para obter mais informações, confira as Junções.
Executar partes da consulta em paralelo ou em série?

Para obter mais informações, consulte Processamento de consulta paralela.

Embora os seguintes fatores reduzam o número de métodos de acesso considerados e, portanto, as possibilidades consideradas:

Predicados de consulta (filtros na WHERE cláusula)
Existências de restrições
Combinações de estatísticas bem elaboradas e atualizadas

Observação: o fato de o QO atingir o limite não significa que ele terminará com uma consulta mais lenta. Na maioria dos casos, a consulta terá um bom desempenho, mas, em alguns casos, você poderá ver uma execução de consulta mais lenta.

Exemplo de como os fatores são considerados

Para ilustrar, vamos dar um exemplo de uma junção entre três tabelas (t1, t2, e t3) e cada tabela tem um índice clusterizado e um índice não clusterizado.

Primeiro, considere os tipos de junção física. Há duas junções envolvidas aqui. E, como há três possibilidades de junção física (NJ, HJ e MJ), a consulta pode ser executada de 3² = 9 maneiras.

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

Em seguida, considere a ordem de junção, que é calculada usando Permutações: P (n, r). A ordem das duas primeiras tabelas não importa, então pode haver P(3,1) = 3 possibilidades:

Junte-se t1 com t2 e depois com t3
Junte-se t1 com t3 e depois com t2
Junte-se t2 com t3 e depois com t1

Em seguida, considere os índices clusterizados e não clusterizados que podem ser usados para recuperação de dados. Além disso, para cada índice, temos dois métodos de acesso, seek ou scan. Isso significa que, para cada tabela, existem 2² = 4 opções. Temos três tabelas, então pode haver 4³ = 64 opções.

Finalmente, considerando todas essas condições, pode haver 9 * 3 * 64 = 1728 planos possíveis.

Agora, vamos supor que haja n tabelas unidas na consulta e que cada tabela tenha um índice clusterizado e um índice não clusterizado. Considere os seguintes fatores:

Ordens de junção: P(n,n-2) = n!/2
Tipos de junção: 3^n-1
Diferentes tipos de índice com métodos de busca e varredura: 4ⁿ

Multiplique tudo isso acima e podemos obter o número de planos possíveis: 2*n!*12^n-1. Quando n = 4, o número é 82.944. Quando n = 6, o número é 358.318.080. Assim, com o aumento do número de tabelas envolvidas em uma consulta, o número de planos possíveis aumenta geometricamente. Além disso, se você incluir a possibilidade de paralelismo e outros fatores, poderá imaginar quantos planos possíveis serão considerados. Portanto, uma consulta com muitas junções tem mais probabilidade de atingir o limite de tempo limite do otimizador do que uma com menos junções.

Observe que os cálculos acima ilustram o pior cenário. Como apontamos, existem fatores que reduzirão o número de possibilidades, como predicados de filtro, estatísticas e restrições. Por exemplo, um predicado de filtro e estatísticas atualizadas reduzirão o número de métodos de acesso físico porque pode ser mais eficiente usar uma busca de índice do que uma verificação. Isso também levará a uma seleção menor de junções e assim por diante.

Por que vejo um Tempo limite do otimizador com uma consulta simples?

Nada com o Query Optimizer é simples. Existem muitos cenários possíveis, e o grau de complexidade é tão alto que é difícil entender todas as possibilidades. O Otimizador de Consulta pode definir dinamicamente o limite de tempo limite com base no custo do plano encontrado em um determinado estágio. Por exemplo, se um plano que parece relativamente eficiente for encontrado, o limite de tarefas para procurar um plano melhor pode ser reduzido. Portanto, a estimativa de cardinalidade (CE) subestimada pode ser um cenário para atingir um tempo limite do otimizador antecipadamente. Neste caso, o foco da investigação é a EC. É um caso mais raro em comparação com o cenário sobre a execução de uma consulta complexa discutido na seção anterior, mas é possível.

Resoluções

Um Tempo Limite do Otimizador que aparece em um plano de consulta não significa necessariamente que ele é a causa do baixo desempenho da consulta. Na maioria dos casos, talvez você não precise fazer nada sobre essa situação. O plano de consulta com o qual o SQL Server termina pode ser razoável e a consulta que você está executando pode estar tendo um bom desempenho. Talvez você nunca saiba que encontrou um tempo limite do otimizador.

Tente as etapas a seguir se achar necessário ajustar e otimizar.

Etapa 1: Estabelecer uma linha de base

Verifique se você pode executar a mesma consulta com o mesmo conjunto de dados em uma compilação diferente do SQL Server, usando uma configuração CE diferente ou em um sistema diferente (especificações de hardware). Um princípio orientador no ajuste de desempenho é "não há problema de desempenho sem uma linha de base". Portanto, seria importante estabelecer uma linha de base para a mesma consulta.

Etapa 2: Procure condições "ocultas" que levam ao tempo limite do otimizador

Examine sua consulta em detalhes para determinar sua complexidade. Após o exame inicial, pode não ser óbvio que a consulta seja complexa e envolva muitas junções. Um cenário comum aqui é que exibições ou funções com valor de tabela estão envolvidas. Por exemplo, na superfície, a consulta pode parecer simples porque une duas exibições. Mas ao examinar as consultas dentro das exibições, você pode descobrir que cada exibição une sete tabelas. Como resultado, quando as duas exibições são unidas, você acaba com uma junção de 14 tabelas. Se sua consulta usar os seguintes objetos, faça uma busca detalhada em cada objeto para ver como são as consultas subjacentes dentro dele:

Para todos esses cenários, a resolução mais comum seria reescrever a consulta e dividi-la em várias consultas. Consulte Etapa 7: Refinar a consulta para obter mais detalhes.

Subconsultas ou tabelas derivadas

A consulta a seguir é um exemplo que une dois conjuntos separados de consultas (tabelas derivadas) com 4 a 5 junções em cada uma. No entanto, após a análise pelo SQL Server, ele será compilado em uma única consulta com oito tabelas associadas.

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

CETs (expressões de tabela comuns)

O uso de várias CTEs (expressões de tabela comuns) não é uma solução apropriada para simplificar uma consulta e evitar o tempo limite do otimizador. Várias CTEs só aumentarão a complexidade da consulta. Portanto, é contraproducente usar CTEs ao resolver tempos limite do otimizador. As CTEs parecem quebrar uma consulta logicamente, mas serão combinadas em uma única consulta e otimizadas como uma única junção grande de tabelas.

Aqui está um exemplo de uma CTE que será compilada como uma única consulta com muitas junções. Pode parecer que a consulta no my_cte é uma junção simples de dois objetos, mas, na verdade, há sete outras tabelas unidas na 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 ...

Exibições

Verifique se você verificou as definições de exibição e envolveu todas as tabelas. Semelhante às CTEs e tabelas derivadas, as junções podem ser ocultadas dentro de exibições. Por exemplo, uma junção entre duas exibições pode ser uma única consulta com oito tabelas envolvidas:

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 ...

Funções com valor de tabela (TVFs)

Algumas junções podem estar ocultas dentro de TFVs. O exemplo a seguir mostra o que aparece como uma junção entre dois TFVs e uma tabela pode ser uma junção de nove tabelas.

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

Os operadores de união combinam os resultados de várias consultas em um único conjunto de resultados. Eles também combinam várias consultas em uma única consulta. Então você pode obter uma consulta única e complexa. O exemplo a seguir terminará com um único plano de consulta que envolve 12 tabelas.

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 ...

Etapa 3: Se você tiver uma consulta de linha de base que seja executada mais rapidamente, use seu plano de consulta

Se você determinar que um plano de linha de base específico obtido da Etapa 1 é melhor para sua consulta por meio de testes, use uma das seguintes opções para forçar o QO a selecionar esse plano:

Etapa 4: Reduza as opções de planos

Para reduzir a chance de um Tempo Limite do Otimizador, tente reduzir as possibilidades que o QO precisa considerar na escolha de um plano. Esse processo envolve testar a consulta com diferentes opções de dica. Como acontece com a maioria das decisões com QO, as escolhas nem sempre são determinísticas na superfície porque há uma grande variedade de fatores a serem considerados. Portanto, não há uma única estratégia bem-sucedida garantida e o plano selecionado pode melhorar ou diminuir o desempenho da consulta selecionada.

Forçar uma ordem JOIN

Use OPTION (FORCE ORDER) para eliminar as permutações de ordem:

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

Reduza as possibilidades de JOIN

Se outras alternativas não ajudaram, tente reduzir as combinações de plano de consulta limitando as opções de operadores de junções físicas com dicas de junção. Por exemplo: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) ou OPTION (MERGE JOIN).

Nota: Você deve ter cuidado ao usar essas dicas.

Em alguns casos, limitar o otimizador com menos opções de junção pode fazer com que a melhor opção de junção não esteja disponível e pode realmente tornar a consulta mais lenta. Além disso, em alguns casos, uma junção específica é exigida por um otimizador (por exemplo, meta de linha) e a consulta pode falhar ao gerar um plano se essa junção não for uma opção. Portanto, depois de direcionar as dicas de junção para uma consulta específica, verifique se você encontra uma combinação que ofereça melhor desempenho e elimine o Tempo Limite do Otimizador.

Aqui estão dois exemplos de como usar essas dicas:

Use OPTION (HASH JOIN, LOOP JOIN) para permitir apenas junções de hash e loop e evitar junções de mesclagem na consulta:
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

Imponha uma junção específica entre duas tabelas:

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

Etapa 5: alterar a configuração do CE

Tente alterar a configuração do CE alternando entre o CE herdado e o novo CE. Alterar a configuração do CE pode fazer com que o QO escolha um caminho diferente quando o SQL Server avalia e cria planos de consulta. Portanto, mesmo que ocorra um problema de Tempo Limite do Otimizador, é possível que você acabe com um plano com desempenho mais otimizado do que o selecionado usando a configuração alternativa do CE. Para obter mais informações, consulte Como ativar o melhor plano de consulta (Estimativa de Cardinalidade).

Etapa 6: Ativar correções do Optimizer

Se você não tiver habilitado as correções do Otimizador de Consulta, considere habilitá-las usando um dos dois métodos a seguir:

Nível do servidor: use o sinalizador de rastreamento T4199.
Nível do banco de dados: use ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON ou altere os níveis de compatibilidade do banco de dados para o SQL Server 2016 e versões posteriores.

As correções de QO podem fazer com que o otimizador siga um caminho diferente na exploração do plano. Portanto, ele pode escolher um plano de consulta mais ideal. Para obter mais informações, consulte Modelo de manutenção 4199 do sinalizador de rastreamento de hotfix do otimizador de consulta do SQL Server.

Etapa 7: refinar a consulta

Considere dividir a consulta única de várias tabelas em várias consultas separadas usando tabelas temporárias. Dividir a consulta é apenas uma das maneiras de simplificar a tarefa para o otimizador. Consulte o seguinte exemplo:

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 otimizar a consulta, tente dividir a consulta única em duas consultas inserindo parte dos resultados da junção em uma tabela temporária:

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 ...

Compartilhar via