Jump to content
Fórum Script Brasil
  • 0

Problemas com performance em consultas SQL Server


Question

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;

 

771.PNG.a4dd48574c001f016b9652471664cefd.PNG7712.thumb.PNG.900701490fbc18931df2ed788682cf73.PNG

Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Bom dia,

Pelo que percebi o SQL está usando um "index scan" para fazer está pesquisa o que significa que todas as linhas são lidas durante o plano de execução. Pelo que vi o predicado da sua consulta é data e um texto que você utiliza o operador like que mata a performance da maioria das consultas.

O ideal é que o Índice seja seek o que significa que o plano está usando uma coluna unica ou primaria para navegar nas página e trazer o resultado.

 Para resolver consigo pensar em duas formas:

Primeira: Habilitar o full-text do sql server e utilizar CONTAINS na pesquisa, vai melhor drasticamente a velocidade.

Segunda opção: Criar uma coluna com o seu filtro da consulta e depois um index para essa coluna e utilizar a coluna nova no filtro ao invés do operador like, isso vai melhor drasticamente sua performance também.

Segue exemplo abaixo:

ALTER TABLE dbo.Users ADD SearchString 
    AS CONVERT(BIT, CASE WHEN DisplayName LIKE '%Christopher%' THEN 1 ELSE 0 END)

CREATE INDEX ix_yourmom ON dbo.Users (SearchString)

Observações: Coisas para verificar antes de aplicar qualquer solução:

Verificar se existe um index para seu predicado (todos os campos que aparecem na clausula WHERE da consulta), se existir pode verificar se o plano de execução está usando  ou não.

Pode tentar forças a utilização do seu index e ver se o desempenho melhore.

Segue exemplo de como forçar um index no select 

SELECT ContactID
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))

Atte.,

Marcos Roberto

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Forum Statistics

    • Total Topics
      148681
    • Total Posts
      644505
×
×
  • Create New...