Desafio: Analisando Servidor com Perfmon

Hoje é dia de desafio! Estou colocando os contadores (na forma gráfica) para que você possa analisar e tirar suas conclusões.

Artigo: Perfmon- Falso Sentido de Monitoração

Artigo: Os 7 Grandes Mitos do Perfmon:

• Parte 1: Buffer Cache Hit Ratio, Average Disk Queue Length e %Disk Busy
• Parte 2: Paging File:%Usage, Memory: Page Faults/sec e Memory: Pages/sec
• Parte 3: SQL Access Methods: Page Splits/sec, SQL Locks: Lock Waits/sec e SQL General Statistics: User Connections

Artigo: Contadores do Perfmon

 

Contadores coletados

Fig.1: Consumo de CPU: %Processor Time e %Privileged Time (Kernel Time)

Fig.2: Fila de CPU: Processor Queue Length

Fig.3: System: Context Switches

Fig.4: Memória: %Committed Bytes

Fig.5: Memória: Available MB (memória livre no sistema)

Fig.6: Logical Disk: Reads/sec e Writes/sec (IOPS)

Fig. 7: Logical Disk: Disk Read Bytes/sec e Disk Write Bytes/sec (taxa de transferência – MB/s)

Fig. 8: Logical Disk: Avg Disk Sec/Transfer (latência do disco)

Fig. 9: Logical Disk: Writes/sec e Disk Write Bytes/sec (IOPS e taxa de escrita no disco de LOG)

Fig. 10: Logical Disk: Avg Disk Sec/Write (latência no disco de LOG) – escala de 0 a 20ms

Fig. 11: Network Interfaces: Data Sent/sec e Data Received/sec (taxa de transferência de rede)

Fig. 12: Buffer Manager: Free Pages, Stolen Pages, Database Pages, Total Pages, Target Pages (Buffer Distribution)

Fig. 13: Buffer Manager: Page Life Expectancy e Lazy Writes/sec

Fig. 14: Buffer Manager: Free List Stalls/sec

Fig. 15: Buffer Manager: Page Reads/sec e Readahead pages/sec

Fig. 16: General Statistics: User Connections

Fig. 17: General Statistics: Logins/sec, Resets/sec e SQL Statistics: Batches/sec

Fig. 18: SQL Statistics: Batches/sec, SQL Compilations/sec, SQL Re-Compilations/sec

Fig. 19: SQL Statistics: SQL Compilations/sec, SQL Re-Compilations/sec, Safe Auto-Params/sec, Forced Parametrizations/sec

Qual a sua recomendação sobre o servidor?

Olhando os gráficos, enxergo pelo menos uma recomendação sobre o servidor. E você, qual a sua opinião?

• Falta CPU na máquina?
• Falta memória RAM?
• Qual o ganho em usar discos SSD?
• Podemos habilitar o Buffer Pool Extension?
• As stored procedures podem ser otimizadas?
• Valeria a pena usar o Hekaton?
• Que tal arriscar o ColumnStore?

No próximo post vou revelar um pouco mais sobre como analisar esses contadores. Vamos falar sobre a distribuição de memória do SQL.

Comments

• Anonymous
  February 17, 2016
  Nenhum palpite? Foi tão difícil assim? :)

• Anonymous
  February 17, 2016
  Na semana que vem (terça-feira) vou mostrar como analisar os contadores de memória. E depois tem os artigos sobre discos e contadores recomendados para Windows e SQL.

• Anonymous
  February 18, 2016
  Difíceis sempre são, por isso gosto de acompanhar :) Vou arriscar apesar de já saber que deve estar cheio de pegadinhas: Falta CPU na máquina? R: Me parece que neste gráfico o consumo de CPU% não está elevado, com média de 50-60% e picos de 80%. Porém, o enfileiramento e a troca de contexto estão altos (dependendo da quantidade de cores e NUMA nodes), o que pode sugerir que temos muitos cores ou que as threads estão yielding rapidamente, necessitando analisar as esperas, quantidade de threads, grau/custo de paralelismo e distribuição das threads entre os schedulers. Aparentemente 10% do workload está sendo compilado e é um número bom perto do que estou acostumado a ver. São poucas as recompilações mas parece que são periódicas. Falta memória RAM? R: Interessante também que o PLE começa em cerca de 16m e por volta das 10:00 chega em níveis bem baixos, enquanto há bastante leituras de páginas para o buffer pool, mas até as 17:00 a tendência é de subida. Pela quantidade de LW percebe-se que houve pressão de memória as 17:00, derrubando consideravelmente o PLE. Pode ser uma edição standard 2008 limitada aos 64GB de RAM e que com atualização para 2014 poderia chegar a 128GB. Qual o ganho em usar discos SSD? A quantidade de IOPS é baixa e a latência de escrita de 20ms não parece impactar o volume de LOG já que são poucas operações de IO. Porém, percebe-se grande leitura de páginas com e sem read ahead bem durante a queda do PLE. Acredito que o SSD possibilitaria minimizar a rotatividade de páginas no buffer utilizando o BPE, se for SQL 2014 ou 2016 Enterprise. Verificar a fragmentação ou ocorrência de page splits também seria interessante pois percebe-se grande leitura de páginas sem read ahead. Podemos habilitar o Buffer Pool Extension? Podemos. Não sei o preço da memória RAM para este caso, teria que avaliar melhor qual o espaço necessário para evitar Lazy Writes e assim verificar se é melhor aumentar a memória RAM ou se o BPE atenderia. No lançamento desta feature assisti uma demo em que mesmo utilizando um disco lento para esta finalidade foi vantajoso utilizar o BPE, pois não era possível fazer upgrade de RAM. As stored procedures podem ser otimizadas? Acredito que SPs sempre podem melhorar mas não me parece que estejam compilando ou recompilando excessivamente. Valeria a pena usar o Hekaton? Estamos com pressão de memória. Não acredito que Hekaton poderia ser a solução do problema neste caso. Considerar que esta feature só existe na edição Enterprise e se tiver muitos cores pode custar muito caro. Que tal arriscar o ColumnStore? Se o workload que ocorre durante a noite e não aparece nos gráficos mas derruba o PLE for devido a leituras de data warehousing e não por conta de estatísticas, reindex e backup pode ser uma opçao mas teria que analisar se é este o caso. Considerar que só existe na edição Enterprise  se tiver muitos cores pode custar muito caro. Obrigado pelo conhecimento gratuito, abs.

• Anonymous
  February 21, 2016
  Obrigado Luiz! Com certeza não é fácil analisar esse tipo de gráfico... Esse é um servidor de produção com bastante acesso e que precisava de otimização. A análise que você fez foi muito bacana. Gostaria de adicionar alguns outros pontos de vista:

1. Qual o problema em relação ao disco? As figuras #7 e #9 mostram um acesso praticamente nulo, então talvez usar SSD não seja uma solução. Mas existe algo curioso nos gráficos #8 e #10 (atenção a esse último).
2. PLE (#13) parece não fornecer nenhuma pista concreta sobre o consumo de memória. Que tal analisar em conjunto com os gráficos #12 (#13), #14 e #15? A análise deve mostrar que não há nada grave, exceto um aumento de carga pela manhã. Agradeço por contribuir com as ideias sobre CPU e context switch. Abraços, Fabricio

• Anonymous
  February 23, 2016
  Olá Fabricio, Não consegui responder a todas as perguntas, mas vou deixar o que eu tinha anotado e olhando os comentarios acho que fui para uma linha totalmente inversa. Antes de mais nada, qual a configuração deste server? Acho que a questão de faltar CPU precisaria ser estudado com maiores detalhes, primeiramente, o contador % Processor Time, não está muito alto para o total do processador, mas precisariamos estudar cada nucleo, se está ocorrendo alguma sobrecarga em um nucleo apenas, e dependendo da quantidade de nucleos o processor queue length está um pouco alto (abaixo de 8 processadores). A mesma situação para o contador "context switch/sec", imaginando que podemos chegar até 5.000 por processador, que é um numero alto (abaixo de 8 processadores). O contador "compilations/sec" até que não está alto comparado com a quantidade de batches/sec executadas. Achei estranho que no espaço de tempo de 1h aproximadamente, subia as recompilações, algum job executando carregando informações? Neste caso, possivelmente ocasionaria em um lock. Sendo assim, vale a pena estudar as procedures se precisam de uma otimização, afinal, nos outros periodos, o numero está mais baixo.

• Anonymous
  February 24, 2016
  Olá Nicolas! Eu não lembro a configuração desse server, mas acredito que seja um server com 16 cores e 64GB de RAM. Você tem razão sobre analisar cada núcleo individualmente. Na prática, eu acabo pulando essa análise porque os servidores tem tantos cores que fica trabalhoso olhar os gráficos. Entretanto, existem diferenças entre eles. Basta olhar a CPU0, que recebe as interrupções e, por isso, apresenta maior consumo de Kernel Time. Falando em context switches, eu já li muito sobre o assunto e o impacto que isso pode causar sobre o TLB do processador (TLB = translation lookaside buffer) e o cache. Ao entrar nesse assunto, a primeira coisa que aparece é o hyper-threading, que aumenta o paralelismo e o context switches usando threads lógicas. Depois há as interferências dos spin-locks, que evitam a troca de contexto, mas causam atrasos na execução. Sem contar que as threads envolvidas podem ser user mode ou kernel. Conclusão: até hoje não sei olhar esse contador e dizer um número limite. Por isso, sempre uso a comparação com o baseline de performance. Comparar com baseline de performance é a melhor estratégia para a maioria dos contadores. Vejamos o "compilations/sec", por exemplo. Isso depende da complexidade da procedure ou adhoc. Eu não consigo imaginar como analisar isso - alguma sugestão? Obrigado por compartilhar seus comentarios. Se tiver mais, nao deixe de postar! Fabricio

• Anonymous
  February 24, 2016
  RESPOSTA 1: Pessoal, a primeira coisa que chama atenção nesse servidor é a CPU. Ela está em 60%, não é alto nem baixo. Não tem nenhum motivo para ficarmos preocupados que ela causa lentidão nesse servidor. Entretanto, não sabemos se esse servidor conseguiria aguentar um aumento repentino de carga. A CPU está OK. Oportunidade: Se você for um consultor de performance, vale a pena investigar as queries desse sistema. Muitas vezes são poucas consultas que se otimizadas, diminuem o consumo de recurso pela metade. A vantagem é que diminuir 60% para 30% de CPU tem um resultado VISÍVEL. Essa oportunidade não existiria se o servidor estivesse com 2% de CPU e diminuíssemos para 1%. Embora seja a metade, ele não é VISIVELMENTE eficiente.

• Anonymous
  February 24, 2016
  RESPOSTA 2: A memória é sempre criticada por conta do gráfico #13 do Page Life Expectancy. Entretanto, o gráfico anterior #12 mostra uma distribuição bastante saudável - não há oscilação no Target e a quantidade de Stolen é baixa. A memória está OK. A causa da variação do PLE é por conta de stored procedures mal escritas. Como eu sei? O gráfico #15 mostra vários read-ahead, que são normalmente decorrentes de table scan. Vale a pena otimizar essas procedures? Não sei. Eu começaria atacando a CPU, porque ela o resultado visual é mais bonito (os gráficos falam mais alto). Lembrando que tanto CPU e memória estão OK.

• Anonymous
  February 24, 2016
  RESPOSTA 3: O problema é o disco. Ele tem um problema grave. Vou colocar um artigo específico sobre o assunto, mas note que o tempo de resposta tem grande variação, embora não exista carga!

• Anonymous
  February 24, 2016
  Link para o artigo sobre análise de memória: blogs.msdn.com/.../perfmon-monitorando-o-buffer-manager.aspx