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

Artigo
03/24/2023

Aplica-se ao: 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?

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 ter criado e avaliado vários planos de consulta. Um dos objetivos do SQL Server Otimizador de Consultas é gastar um tempo razoável na otimização de consulta em comparação com a execução de consulta. Otimizar uma consulta deve ser muito mais rápido do que executá-la. Para atingir esse destino, 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 Razão para Término Antecipado da Otimização de Instruções. É importante entender que esse limite não se baseia na hora do relógio, mas no número de possibilidades consideradas pelo otimizador. Nas versões de QO SQL Server atuais, mais de meio milhão de tarefas são consideradas antes que um tempo limite seja atingido.

O Tempo limite do otimizador foi projetado para 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 o desempenho de consulta mais lento pode resultar. Quando você encontra esses problemas, entender o mecanismo de tempo limite do otimizador e como consultas complexas podem ser afetadas pode ajudá-lo a solucionar problemas e melhorar sua velocidade de consulta.

O resultado de atingir o limite de tempo limite do Otimizador é que SQL Server não considerou todo o conjunto de possibilidades para otimização. Ou seja, pode ter perdido planos que poderiam produzir tempos de execução mais curtos. O QO vai parar no limite e considerar o plano de consulta de menor custo nesse ponto, mesmo que possa haver opções melhores e inexploradas. Tenha em mente que o plano selecionado após o alcance de um Tempo limite do Otimizador 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 que é suboptimal.

Como detectar um tempo limite de otimização?

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

Consulta complexa

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

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

O plano de consulta mostra StatementOptmEarlyAbortReason=Timeout

O plano de consulta é exibido 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 Razão para Término Antecipado da Otimização de Instrução é TimeOut.

O que causa um tempo limite de otimização?

Não há uma maneira simples de determinar quais condições fariam com que o limite de 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
- Ingressar Table1 com Table2 e o resultado com Table3
- Ingressar Table1 com Table3 e o resultado com Table2
- Ingressar Table2 com Table3 e o resultado com Table1
Nota: Quanto maior for o número de tabelas, maiores são as possibilidades.
Qual estrutura de acesso hoBT (heap ou árvore binária) usar para recuperar as linhas de uma tabela?
- Índice clusterizado
- Índice nãocluso1
- Índice nãocluso2
- Heap de tabela
Qual método de acesso físico usar?
- Busca de índice
- Verificação de índice
- Verificação 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, consulte Junções.
Executar partes da consulta em paralelo ou serialmente?

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 projetadas e atualizadas

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

Exemplo de como os fatores são considerados

Para ilustrar, vamos dar um exemplo de junção entre três tabelas (t1, t2e 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, portanto, pode haver P(3,1) = 3 possibilidades:

Junte-se t1 com t2 e, em seguida, com t3
Junte-se t1 com t3 e, em seguida, com t2
Junte-se t2 com t3 e, em seguida, com t1

Em seguida, considere os índices clusterizados e não clusterizados que poderiam ser usados para recuperação de dados. Além disso, para cada índice, temos dois métodos de acesso, busca ou verificação. Isso significa que, para cada tabela, há^{2 2} = 4 opções. Temos três tabelas, portanto, pode haver 4³ = 64 opções.

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

Agora, vamos supor que há n tabelas ingressadas na consulta e cada tabela tem um índice clusterizado e um índice não clusterizado. Considere os fatores a seguir:

Pedidos de junção: P(n,n-2) = n!/2
Tipos^{de junção: 3 n-1}
Tipos de índice diferentes com métodos de pesquisa e verificação: 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. Portanto, 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, você pode imaginar quantos planos possíveis serão considerados. Portanto, uma consulta com muitas junções é mais provável que atinja o limite de tempo limite de otimização do que uma com menos junções.

Observe que os cálculos acima ilustram o pior cenário. Como apontamos, há 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 Otimizador de Consultas é simples. Há 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 tarefa para procurar um plano melhor poderá ser reduzido. Portanto, a estimativa de cardinalidade subestimada (CE) pode ser um cenário para atingir um tempo limite do otimizador mais cedo. Nesse caso, o foco da investigação é CE. É um caso mais raro comparado com o cenário sobre a execução de uma consulta complexa que é discutida na seção anterior, mas é possível.

Resoluções

Um Tempo limite de otimização que aparece em um plano de consulta não significa necessariamente que seja a causa do fraco desempenho da consulta. Na maioria dos casos, talvez você não precise fazer nada sobre essa situação. O plano de consulta que SQL Server acaba com 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.

Experimente as etapas a seguir se você encontrar a necessidade de 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 um build diferente de SQL Server, usando uma configuração de CE diferente ou em um sistema diferente (especificações de hardware). Um princípio norteador no ajuste de desempenho é "não há nenhum 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 levem 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 é complexa e envolve muitas junções. Um cenário comum aqui é que as 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 dos modos de exibição, você pode descobrir que cada exibição une sete tabelas. Como resultado, quando as duas exibições são ingressadas, você acaba com uma junção de 14 tabelas. Se a consulta usar os seguintes objetos, faça uma detalhamento 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 junções 4-5 em cada. No entanto, após a análise por SQL Server, ele será compilado em uma única consulta com oito tabelas ingressadas.

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

Expressões de tabela comuns (CTEs)

Usar várias CTEs (expressões comuns de tabela) 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 tempo limite do otimizador. As CTEs parecem quebrar uma consulta logicamente, mas elas serão combinadas em uma única consulta e otimizadas como uma única grande junção de tabelas.

Aqui está um exemplo de um CTE que será compilado 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á outras sete tabelas unidas no 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 a CTEs e tabelas derivadas, as junções podem ser ocultadas dentro de exibições. Por exemplo, uma junção entre duas exibições pode, em última análise, 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 duas 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 ...

União

Os operadores sindicais combinam os resultados de várias consultas em um único conjunto de resultados. Eles também combinam várias consultas em uma única consulta. Em seguida, você pode obter uma consulta complexa e única. O exemplo a seguir acabará 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 que você obtém 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: Reduzir 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 pedido:

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

Reduzir as possibilidades de JOIN

Se outras alternativas não ajudarem, tente reduzir as combinações do plano de consulta limitando as opções de operadores de junção física 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 reduzir a velocidade da consulta. Além disso, em alguns casos, uma junção específica é necessária por um otimizador (por exemplo, meta de linha) e a consulta pode não 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, marcar se encontrar 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 a junção de mesclagem na consulta:
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

Impor 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 de CE

Tente alterar a configuração de CE alternando entre o Legacy CE e o New CE. A alteração da configuração de CE pode fazer com que o QO escolha um caminho diferente quando 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 que tenha um desempenho mais ideal do que o selecionado usando a configuração de CE alternativa. Para obter mais informações, consulte Como ativar o melhor plano de consulta (Estimativa de Cardinalidade).

Etapa 6: Habilitar correções de otimizador

Se você ainda não tiver habilitado correções do Otimizador de Consultas, 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 versões SQL Server 2016 e 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 SQL Server modelo de manutenção do sinalizador de rastreamento de hotfix do otimizador de consulta 4199.

Etapa 7: Refinar a consulta

Considere dividir a consulta de várias tabelas em várias consultas separadas usando tabelas temporárias. Interromper a consulta é apenas uma das maneiras de simplificar a tarefa para o otimizador. Veja 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