«

»

set 02

Análise de Lentidão

 

 

Vamos relatar um caso real de análise de lentidão, chegando a dar timeout em alguns casos e registrando deadlocks:

 

 

 

Cenário

 

1.   A carga é realizada em lote através de um TXT, com centenas ou milhares de transações tratadas pela aplicação, enviadas à Secretaria da Fazenda e registradas na base NFE;

 

2.   Operações similares são realizadas na base NFE_LOJA que fica na mesma instância, porém, de forma distribuída ao longo do dia, individualmente e não em lote. Neste caso sem apresentar erros, ou seja, a lentidão é em uma base específica chamada NFE;

 

3.   A aplicação é capaz de registrar em log o momento que ocorre os deadlocks;

 

4.   Os deadlocks ocorrem sequencialmente dentro do mesmo minuto, em horários variados, e não existem Jobs agendados nestes momentos;

 

5.   O problema passou a ocorrer há cerca de dez dias, quando também ocorreu um incidente de falta de espaço em disco no servidor de aplicação

 

6.   Apesar da lentidão, não havia indícios sobre consumo excessivo de CPU e memória;

 

Simulação

 

Para que fosse possível analisar, foi realizada uma carga com cerca de 700 transações comerciais por volta das 11:00. Neste momento, foi possível identificar que a quantidade de IOPS solicitada era superior a 3.000 e que havia um enfileiramento médio de disco entre 500 e 600.

 

Uma nova base chamada NFE2 com a mesma estrutura da base NFE foi criada, no mesmo volume da base NFE que neste momento possuía 80GB, para realizarmos os testes. Diversas cargas superiores à realizada as 11:00 foram feitas na base NFE2 mas neste caso, a média de consumo de IOPS se manteve inferior a 100 todo o tempo. Enquanto vários dados do perfmon e DMVs estavam sendo coletados, foi feita uma análise nas consultas, verificando quais consumiam mais recursos como CPU e disco. A consulta TOP consumia tanto recurso que era absurdamente maior que a segunda consulta da lista, ordenada pela quantidade de operações de escrita e também em uma segunda lista ordenada por tempo de CPU. A comparação de custo entre a base vazia e a base com 80GB de dados era gritante, 335 contra 2,7.

 

Análise

 

A query fazia um select de 17 colunas, usando a coluna STATUS como predicado na cláusula WHERE. Porém, o índice criado para esta consulta era do tipo não-cluster, ou seja, já que não foram adicionadas as outras 16 colunas explicitamente através da cláusula INCLUDE, só eram encontrados os dados referente a coluna STATUS e um ID do índice cluster, onde uma nova consulta teria que ser realizada para obter os dados das outras 16 colunas e responder à aplicação.

 

A primeira leitura do índice é chamada de Index Seek, pois os dados estão mapeados de acordo com os dados da coluna STATUS. Em seguida, o redirecionamento ao índice cluster pra pegar os dados é chamado de Bookmark Lookup até que linha a linha são montadas, operação chamada de Nested Loops.

 

Mas será que não seria mais fácil, já que o índice cluster possui todos os dados, por que não ler direto o índice cluster todo (este sim possui todas as colunas da tabela), retornando as 17 colunas onde o STATUS seja igual a 1? É exatamente isso que o Query Optimizer do SQL Server faz: quando recebe a query o Query Optimizer cruza as informações com as estatísticas e se for mais barato verificar direto o índice cluster ele então o faz! Esta operação é chamada de Index Scan.

 

Além de mais custosa, esta query era uma das mais executadas. Seu resultado era consumido por outra query que aguardava estas informações para escrever na própria tabela consultada e em outras tabelas da mesma base. Aí foi possível entender a origem dos deadocks, onde queries lentas estavam causando enfileiramento, aguardando liberações de locks do mesmo objeto e dependendo disso para prosseguir.

 

Analisando os dados do Perfmon e DMVs coletados, foi possível identificar um elevado número de solicitações de locks, onde a principal espera das threads era LCK_M_S, ou seja, Shared Lock. No nível de isolamento padrão do SQL Server (Read Commited) as consultas precisam ler dados consistentes e para garantir que não estão sendo alterados durante a leitura os bloqueia antes de ler.

Isso permite que somente quem também queira ler aquele dado possa usá-lo mas quem pretende inserir, atualizar ou deletar dados deve aguardar até que a operação de leitura seja concluída e assim o lock liberado. Portanto, tendo queries querendo ler enquanto outras querem escrever nas mesmas colunas da mesma tabela de forma intensa e sem o índice adequado explica a lentidão de acordo com a quantidade de dados. Tanto as operações de leitura quanto as de escrita demoram mais utilizando o pior plano de execução, fazendo assim com que as demais queries fiquem aguardando ou passem a depender do mesmo dado, resultando em deadlock.

 

Ainda de acordo com o Perfmon e as DMVs, cruzando com as configurações da base e da instância, foi possível perceber que os arquivos de TLog estavam fazendo operações de crescimento muito pequenas (10MB), porque todas as noites era feito o backup do TLog e uma operação de Shrink, reduzindo-o para 10MB novamente (seu tamanho inicial).

 

A TempDB era responsável por cerca de 40% das transações por segundo e estava utilizando apenas um arquivo no mesmo volume da base NFE (que já sofria de consumo de I/O). Seu arquivo de TLog e dados também estavam configurados para crescer a cada 10MB. Como não estava configurado o direito de Perform Volume Maintenance Tasks, as operações de crescimento do arquivo de dados estavam muito mais custosas.

Outros itens foram verificados mas não davam indícios de problemas, portanto vamos deixar de lado para não deixar o texto ainda mais longo.

 

Recomendações 

 

1.   Recriar o índice não-cluster adequadamente, incluindo as outras 16 colunas solicitadas, em outro volume dedicado a este data file, com operações de rebuild agendadas, fazendo testes com o fill factor em 80% 70% e 60% neste índice, já que teremos operações de carga frequentes e desordenadas;

2.   Particionar a tabela, mantendo os dados antigos em arquivos e volumes diferentes, possibilitando que alguns fiquem em modo somente leitura. Desta forma a estratégia de recuperação de desastres receberia uma melhoria significativa, além do processo de rebuild dos índices;

3.   Configurar os arquivos de TLog para um tamanho inicial e crescimento adequados, visando diminuir a quantidade de VLFs e de operações de crescimento do arquivo;

4.   Passar a fazer backups do TLog com intervalos regulares menores, também no intuito de diminuir a quantidade de operações de crescimento do arquivo mas com o benefício de combater sua fragmentação;

5.   Adicionar 7 arquivos à TempDB, já que sustenta 40% das transações, com a finalidade de evitar contenção de GAM, SGAM e PFS. Configurar a trace flag 1117, para garantir a eficiência da utilização dos arquivos de dados.

6.   Transferir os arquivos de dados da TempDB para um volume dedicado, pois os números de IOPS e fila de disco no volume atual são muito expressivos.

7.   Adicionar o direito de Perform Volume Maintenance Tasks para o usuário do serviço do SQL Server, possibilitando que as operações de crescimento dos arquivos de dados sejam beneficiadas pelo recurso Instant File Initialization;

8.   Realizar testes utilizando o Read Commited Snapshot Isolation. Este recurso faz uma cópia dos dados soicitados por uma query na TempDB, reduzindo drasticamente a quantidade de locks, mas aumentando a quantidade de transações na TempDB e consequentemente a quantidade de IOPS.

 

Ainda não obtive um retorno de quais ações de fato foram tomadas e como ficou este caso mas assim que souber postarei o resultado.

 

Este foi um exemplo de como uma análise pode ser longa, podendo tomar caminhos ou linhas de raciocínios diferentes conforme os sintomas apresentados inicialmente, já que muitos fatores devem ser analisados tomando bastante tempo.

Se ficou alguma dúvida não deixe de comentar!

 

4 comentários

Pular para o formulário de comentário

  1. Angelo Máximo Moreira Silva

    Pelo visto, no próximo SQL Saturday vou assistir uma palestra sua.
    Gostei da recomendação de número 7, configurar a policie Perform Volume Maintenance Tasks, raramente vejo fazerem essa configuração.
    Parabéns !

  2. Nilton Pinheiro

    muito legal!!!! Seria uma boa avaliar também a utilização do nível de isolamento snapshot. Embora deva aumentar um pouco o overhead no tempdb mas considerando que sugestões de melhoria para ele já foram passadas…..o snapshot deve ajudar.

    há…não seria o TF 1118?

    abs

  3. Luiz Mercante

    Valeu Nilton!

    No item 8 estou recomendando testes com RCSI e a TF 1118 é pra usar extents uniformes, que elimina a concorrência por GAM e SGAM. Como já estamos adicionando outros arquivos para eliminar a concorrência também na PFS, a TF 1118 já não seria necessária.

    Sugeri a TF 1117 para que os arquivos da TempDB (entre outros data files) cresçam juntos, garantindo assim que todos os arquivos sejam utilizados.

    Obrigado, ter um especialista experiente visitando e comentando no SQL Dicas! é uma honra.

  4. Luiz Mercante

    Hehehe tomara Angelo, estou empenhado nos estudos, espero poder contribuir com a comunidade palestrando.

    Abs e obrigado!

Deixe uma resposta