Deadlocks


Passei por uma situação numa consultoria que dei a um cliente um tanto estranha.Resolvi, mas gostaria de compartilhar o case com vocês . A situação foi a seguinte :

Um cliente tinha uma tabela de 8 milhões de linhas. Era usado surrogate keys, mas não tinha indice cluster e 15 indices não cluster e muito inserção diária nela.O fillfactor era de 100%. Tinha numa média 100 deadlocks por dia e o mais estranho, as procedures eram de insert e select. Não havia update nesta tabela..ou seja, deadlock em inserts e em varios selects.Esta tabela tb tinha alguns indices duplicados e FK´s duplicadas.

Ativei o flag de deadlock e as informações que me apareciam era justamente no insert e nos select (as vezes select com select) e com muito deadlock de pagina. Minha primeira ação foi desabilitar o escalonamento de lock para paginas, mas não teve muito sucesso.

Peguei esta tabela e criei um indice cluster, limpando tb os indices duplicados e FK´s duplicadas.. Esta ação reduziu para uma média de 40 deadlocks por dia. Mesmo assim inaceitável.Verifiquei que haviam muitos CXPACKETS nos selects e como sou cetico em paralelismo em OLTP tomei uma ação drastica e coloquei o max degree of paralelism para 1 (era um xeon quadriprocessado). esta ação reduziu para 4 deadlocks diarios. Quando implemnetei o fillfactor papar 80% nos indices nao cluster, os deadlocks começaram a ficar na faixa de 2 a 3 SEMANAIS..quando tinha.O meu "achamento" hehehe, foi o seguinte :

Criar o indice cluster : Como não existia um indice cluster a cada atualização da tabela os 15 indices não cluster tinham que ser reorganizados com base no RID (arquivo.pagina..slot..etc). Talvez como eram muitos indices não cluster, poderia haver algum tipo de contenção ou demora nestra atualização, podendo gerar os deadlocks.

Max degree of paralelism pra 1: LI certa vez sobre deadlocks de paralelismo (processador), mas nao havia explicação e sim dizendo que eles ocorrem. Acredito que foi o meu caso deadlocK de paralelismo, pois o maior percentual que tive de ganho foi em cima desta ação. (estou fuçando nos insides do UMS scheduler pra entender mais do assunto…quando tiver novidades eu posto)

Fillfactor de 80% : O indice cluster ficou em cima do identity, não deixei fillfactor para este indice, pois como são colocados sempre no final não vi o porque do fillfactor aqui. Mas nos outros indices que não eram identitys (óbvio), eu deixei com 80% provavelmente minimizando os page splits destes indices. Uma vez li um artigo da Kalen Danley dizendo sobre deadlocks por causa de MUITOS page splits. To procurando uma explicação estilo "inside" pra gerar deadlocks em page splits.

Após isso, continuei a consultoria por mais algum tempo, revemos as queries que estavam paralelizando sem necessidade (99% mal construida e faltando indices ou com indices "burros"). Demos uma geral nas tabelas e na modelagem, havia muita contenção na TEMPDB tb. Voltei o max dgree of paralelism para 0, não tendo mais paralelismo desnecessárioe os que ocorrem instrui os desenvolvedores a primeiramente verificar o plano de execução retirando o paralelismo na propria querie (maxdop) confrontando com a querie paralelizada para saber qual esta mais otimizada. Não pelo problema de deadlocks, mas por ser cético quanto ao paralelismo em ambientes transacionais. Queries bem construidas, modelagem com bom senso dificilmente geram paralelismo a não ser que trabalhemos com muita informação…..só que neste caso já muda o conceito né…muita informação em OLTP já esta mais para OLAP…neste sim sou fã do paralelismo.

Estava até ontem conversando com um amigo meu o Rodrigo e ele comentou que usando o paralelismo no SSIS (obvio que é outro conceito) e deixando ele gerenciar , esta conseguindo subir 250 milhões de linhas em 12 minutos…Realmente o SSIS é poderosíssimo…..

Grande abraço Galera

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s