none
Análise de Performance - Consumo Expressivo no CPU RRS feed

  • Pergunta

  • Prezados colegas, estava analisando um de meus ambientes, no qual começaram a se queixar de lentidão. Determinei um conjunto de contadores para identificar se o problema estava na limitação dos recursos de Disco, Memória ou CPU. Pelo que coletei em 12 horas de monitoramento me pareceu ter um problema evidente em consultas que estão consumindo muito CPU em seus respectivos processamento. Queria ouvir o conselho da galera nesse tipo de problema para compreender se estou no caminho certo e se posso confirmar a minha afirmação. 

    Este servidor atualmente está com 16 GB, no qual desses definir pra max server memory 13 GB. Definir o max degree of parallelism com o valor 1. Analisei também os Waits e vi que a espera mais significativa estava com CXPACKET e as vezes ASYNC_NETWORK_IO. 

    Segue minha análise:

    Analisando o Bytes e MBytes disponíveis as médias correspondem um valor positivo e aceitável. Considerei a seguinte afirmação "Um valor menor do que 5% do total de memória física significa que a mesma é insuficiente, e isso pode aumentar a atividade de paginação", ou seja, dos 16 GB alocados para o servidor 5% seria 819MB.

    Analisando o resultado do contador Entradas Livres de Tabela de Paginação do Sistema a recomendação indica “Se o número for menor do que 5.000, pode haver um vazamento de memória” o coletado foi de 33.000, estando dentro do valor aceitável.

    O contador Bytes de Pool Não-Paginável mede o tamanho, em bytes, do pool não-paginável. Essa é uma área da memória do sistema para objetos que não podem ser gravados em disco, mas que devem permanecer na memória física enquanto estiverem alocados. “Se o valor for maior do que 175 MB, pode haver um vazamento de memória”. O valor do monitoramento foi de 55MB.

    No contador Bytes de Pool Paginável que mede o tamanho, em bytes, do pool paginável. Essa é uma área da memória do sistema usada para objetos que podem ser gravados em disco quando não estão em uso. “Se o valor for maior do que 250 MB, pode haver um vazamento de memória”. O valor descrito no resulto foi de 161MB.

    Para o contador de páginas/s considerando a afirmação "Se o valor for maior que 1.000, como resultado da paginação excessiva, pode haver um vazamento de memória.". Considerando o resultado do contador de 290 a média de paginação é aceitável.

    O Buffer Cache Hit Ratio o parâmetro de análise com relação a afirmação seguinte "Taxa de 90 por cento ou mais é desejável", o resultado do contador consiste na média aceitável para funcionamento ótimo da instância, sendo apresentado o valor de 99,98%.

    Para o contador Total Server Memory considerando a afirmação "Consistentemente alto, em comparação com a quantidade de memória física disponível no computador.". Considerei aceitável, pois a memória física é de 16GB e o resultado marca 13GB, justamente o configurado na instância Max Server Memory, não ultrapassando o valor da memória física. 

    No conjunto de trabalho do serviço do SQL Server considerei a afirmação "Se esse número estiver consistentemente abaixo da quantidade de memória definida pelas opções de servidor Min Server Memory e Max Server Memory, o SQL Server está configurado para usar memória demais.". Neste caso o resultado apresentou o valor acima de 1GB e 13GB o configurado respectivamente para Min Server Memory e Max Server Memory.

    De acordo com a recomendação os objetos do contador de Page File devem trabalhar com valores abaixo de 70%. Observando a tabela o utilizado (%Usage) e o consumido (%Usade Peak) estão bem abaixo, tendo respectivamente valores de aproximadamente 10%. O valor da curva de normalidade aceitável. Isso justificava o valor do contador Pages/s ser bem pequeno tendo um valor bem razoável não visualizando excesso de paginação.

    O objeto %bytes confirmados em uso está consumindo apenas 46%, sendo que o recomendado ser menor à 80%. Bytes de pool paginável e não paginável observando os gráficos vi que tem seus valores constantes durante o período que foi monitorado. O objeto entradas livres de tabela de paginação do sistema também permaneceu em seu limite aceitável.

    No buffer manager o objeto checkpoint pages/sec está com valor aceitável considerando o recomendado abaixo de 50%. O lazy writes/sec da mesma forma, no monitoramento apresentou 0%, sendo que o recomendado é o mais próximo de 0. Geralmente este valor zerado é por não apresentar uma pressão de memória. As expectativas de vida na memória das páginas está também dentro dos conformes, pois de acordo com a recomendação o valor aceitável é acima de 5 minutos e a média coletada foi de 44 minutos.

    Para os contadores de páginas lidas e escritas por segundo o recomendado é de um valor abaixo de 90. O encontrado na coleta foram respectivamente de 8 e 1. O contador Readahead/sec o valor aceitável é abaixo de 20% e o apresentado na instância 5%.

    Os contadores do objeto Estatísticas do SQL estão dentro da curva de normalidade, pois fazendo os relacionamentos com os planos de execução compilados e recompilados é possível determinamos um nível satisfatório do uso eficiente do SQL Server dos novos planos criados e dos reaproveitados pelas consultas definidas pela aplicação Smart.

    Para o contador %tempo de processador o recomendado é um valor abaixo de 80% durante períodos contínuos de 10 minutos ou mais durante um período de monitorização. No acompanhamento realizado no ambiente foi verificado um valor acima do recomendado, se fazendo necessário avaliar a carga atual atuando no servidor ou verificar o desempenho das consultas que porventura esteja gerando esse problema.

    No contador comprimento da fila do processador o valor recomendado é de não exceder 2 processadores durante períodos de 10 minutos ou mais durante o período monitorado, ou seja, o servidor de 12 CPU’s lógicos, no qual o valor não deveria ultrapassar 24. Atualmente o valor que consta é de 54, saindo do limite aceitável.


    Agradeço desde já!



    terça-feira, 18 de agosto de 2015 17:03

Respostas

  • Jerfeson,

    Há nível de curiosidade, porque você definiu o MaxDop = 1? Desta forma, você esta desabilitando a capacidade do Microsoft SQL Server trabalhar com Paralelismo.

    Você mesma esta controlando em suas transações estas funcionalidade?


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

    quarta-feira, 19 de agosto de 2015 17:57
    Moderador
  • Jerfeson,

    Sim, que bom que você entendeu e verificou!!!

    Realmente os valores de CXPacket podem ter algum tipo de relação com a falta de paralelismo, por padrão o SQL Server trabalha com o paraleslimo fazendo com que ele mesmo controle a melhor forma de uso dos CPUs e demais recursos necessário, para tal capacidade deve-se utilizar o valor 0(zero) para a propriedade Maxdop.

    Em relação aos seus questionamentos e observações, elas possuem sentido e estão coerentes com a sua percepção, alguns pontos talvez podemos analisar com alguns outros ajustes.

    Um ponto que podemos focar é o tamanho da fila que você destacou em relação ao seu processador!!! É justamente ai que o Maxdop pode mudar este cenário.


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

    sexta-feira, 21 de agosto de 2015 18:21
    Moderador
  • Jerferson,

    Realmente entender o comportamento do SQL Server é algo que para nós simples mortais esta bem longe, acredito que para este tipo de situação temos que recorrer aos Pais do SQL Server, dentre eles Paul Randal!!!

    Eu particularmente procuro seguir as recomendações da documentação Microsoft, e no caso do Maxdop ainda mais, eu sempre deixo o Maxdop = 0 quando utilizo máquinas físicas ou virtuais com processadores abaixo de 8 processadores lógicos e Maxdop=8 para ambientes com mais de 8.

    Em relação aos testes do Cost Threshold(linear) for Parelellism eu penso da seguinte forma, imagine que você acabou de instalar um ambiente limpo do SQL Server e quer realizar alguns testes de performance, então você vai utilizar o conjunto de configurações padrões e conforme os testes forem sendo realizados, você vai poder ir alterandos as configurações.

    Faça exatamente isso, realize testes com as configurações padrões e depois vai ajustando os valores que você deseja para cada configuração, durante estes testes você pode ir salvando o plano de execução para posterior comparação.


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

    sábado, 22 de agosto de 2015 20:57
    Moderador

Todas as Respostas

  • Jerfeson,

    Há nível de curiosidade, porque você definiu o MaxDop = 1? Desta forma, você esta desabilitando a capacidade do Microsoft SQL Server trabalhar com Paralelismo.

    Você mesma esta controlando em suas transações estas funcionalidade?


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

    quarta-feira, 19 de agosto de 2015 17:57
    Moderador
  • Galvão, algumas transações vindas da aplicação já vem com esse controle OPTION(MAXDOP 1). Concordo com sua afirmativa, pois dei uma aprofundada no que disse "Desta forma, você esta desabilitando a capacidade do Microsoft SQL Server trabalhar com Paralelismo" e no link abaixo do colega Alberto Lima esclarece bem o seu comentário. Obrigado.

    http://blogs.msdn.com/b/pfebrasilsql/archive/2014/08/21/max-degree-of-parallelism-cxpacket-maxdop.aspx

    Já estou revendo essa questão na configuração do MAXDOP. Então de ante mão a configuração inadequada mostra ter uma relação evidente com o a espera CXPACKET? Qual a melhor forma de configurar essa opção, pois entendo que precisamos levar em consideração a quantidade de CPU's lógicos ou manter esse valor 0 como default.

    E sobre os resultados do monitoramento e os meus questionamentos feitos sobre o ambiente como um todo? Foram coerentes?

    Agradeço desde já!

    Abraços! 


    • Sugerido como Resposta Fabio Cerqueira quinta-feira, 20 de agosto de 2015 11:23
    • Editado Jerfeson S. Barbosa quinta-feira, 20 de agosto de 2015 14:02 Alteração no texto
    quarta-feira, 19 de agosto de 2015 19:03
  • Jerfeson,

    Sim, que bom que você entendeu e verificou!!!

    Realmente os valores de CXPacket podem ter algum tipo de relação com a falta de paralelismo, por padrão o SQL Server trabalha com o paraleslimo fazendo com que ele mesmo controle a melhor forma de uso dos CPUs e demais recursos necessário, para tal capacidade deve-se utilizar o valor 0(zero) para a propriedade Maxdop.

    Em relação aos seus questionamentos e observações, elas possuem sentido e estão coerentes com a sua percepção, alguns pontos talvez podemos analisar com alguns outros ajustes.

    Um ponto que podemos focar é o tamanho da fila que você destacou em relação ao seu processador!!! É justamente ai que o Maxdop pode mudar este cenário.


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

    sexta-feira, 21 de agosto de 2015 18:21
    Moderador
  • Obrigado Galvão mais uma vez.

    Irei realizar um novo monitoramento de 12 horas no ambiente para determinar um novo foco de análise de performance. Postarei aqui os resultados.

    Só uma dúvida ainda quanto ao MAXDOP. O servidor tem 6 cores com 12 CPU's lógicos,. De acordo com documentação da MS recomenda-se configurar o MAXDOP = 8 quando o nº de CPU's lógico descrito no servidor ultrapassa o valor 8 e MAXDOP = 0 quando é igual ou abaixo do valor 8. Tenho minhas dúvidas quando essa afirmação. Configurei a opção com o MAXDOP = 4, estaria correto ou deveria eu seguir a recomendação e definir MAXDOP = 8? Outro detalhe, é interessante manter o MAXDOP = 0 e analisar somente o Cost Threshold for Paralellism que está com valor default 5 para obter melhores resultados nas consultas que necessitam de usar paralelismo? Qual a melhor forma de analisar essa questão nos planos de execução.


    sábado, 22 de agosto de 2015 01:20
  • Jerferson,

    Realmente entender o comportamento do SQL Server é algo que para nós simples mortais esta bem longe, acredito que para este tipo de situação temos que recorrer aos Pais do SQL Server, dentre eles Paul Randal!!!

    Eu particularmente procuro seguir as recomendações da documentação Microsoft, e no caso do Maxdop ainda mais, eu sempre deixo o Maxdop = 0 quando utilizo máquinas físicas ou virtuais com processadores abaixo de 8 processadores lógicos e Maxdop=8 para ambientes com mais de 8.

    Em relação aos testes do Cost Threshold(linear) for Parelellism eu penso da seguinte forma, imagine que você acabou de instalar um ambiente limpo do SQL Server e quer realizar alguns testes de performance, então você vai utilizar o conjunto de configurações padrões e conforme os testes forem sendo realizados, você vai poder ir alterandos as configurações.

    Faça exatamente isso, realize testes com as configurações padrões e depois vai ajustando os valores que você deseja para cada configuração, durante estes testes você pode ir salvando o plano de execução para posterior comparação.


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

    sábado, 22 de agosto de 2015 20:57
    Moderador
  • Galvão monitorei novamente o ambiente e percebi melhoras quando a questão do MAXDOP. Fazendo um comparativo com o monitoramento anterior o ajuste na configuração do valor do mesmo percebi uma melhora nos objetos do System e Processo. Aumentar um pouco mais o valor do MAXDOP pode melhorar ainda mais esses resultados, pois de uma certa forma ainda está fora da curva de normalidade recomendada?

    As imagens abaixo comprovam:




    Agradeço desde já!
    sábado, 29 de agosto de 2015 13:55
  • Jerfeson,

    Então não posso afirmar se vai melhorar, pois não estou totalmente familiarizado com o seu ambiente, uma possibilidade é controlar o uso do Maxdop por transação!!!

    Como também analisar o consumo de memória de algumas transações para procurar identificar em qual momento este consumo pode estar fugindo do controle.


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

    domingo, 30 de agosto de 2015 02:04
    Moderador