Análise de consulta gerando deadlocks constantes

Question

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á!
 

Answer 1

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

Answer 2

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 */

Answer 3

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á!

Answer 4

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]

Answer 5

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.

Answer 6

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]