none
Análise de consulta gerando deadlocks constantes RRS feed

  • Pergunta

  • Olá pessoal, estou com uma dúvida. Recentemente criei um alerta de deadlock para a mensagem 1205 e ativei os sinalizadores de rastreamento 1204 e 1222. Diante disso passei a receber por email os alertas de deadlocks e verificar a consulta que estava causando o travamento que segue abaixo:

    set statistics io on
    set statistics time on
    
    SELECT cont_reg_cliente, 
           str_cod, 
           cont_msg, 
           cont_cod 
    FROM   table1_controleenvio 
    WHERE  ( cont_reg_cliente IS NOT NULL ) 
           AND ( ( cont_tipo = 'S' 
                   AND cont_destino LIKE '%81069490' ) 
                  OR ( cont_tipo = 'E' 
                       AND cont_destino = '5575981069490' ) ) 
           AND ( cont_sucesso = 'S' ) 
           AND ( ( cont_titulo LIKE '%agenda%' ) 
                  OR ( cont_titulo LIKE '%Agenda%' ) ) 
           AND ( cont_datahora BETWEEN '2016-02-01 00:00:00' AND 
                                       '2016-02-03 00:00:00' ) 
    ORDER  BY cont_cod DESC 
    
    set statistics io off
    set statistics time off
    
     

    Analisando o plano de execução e o conjunto de estatísticas da query percebi que a mesma estava passando usando paralelismo em duas circunstância, além das informações de estatísticas não ter me agradado muito:

    Inicialmente analisei as estatísticas do índice usado da tabela e percebi que a mesma estava desatualizada. Atualizada a mesma e gerado novamente o plano de execução e conjunto de estatísticas percebi uma certa melhora no plano com relação a eliminação de uma etapa no retorno dos dados, porém não houve redução no tempo de cpu, tempo de espera, leituras lógica e de contagem de varredura:

    Queria a opinião dos colegas dos pontos mais que preciso analisar para evitar o lock? Da questão de seletividade dos filtros no WHERE? Se o índice criado está coerente para solucionar o consumo da consulta? Etc.

    CREATE NONCLUSTERED INDEX [idx_table1_ControleEnvio_4] ON [dbo].[table1_ControleEnvio] 
    (
    	[cont_sucesso] ASC,
    	[msg_cod] ASC,
    	[cont_titulo] ASC
    )
    INCLUDE ( [cont_reg_cliente],
    [cont_datahora]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
    GO
    
    
    Obrigado desde já!
     
    segunda-feira, 15 de fevereiro de 2016 02:54

Respostas

  • Boa tarde Jeferson,

    Já experimentou utilizar o hint WITH(NOLOCK) nessa query para verificar se diminui as ocorrencias de Locks ? 

    Apenas para fins de testes, caso resolva poderia analisar se a regra de negocio permite trazer informação suja.

    Att

    Reginaldo Silva

    segunda-feira, 15 de fevereiro de 2016 15:19
  • Jerfeson,

    Acredito que o índice que você está sugerindo não seja o melhor.

    O campo msg_cod não está sendo usado no seu WHERE e sim no SELECT, então colocaria ele na claúsula INCLUDE.

    Você deve tentar indexar pelos campos que estão no seu WHERE e aí colocar os campos do SELECT na clausula INCLUDE para evitar a operação de Key lookup.

    Verifique a seletividade (quantidade de valores distintos) de cada campo para montar a ordem no índice .

    Uma coisa ruim na sua query são os LIKE. Queries em string usando LIKE são sempre pobres em performance, ainda mais se sua tabela for grande.

    Em relação a sugestão do Reginaldo, o NOLOCK pode ajudar, mas não é muito recomendado.

    Se for seguir nessa linha de nível de isolamento, uso o  SET TRANSACTION ISOLATION LEVEL SNAPSHOT. 

    Aqui vai um artigo muito bom sobre o tema: https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    segunda-feira, 15 de fevereiro de 2016 15:31
  • Jerfeson,

    Observe que o seu plano de execução esta apresentando o operador Key Lookup, sendo este ocorrido sempre que o dando no índice non-clustered não é encontrado e o mesmo preciso de uma referência no índice clustered para localizar o dado.

    A melhor forma de evitar isso é fazer uso de um comando Where no seu select!!! A sugestão de índice de cobertura neste caso é interessante desde que o próprio SQL Server faça esta referência.

    Mas caso você queira fazer uso do mesmo, verifique no operador Key Lookup qual coluna esta sendo utilizada e adicione esta coluna no índice de cobertura ou crie um índice específico para a mesma.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 18 de fevereiro de 2016 12:19

Todas as Respostas

  • Boa tarde Jeferson,

    Já experimentou utilizar o hint WITH(NOLOCK) nessa query para verificar se diminui as ocorrencias de Locks ? 

    Apenas para fins de testes, caso resolva poderia analisar se a regra de negocio permite trazer informação suja.

    Att

    Reginaldo Silva

    segunda-feira, 15 de fevereiro de 2016 15:19
  • Jerfeson,

    Acredito que o índice que você está sugerindo não seja o melhor.

    O campo msg_cod não está sendo usado no seu WHERE e sim no SELECT, então colocaria ele na claúsula INCLUDE.

    Você deve tentar indexar pelos campos que estão no seu WHERE e aí colocar os campos do SELECT na clausula INCLUDE para evitar a operação de Key lookup.

    Verifique a seletividade (quantidade de valores distintos) de cada campo para montar a ordem no índice .

    Uma coisa ruim na sua query são os LIKE. Queries em string usando LIKE são sempre pobres em performance, ainda mais se sua tabela for grande.

    Em relação a sugestão do Reginaldo, o NOLOCK pode ajudar, mas não é muito recomendado.

    Se for seguir nessa linha de nível de isolamento, uso o  SET TRANSACTION ISOLATION LEVEL SNAPSHOT. 

    Aqui vai um artigo muito bom sobre o tema: https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    segunda-feira, 15 de fevereiro de 2016 15:31
  • Caros colegas, vou rever os pontos pelas dicas que me passaram e dou retorno dos resultados.

    De fato é relativamente uma tabela grande são 2011695 linhas. 

    Mariana, o uso do campo cont_msg não é possível ser usado num INCLUDE por conta do tipo de dado ser text. Fiz as alterações no índice e avaliei e não vi muita mudança para melhor. Deixou de executar a operação de Key lookup no plan exec,, porém os resultados de statistics não mudou muito.

    Agradeço desde já!




    quarta-feira, 17 de fevereiro de 2016 15:33
  • Jerfeson,

    Observe que o seu plano de execução esta apresentando o operador Key Lookup, sendo este ocorrido sempre que o dando no índice non-clustered não é encontrado e o mesmo preciso de uma referência no índice clustered para localizar o dado.

    A melhor forma de evitar isso é fazer uso de um comando Where no seu select!!! A sugestão de índice de cobertura neste caso é interessante desde que o próprio SQL Server faça esta referência.

    Mas caso você queira fazer uso do mesmo, verifique no operador Key Lookup qual coluna esta sendo utilizada e adicione esta coluna no índice de cobertura ou crie um índice específico para a mesma.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 18 de fevereiro de 2016 12:19
  • Grande Galvão, estava analisando as colunas da consulta para definir melhor o índice vi que a coluna que está fazendo Key Lookup é a cont_msg, na qual o tipo de dados é TEXT. Esse tipo de dado não permiti ser incluído em um índice. Porém estava estudando e o tipo de dado está pra ser descontinuado pela MS, além de que a substituição pelo o VARCHAR(MAX) a performance da consulta pode ser tornar bem melhor. Seria útil a troca do tipo de dado? Teria como me esclarecer e me dizer se neste caso é aconselhável pensar nesta possibilidade já que nesta condição se tornou difícil evitar o KEY LOOKUP. E poderia me explicar o porque o SQL Server ainda insiste usar os operadores de paralelismo GATHER STREAMS e REPARTITION STREAMS.


    sexta-feira, 19 de fevereiro de 2016 00:15
  • Jerferson,

    Sim é totalmente viável você mudar do Text para o Varchar(Max) principalmente pelo fato de a Microsoft descontinuar este tipo de dado.

    Faça esta mudança e observe se o key Lookup e o Paralelismo são removidos.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 23 de fevereiro de 2016 13:15