Problemas com performance em consultas SQL Server

[hgkohm]

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;

 

[Marcos Roberto Vieira Juni]

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