Consulta usando índice hora faz index scan outra hora faz index seek

Question

Estou com a consulta abaixo que identifiquei ser a motivadora de uma lentidão reclamada pelos usuários algum tempo. Analisado vi que o problema do tempo de execução estava na subconsulta abaixo:

    (         SELECT              COUNT(DISTINCT table_2.table_2_ctle_table_5) 
        FROM 
            table_2, table_3 
        WHERE 
            table_2.table_2_serie = table_3.table_3_table_2_serie 
            AND table_2.table_2_num = table_3.table_3_table_2 
            AND table_3.table_3_table_1_serie = table_1.table_1_serie 
            AND table_3.table_3_table_1 = table_1.table_1_num 
            AND table_2.table_2_ctle_table_5 IS NOT NULL
    ) AS guias

Como não é possível alterar a consulta, pois a mesma é internalizada no ERP, verifiquei o plano para saber se a subconsulta estava precisando de um índice. O índice que estava utilizando não estava bem formatado, pois verifiquei que estava usando o operador index scan e a as colunas chaves do índice não era tão seletivas. Ajustado o índice o mesmo passou a utilizar o operador index seek, o tempo de execução caiu de 53s para milissegundos e o leituras lógicas reduziu significativamente, até mesmo o custo que que estava de 72% no index scan foi para 1% usando o index seek. Importante dizer que analisei também as estatísticas e fragmentação e estavam os resultados ok. Passado uns 5 dias os usuários voltaram a reclamar da mesma rotina do ERP, no qual voltei a verificar a consulta e vi que o tempo de execução voltou a ficar em 53s, usando o índice que foi ajustado, porém fazendo index scan com 73% de custo novamente. Sem fragmentação, com estatísticas atualizadas fiquei um pouco confuso o que poderia está levando o otimizador do SQL Server ter optar em usar um plano com operador index scan, sendo que o mesmo índice estava usando o index seek. Então recriei o índice sem alterar nada em sua estrutura e para minha surpresa a consulta voltou a ser executado em milissegundos e usar o index seek. Lembrado que o mesmo cenário de ficar bom e ruim o tempo de execução, e de recriar o índice se repetiu por umas 3 vezes e ainda não conseguir identificar o real problema e solução. Queria um apoio dos colegas para entender o cenário e o que seria mais coerente analisar ou implementar para solucionar o problema. Fico agradecido.

DROP INDEX [idx_table_3_6_99465] ON [dbo].[table_3]
CREATE NONCLUSTERED INDEX [idx_table_3_6_99465] ON [dbo].[table_3]
(
	[table_3_fat_serie] ASC,
	[table_3_fat] ASC
)
INCLUDE([table_3_osm_serie],[table_3_osm],[table_3_num],[table_3_tpcod],[table_3_cod],[table_3_sfat],[table_3_str],[table_3_vlr],[table_3_cth_num],[table_3_cnv_cod],[table_3_usr_login_lanc])
WITH (ONLINE = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [FG_Indexes]

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  
    table_1.table_1_serie,  
    table_1.table_1_num,  
    table_1.table_1_dini,  
    table_1.table_1_dfin,  
    table_1.table_1_val,  
    table_1.table_1_stat,  
    table_1.table_1_table_5,  
    table_1.table_1_int_amb,  
    table_1.table_1_hsp_table_4,  
    table_1.table_1_hsp_num,  
    table_1.table_1_cth_num,  
    table_1.table_1_str_cod_sol,  
    table_1.table_1_lpf_serie,  
    table_1.table_1_lpf_num,  
    table_1.table_1_str_cod_exe,  
    table_1.table_1_tipo_table_1ura,  
    table_1.table_1_lta_cod,  
    table_1.table_1_table_2_atend,  
    (
        SELECT 
            COUNT(DISTINCT table_2.table_2_ctle_table_5) 
        FROM 
            table_2, table_3 
        WHERE 
            table_2.table_2_serie = table_3.table_3_table_2_serie 
            AND table_2.table_2_num = table_3.table_3_table_2 
            AND table_3.table_3_table_1_serie = table_1.table_1_serie 
            AND table_3.table_3_table_1 = table_1.table_1_num 
            AND table_2.table_2_ctle_table_5 IS NOT NULL
    ) AS guias,  
    table_4.table_4_pront,  
    table_4.table_4_nome,  
    table_1.table_1_pln_cod,  
    table_1.table_1_psv_cod,  
    table_1.table_1_usr_login  
FROM 
    table_1 
    LEFT OUTER JOIN table_4 ON table_1.table_1_hsp_table_4 = table_4.table_4_reg,  
    table_5  
WHERE 
    (table_1.table_1_table_5 = table_5.table_5_cod)  
    AND ( 
        (table_5.table_5_cod ='619')  
        AND (table_1_nfs_serie IS NULL)  
        AND (table_1_stat = 'P')  
        AND ( 
            table_1.table_1_val > 0  
            OR (table_1.table_1_ind_zerados = 'S') 
        )  
        AND ( 
            (table_5.table_5_emp_cod >= 1000 
            AND table_5.table_5_emp_cod <= 1000)  
            OR ( 
                EXISTS ( 
                    SELECT 
                        1 
                    FROM 
                        table_6 
                    WHERE 
                        table_6.table_6_table_5_cod = table_5.table_5_cod 
                        AND table_6.table_6_emp_cod >= 1000 
                        AND table_6.table_6_emp_cod <= 1000
                ) 
            )          )      );   

Answer 1

Oi Jerfeson Santos Barbosa!

Bem-vindo ao Microsoft Q&A!

Entendo que a situação que você está enfrentando pode ser bastante frustrante, especialmente quando o desempenho da consulta varia sem uma razão aparente. Vou tentar te ajudar a entender melhor o que pode estar acontecendo e sugerir algumas abordagens para resolver o problema.

Possíveis Causas e Soluções

1. Mudanças no Plano de Execução:
   • O SQL Server pode escolher diferentes planos de execução com base nas estatísticas e na carga de trabalho atual. Mesmo pequenas mudanças nos dados ou nas estatísticas podem fazer com que o otimizador de consultas escolha um plano diferente. Verifique se as estatísticas estão atualizadas regularmente e considere usar a opção UPDATE STATISTICS para garantir que o otimizador tenha as informações mais recentes.
2. Parâmetros de Consulta:
   • Se a consulta usa parâmetros, o SQL Server pode estar sofrendo de "parameter sniffing", onde o plano de execução é otimizado para um conjunto específico de valores de parâmetros, mas não funciona bem para outros valores. Uma solução pode ser usar a opção OPTION (RECOMPILE) para forçar a recompilação da consulta a cada execução, garantindo que o plano de execução seja otimizado para os valores atuais dos parâmetros.
3. Fragmentação de Índices:
   • Embora você tenha mencionado que a fragmentação não é um problema, é importante garantir que os índices sejam reconstruídos ou reorganizados regularmente. A fragmentação pode afetar o desempenho da consulta e a escolha do plano de execução.
4. Cache de Plano de Execução:
   • O SQL Server armazena planos de execução em cache para reutilização. No entanto, se o plano em cache não for ideal para a carga de trabalho atual, pode ser necessário limpar o cache de planos de execução usando o comando DBCC FREEPROCCACHE. Isso pode forçar o SQL Server a recompilar a consulta e potencialmente escolher um plano de execução mais eficiente.
5. Configurações de Banco de Dados:
   • Verifique as configurações do banco de dados, como o nível de compatibilidade e as opções de otimização de consulta. Certifique-se de que estão configuradas de acordo com as melhores práticas para o SQL Server.

Exemplos de Comandos Úteis

• Atualizar Estatísticas:

    UPDATE STATISTICS table_3;
  

• Consulta de reconstrução:

    SELECT 
        COUNT(DISTINCT table_2.table_2_ctle_table_5) 
    FROM 
        table_2, table_3 
    WHERE 
        table_2.table_2_serie = table_3.table_3_table_2_serie 
        AND table_2.table_2_num = table_3.table_3_table_2 
        AND table_3.table_3_table_1_serie = table_1.table_1_serie 
        AND table_3.table_3_table_1 = table_1.table_1_num 
        AND table_2.table_2_ctle_table_5 IS NOT NULL
    OPTION (RECOMPILE);
  

• Limpar Cache de Plano de Execução:

Estatísticas

Query Dicas (Transact-SQL)

Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos

DBCC FREEPROCCACHE (Transact-SQL)

Espero que essas dicas ajudem a resolver o problema! Se precisar de mais assistência, estou à disposição.

Saudações

Jonathan.

--__-__-------

Sua opinião é muito importante para nós! Se esta resposta resolveu sua consulta, por favor clique em ‘YES‘. Isso nos ajuda a melhorar continuamente a qualidade e relevância de nossas soluções. Obrigado pela sua colaboração!

Answer 2

A consulta não pode ser alterada para forçar a recompilação do plano, ou seja, adicionar um Option (recompile), além de que o trecho abaixo faz parte da consulta principal como subselect do SELECT.

As estatísticas são atualizadas semanalmente por ser um ambiente muito transacional. Não cogitei limpar o cache da tabela table_3 pelo fato de não entender que o problema possa ser isso. O ambiente foi migrado e mesmo assim o problema persisti do ambiente antigo para novo. Com cache limpa.

SELECT 
      COUNT(DISTINCT table_2.table_2_ctle_table_5) 
  FROM 
      table_2, table_3 
  WHERE 
      table_2.table_2_serie = table_3.table_3_table_2_serie 
      AND table_2.table_2_num = table_3.table_3_table_2 
      AND table_3.table_3_table_1_serie = table_1.table_1_serie 
      AND table_3.table_3_table_1 = table_1.table_1_num 
      AND table_2.table_2_ctle_table_5 IS NOT NULL
  OPTION (RECOMPILE);

Answer 3

Hola Jerfeson Santos Barbosa,

Entendo a complexidade da situação, especialmente quando não é possível alterar a consulta diretamente. Vamos explorar algumas outras abordagens que podem ajudar a resolver o problema de desempenho sem modificar a consulta.

Possíveis Causas e Soluções

1. Mudanças no Plano de Execução:
   • O SQL Server pode escolher diferentes planos de execução com base nas estatísticas e na carga de trabalho atual. Mesmo pequenas mudanças nos dados ou nas estatísticas podem fazer com que o otimizador de consultas escolha um plano diferente. Verifique se as estatísticas estão atualizadas regularmente e considere usar a opção UPDATE STATISTICS para garantir que o otimizador tenha as informações mais recentes.
2. Parâmetros de Consulta:
   • Se a consulta usa parâmetros, o SQL Server pode estar sofrendo de "parameter sniffing", onde o plano de execução é otimizado para um conjunto específico de valores de parâmetros, mas não funciona bem para outros valores. Uma solução pode ser usar a opção OPTION (RECOMPILE) para forçar a recompilação da consulta a cada execução, garantindo que o plano de execução seja otimizado para os valores atuais dos parâmetros.
3. Fragmentação de Índices:
   • Embora você tenha mencionado que a fragmentação não é um problema, é importante garantir que os índices sejam reconstruídos ou reorganizados regularmente. A fragmentação pode afetar o desempenho da consulta e a escolha do plano de execução.
4. Cache de Plano de Execução:
   • O SQL Server armazena planos de execução em cache para reutilização. No entanto, se o plano em cache não for ideal para a carga de trabalho atual, pode ser necessário limpar o cache de planos de execução usando o comando DBCC FREEPROCCACHE. Isso pode forçar o SQL Server a recompilar a consulta e potencialmente escolher um plano de execução mais eficiente.
   Configurações de Banco de Dados:
   • Verifique as configurações do banco de dados, como o nível de compatibilidade e as opções de otimização de consulta5. Certifique-se de que estão configuradas de acordo com as melhores práticas para o SQL Server.

Exemplos de Comandos Úteis

Atualizar Estatísticas:

UPDATE STATISTICS table_3;

Consulta de Reconstrução:

SELECT 
    COUNT(DISTINCT table_2.table_2_ctle_table_5) 
FROM 
    table_2, table_3 
WHERE 
    table_2.table_2_serie = table_3.table_3_table_2_serie 
    AND table_2.table_2_num = table_3.table_3_table_2 
    AND table_3.table_3_table_1_serie = table_1.table_1_serie 
    AND table_3.table_3_table_1 = table_1.table_1_num 
    AND table_2.table_2_ctle_table_5 IS NOT NULL
OPTION (RECOMPILE);

• Limpar Cache de Plano de Execução:

    DBCC FREEPROCCACHE
  

Respetuosamente,

Jonathan

---------

Tu opinión es muy importante para nosotros! Si esta respuesta resolvió tu consulta, por favor haz clic en ‘Sí’. Esto nos ayuda a mejorar continuamente la calidad y relevancia de nuestras soluciones. ¡Gracias por tu colaboración!