none
Problemas com performance em consulta no SQL Server RRS feed

  • Pergunta

  • Pessoal,

    Estou com um problema de performance em uma das consultas nos bancos de dados SQL Server. Tenho o cenário descrito abaixo e gostaria de saber se alguém já se deparou com este problema ou se saberia o motivo.

    A query (abaixo) possui filtros, onde o campo de data DTLOG tem índice e o campo TXLOGDETALHE não possui índice e seu tipo de dados é TEXT.

    Ocorre a seguinte situação:

    Pelo plano de execução identificamos que o otimizador opta por não utilizar o índice existente para o campo de data (DTLOG) e com isso faz um FULLSCAN na segunda tabela do JOIN (FMLOGDET) que possui milhões de registros usando a PK (conforme imagem abaixo).

    Mesmo forçando o otimizador usar o índice da coluna DTLOG,  ele continua fazendo o FULLSCAN pela PK na tabela FMLOGDET e com isso a performance continua ruim.

     

    DÚVIDA: Porque mesmo filtrando por apenas 1 dia, o otimizador continua fazendo o FULLSCAN na tabela (FMLOGDET), onde colocamos o LIKE, e não utiliza o índice criado para o campo DTLOG da tabela (FMLOG), diminuindo assim o esforço?

    Pela lógica, se ele filtrasse pela data primeiro, teria uma quantidade de registros menor para o segundo filtro e com isso o custo seria dentro do esperado. Mas ele executa o caminho inverso (as estatísticas estão atualizadas).

    Uma observação, quando colocamos no filtro DTLOG, uma data maior que o dia atual, o retorno da query é instantâneo e no plano mostra que o índice é utilizado.  

    QUERY:

    SELECT MLOG.IDLOG,            

    MLOG.DTLOG,                 

    MLOG.HRLOG,                 

    MLOG.CDMODULOSISTEMA

    FROM GKOSCF.FMLOG MLOG

    INNER JOIN GKOSCF.FMLOGDET MLOGDETALHE ON (MLOGDETALHE.IDLOG = MLOG.IDLOG)

    WHERE MLOG.DTLOG BETWEEN '10-MAY-2019' AND '10-MAY-2019' 

    AND MLOGDETALHE.TXLOGDETALHE LIKE '%TR_FMCONHEC_UPD2%' 

    ORDER BY MLOG.IDLOG;

      

    Os teste e configurações abaixo foram realizados, mas não obtivemos sucesso:

     

    1. Executamos a mesma query nas versões 2012, 2014 e 2016 com SP atualizados, mas em todas as versões apresentou o mesmo caso;

    2. Sabemos que existe um índice especifico para o tipo de dados TEXT no SQL Server, mas não podemos adotar esta solução;

    3. Alteramos a query com JOIN, EXISTS, BETWEEN, <=, >= em nenhuma das alterações houve melhora;

    4. Já conferimos a configuração Max Degree of Paralelism e está baseado no cálculo processadores/núcleos;

    5. A análise dos traces pelo Tuning Advisor simplesmente não geram recomendações;

    6. Os objetos não estão com porcentual de fragmentação, a manutenção é executada semanalmente;

    segunda-feira, 29 de julho de 2019 20:25

Todas as Respostas

  • gkohm,

    Antes de darmos início, quero somente direcionar algumas questões:

    1 - Em qual versão do SQL Server você vai fazer uso de forma oficial desta query?

    2 - Qual é a versão atual do nível de compatibilidade deste banco de dados?

    3 - Este ambiente foi migrado de alguma outra versão?

    4 - Estamos se referindo a máquina física ou virtual?

    Seguindo em frente, temos um cenário aqui bastante comum de ocorrer quando nosso banco de dados apresenta comportamento um pouco fora do comum quando trabalhamos com faixas de valores, as coisas podem ser as mais variadas, mas inicialmente vou focar neste trecho do seu post:

    "Uma observação, quando colocamos no filtro DTLOG, uma data maior que o dia atual, o retorno da query é instantâneo e no plano mostra que o índice é utilizado."

    Pois bem, este é o tipo de comportamento que pode ocorrer quando as estásticas das nossas tabelas, bem como, as estatísticas das tabelas internas do nosso banco de dados estão possívelmente desatualizadas, se realmente for este o cenário, podemos verificar cada uma das tabelas envolvidas nesta query, analisar o histograma ou simplesmente forçar uma atualização das estatísticas.

    Se partirmos para análise referente a estatística, gostaria de sugerir o primeiro passo, que consiste em identificar se as estatísticas utilizadas por estas tabelas estão atualizadas ou não, através do uso da função Stats_Date introduzida a partir do SQL Server 2012 que nos permite justamente obter informações sobre as atualizações de estatísticas existentes em nossas tabelas:

    -- Obtendo informações sobre as estatísticas --
    SELECT name AS stats_name, STATS_DATE(object_id, stats_id) AS statistics_update_date FROM sys.stats order by statistics_update_date Desc
    Um outro exemplo que poderá nos ajudar apresento abaixo, o que nos permite obter informações sobre as estatísticas de tabelas e índices que estão a mais de 30 dias desatualizadas, combinando a função STATS_Date em conjunto com a Visão de Sistema sys.stats:
    -- Estatasticas com mais de 30 dias sem atualização --
    SELECT  [LastUpdate] = STATS_DATE(object_id, stats_id), 
            [Table] = OBJECT_NAME(object_id), 
            [Statistic] = A.name ,C.rowmodctr, 'UPDATE STATISTICS ' + OBJECT_NAME(object_id) + ' ' + A.name+ ' WITH FULLSCAN'
    FROM sys.stats A
    	join sys.sysobjects B with(nolock) on A.object_id = B.id
    	join sys.sysindexes C with(nolock) on C.id = B.id and A.name = C.Name
    WHERE STATS_DATE(object_id, stats_id) < getdate()-30	
    	and substring(OBJECT_NAME(object_id),1,3) not in ('sys','dtp')
    	and substring( OBJECT_NAME(object_id) , 1,1) <> '_' -- elimina tabelas temporarias
    order by C.rowmodctr desc

    Ressalto que estes exemplos não são de minha autoria, são resultado de exemplos, códigos e scripts compartilhados por amigos, colegas DBAs, MVPs do mundo todo em nossos grupos de discussões, exemplos obtidos via consultoria, bem como, construídos através da documentação oficial Microsoft.

    Caso você queria saber mais sobre a função STATS_Date acesse: https://docs.microsoft.com/pt-br/sql/t-sql/functions/stats-date-transact-sql?view=sql-server-2017

    Observação ou pensamento: Pensando diretamente que ao alterar a data que deve ser filtrada o processamento é rápido e o índice é atualizado, também nos deparamos com outro comportamento que pode possivelmente indicar um índice fragmentada ou até mesmo que necessita ser reconstruído, mas este é outro ponto que talvez seja necessário analisar posteriormente.

    Acredito que este passo ser o ponto de partida, o próximo passo será justamente validar a necessidade de atualizar as estatísticas sejam elas das tabelas internas, tabelas do seu banco de dados ou índices, e ai caso necessário fazer uso dos comandos:

    UPDATE STATISTICS (Transact-SQL)
    sp_autostats (Transact-SQL)
    sp_updatestats (Transact-SQL)

    Dúvidas:

    1 - Um detalhe que me chamou a atenção, você destacou que o plano de execução apresenta quando trabalha com uma determinada faixa de datas o operador FullScan, não seria por acaso Table Scan?

    2 - Por que você esta informando a data na cláusula from neste formato: '10-MAY-2019' AND '10-MAY-2019'? 
    Já tentou passar no padrão '2019-05-10'.

    Cuidados importantes:

    5. A análise dos traces pelo Tuning Advisor simplesmente não geram recomendações:

    Nem sempre o Tuning Advisor vai sugerir, para que isso seja feito de forma real, é necessário que o monitoramento do ambiente ocorra por um bom período.

    6. Os objetos não estão com porcentual de fragmentação, a manutenção é executada semanalmente:
    Muito cuidado em aplicar manutenções semanais, isso pode indiretamente forçar fragmentação das páginas de dados e por consequência movimentações de dados entre estas páginas, em alguns cenários forçando uma reescrita de toda estrutura do índice. Temos que levar sempre em consideração configurações como FillFactor e PadIndex aplicadas em nossas páginas de dados.

    Por fim, fico no aguardo dos resultados referentes aos blocos de código apresentados como o primeiro passo da nossa análise.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    • Editado Junior Galvão - MVPMVP segunda-feira, 29 de julho de 2019 21:42 Atualização do trecho que se refere a autoria dos códigos de exemplo.
    • Marcado como Resposta gkohm terça-feira, 30 de julho de 2019 18:50
    • Não Marcado como Resposta gkohm terça-feira, 30 de julho de 2019 18:50
    • Sugerido como Resposta Junior Galvão - MVPMVP terça-feira, 8 de outubro de 2019 13:30
    segunda-feira, 29 de julho de 2019 21:41
  • Boa noite,

    gkohm, pelo que vi a query não retorna nenhuma coluna da tabela FMLOGDET, então experimente fazer uns testes com a versão abaixo da query:

    SELECT 
        MLOG.IDLOG,            
        MLOG.DTLOG,                 
        MLOG.HRLOG,                 
        MLOG.CDMODULOSISTEMA
    FROM GKOSCF.FMLOG MLOG
    WHERE 
        MLOG.DTLOG BETWEEN '10-MAY-2019' AND '10-MAY-2019' AND 
        EXISTS
            (SELECT 1 
             FROM GKOSCF.FMLOGDET MLOGDETALHE
             WHERE
                 MLOGDETALHE.IDLOG = MLOG.IDLOG AND
                 MLOGDETALHE.TXLOGDETALHE LIKE '%TR_FMCONHEC_UPD2%')
    ORDER BY 
        MLOG.IDLOG

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 29 de julho de 2019 23:18
  • Boa noite,

    gkohm, pelo que vi a query não retorna nenhuma coluna da tabela FMLOGDET, então experimente fazer uns testes com a versão abaixo da query:

    SELECT 
        MLOG.IDLOG,            
        MLOG.DTLOG,                 
        MLOG.HRLOG,                 
        MLOG.CDMODULOSISTEMA
    FROM GKOSCF.FMLOG MLOG
    WHERE 
        MLOG.DTLOG BETWEEN '10-MAY-2019' AND '10-MAY-2019' AND 
        EXISTS
            (SELECT 1 
             FROM GKOSCF.FMLOGDET MLOGDETALHE
             WHERE
                 MLOGDETALHE.IDLOG = MLOG.IDLOG AND
                 MLOGDETALHE.TXLOGDETALHE LIKE '%TR_FMCONHEC_UPD2%')
    ORDER BY 
        MLOG.IDLOG

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    Gapimex,

    Então, por isso, eu direcionei a minha abordagem para a análise relacionada a estatística, também não consegui compreender a argumentação do ghokm em relação a tabela FMLOGDET se a mesma não é envolvida no Select, e quando se troca a data o select é processado "rapidamente".



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 29 de julho de 2019 23:37
  • Deleted
    terça-feira, 30 de julho de 2019 00:22
  • Olá Junior Galvão, 

    Obrigada pelo retorno e ajuda. Então, respondendo aos seus questionamentos:

    1 - Em qual versão do SQL Server você vai fazer uso de forma oficial desta query?

    = Versão 2014, SP3 edição Standard

    2 - Qual é a versão atual do nível de compatibilidade deste banco de dados?

    = (100) SQL Server 2008

    3 - Este ambiente foi migrado de alguma outra versão?

    = Não, não migramos o banco já nasceu nesta versão.

    4 - Estamos se referindo a máquina física ou virtual? 

    =Máquina Física

    * Sobre as estatísticas, executo o UPDATE STATISTICS aos sábados.

    == Executei o comando que você passou dos 30 dias e não houve retorno de estatística ou índices.

    1 - Um detalhe que me chamou a atenção, você destacou que o plano de execução apresenta quando trabalha com uma determinada faixa de datas o operador FullScan, não seria por acaso Table Scan?

    = Exatamente, Table Scan

    2 - Por que você esta informando a data na cláusula from neste formato: '10-MAY-2019' AND '10-MAY-2019'? 
    Já tentou passar no padrão '2019-05-10'.

    = Sim, mas não houve melhora.

    6. Os objetos não estão com porcentual de fragmentação, a manutenção é executada semanalmente:
    Muito cuidado em aplicar manutenções semanais, isso pode indiretamente forçar fragmentação das páginas de dados e por consequência movimentações de dados entre estas páginas, em alguns cenários forçando uma reescrita de toda estrutura do índice. Temos que levar sempre em consideração configurações como FillFactor e PadIndex aplicadas em nossas páginas de dados.

    = Estarei me atentando com nesta questão, obrigada!

    ** Sobre o porque da segunda tabela FMLOGDET nesta query:

    Preciso filtrar por ela para encontrar registros pelo nome definido no LIKE. A coluna 'TXLOGDETALHE' desta tabela é do tipo TEXT, sei que isso também influência.. Mas queria entender o porque o otimizador ignora o primeiro filtro. 

    terça-feira, 30 de julho de 2019 18:29
  • Obrigada pela ajuda gapimex.

    Já tinha reformulado a query neste formato e não obtive melhoras :(

    Sobre o motivo de associar a segunda tabela FMLOGDET nesta query:

    Preciso filtrar por ela para encontrar registros pelo nome definido no LIKE. A coluna 'TXLOGDETALHE' desta tabela é do tipo TEXT, sei que isso também influência.. Mas queria entender o porque o otimizador ignora o primeiro filtro. 

    terça-feira, 30 de julho de 2019 18:49
  • Olá Junior Galvão, 

    Obrigada pelo retorno e ajuda. Então, respondendo aos seus questionamentos:

    1 - Em qual versão do SQL Server você vai fazer uso de forma oficial desta query?

    = Versão 2014, SP3 edição Standard

    2 - Qual é a versão atual do nível de compatibilidade deste banco de dados?

    = (100) SQL Server 2008

    3 - Este ambiente foi migrado de alguma outra versão?

    = Não, não migramos o banco já nasceu nesta versão.

    4 - Estamos se referindo a máquina física ou virtual? 

    =Máquina Física

    * Sobre as estatísticas, executo o UPDATE STATISTICS aos sábados.

    == Executei o comando que você passou dos 30 dias e não houve retorno de estatística ou índices.

    1 - Um detalhe que me chamou a atenção, você destacou que o plano de execução apresenta quando trabalha com uma determinada faixa de datas o operador FullScan, não seria por acaso Table Scan?

    = Exatamente, Table Scan

    2 - Por que você esta informando a data na cláusula from neste formato: '10-MAY-2019' AND '10-MAY-2019'? 
    Já tentou passar no padrão '2019-05-10'.

    = Sim, mas não houve melhora.

    6. Os objetos não estão com porcentual de fragmentação, a manutenção é executada semanalmente:
    Muito cuidado em aplicar manutenções semanais, isso pode indiretamente forçar fragmentação das páginas de dados e por consequência movimentações de dados entre estas páginas, em alguns cenários forçando uma reescrita de toda estrutura do índice. Temos que levar sempre em consideração configurações como FillFactor e PadIndex aplicadas em nossas páginas de dados.

    = Estarei me atentando com nesta questão, obrigada!

    ** Sobre o porque da segunda tabela FMLOGDET nesta query:

    Preciso filtrar por ela para encontrar registros pelo nome definido no LIKE. A coluna 'TXLOGDETALHE' desta tabela é do tipo TEXT, sei que isso também influência.. Mas queria entender o porque o otimizador ignora o primeiro filtro. 

    gkohm,

    Obrigado pelas respostas, vou abaixo destacar algumas considerações, com base nas suas respostas:

    Qual é a versão atual do nível de compatibilidade deste banco de dados?

    = (100) SQL Server 2008. Recomendo que você verifique a possibilidade de alterar o nível de compatibilidade para versão 120 o qual representa a versão SQL Server 2014, isso vai ajudar o SQL Server a aplicar os novos operadores de cardinalidade na busca de dados.

    Um detalhe que me chamou a atenção, você destacou que o plano de execução apresenta quando trabalha com uma determinada faixa de datas o operador FullScan, não seria por acaso Table Scan?

    = Exatamente, Table Scan. Então neste caso quando se deparamos com este operador o mesmo indica a possível ausência de uma chave primária em sua respectiva tabela, a qual é considerada pelo SQL Server no momento do processamento dos dados como uma Heap Table.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 30 de julho de 2019 18:58
  • Deleted
    terça-feira, 30 de julho de 2019 19:28
  • Deleted
    segunda-feira, 5 de agosto de 2019 09:25