Desempenho do Processador x Desempenho do SQL Server – Parte 4

Post 4/4. Este post é parte da série: Desempenho do Processador x Desempenho do SQL Server
Tempo de leitura estimado: 7 minutos

Quando falamos de desempenho de processador, estamos falando de tempo, conforme já foi mostrado nos posts anteriores desta série. A conta é simples:  Um processador mais rápido significa que executa instruções em menos tempo!

Mas como o tempo de CPU é transformado em uma porcentagem? Essa foi uma pergunta que me fizeram há anos: Quantos por cento significa esses tempos reportados nas DMVs do SQL Server? Eu fui atrás das respostas e isto gerou até um artigo que foi capa na SQL Magazine!

No SQL Server, a famosa sys.dm_exec_requests tem uma coluna chamada cpu_time.  Você também conheceu o SET STATISTICS TIME, que traz informações sobre tempo de CPU gasto por queries em uma determinada sessão. E ainda há muitos outros lugares que reportam tempos de CPU dentro do SQL. Qual é a relação desses tempos com o percentual de CPU reportado no gerenciador de tarefas, por exemplo?

A resposta é simples, mas para entendê-la, vamos voltar um pouco pro sistema operacional!

Tempo Total de CPU

A métrica mais simples de entender, é o tempo total de CPU. Ela é apenas um valor, geralmente em microssegundos ou milissegundos, indicando por quanto tempo a CPU foi usada para completar uma operação, ou, quando a operação ainda não foi completada, quanto tempo de CPU já foi gasto até o momento. O SQL Server consegue mensurar o tempo de CPU gasto por suas operações graças ao Windows, que cuida de manter essa informação atualizada, e a fornece aos processos, quando eles precisam!

Basicamente, o Windows guarda as informações de tempo de CPU por thread. Uma thread representa uma sequência de instruções. Todo processo (programa em execução), tem pelo menos uma thread e com o SQL Server não é diferente. Cada thread pode executar em apenas uma CPU por vez (e uma CPU é capaz de rodar apenas uma thread), e o Windows é responsável por decidir qual thread vai rodar em qual CPU.

 

 

Sempre que o Windows coloca uma thread em uma CPU, ele periodicamente vai atualizando o tempo em que esta thread está rodando na CPU. Sendo assim, até que a thread seja encerrada, o Windows mantém um tempo total de CPU gasto para cada uma das threads existentes, de cada processo.

A imagem mostra uma linha do tempo com duas threads de exemplo (que podem ser do mesmo processo, ou de processos diferentes). A medida que o Windows retira e coloca threads para execução, ele vai atualizando as informações de execução! A frequência com que essa atualização é feita depende de muitos fatores, mas é em questão de millisegundos

Com essa informação é possível que qualquer outro programa possa monitorar o consumo de CPU de qualquer operação. Por exemplo, para que o SQL Server saiba quanto tempo de CPU um comando gastou, ele poderia utilizar os seguintes passos:

  1. Obter o tempo total de CPU gasto até agora pela thread que vai rodar a query.
  2. Deixa a query rodar até o final
  3. Depois que a query rodar, basta subtrair  o tempo total da thread, pelo tempo capturado no passo 1.

Por exemplo, vamos supor que o SQL Server vá rodar um comando SELECT.  Para que este comando execute, uma thread (que dentro do SQL Server, ele chama de worker) já está associada com essa execução, então ele pode fazer o seguinte:

  • Obter (do Windows) o tempo total de CPU que essa thread já gastou durante a vida dela. Suponha que seja 2000 millisegundos. Vamos chamar de TempoInicio = 2000
  • Executa a query…
  • Após a execução, obter novamente o tempo total e CPU que a thread gastou. Como a thread precisou rodar a query, certamente ela precisou entrar algumas vezes na CPU, aumentando o valor do seu tempo total de CPU. Suponha que tenha aumentado para 3000ms. Vamos chamar de TempoFim = 3000
  • Agora é só calcular:  TempoFim – TempoInicio = 3000 – 2000 = 1000ms
  • Ou seja, a query gastou 1000ms de CPU!

Você vai ver que, utilizando esta mesma lógica, o SQL Server consegue registrar diversas informações de tempo de CPU, não somente para cada query que executa, mas para outros níveis como requests, sessões, tempo de compilação, por pool do Resource Governor, etc.

Todo esse exemplo foi considerando queries que rodam sem paralelismo. Com paralelismo, a conta tem apenas uma pequena variação, mas segue todos esse princípios. Veremos em outros posts da série.

 

Percentual de uso de CPU

O percentual de tempo de CPU já é um valor um pouquinho mais elaborado. Como é uma porcentagem, ele é um valor que se baseia em um total. Você já se perguntou quanto é o 100% de CPU?

Muitos podem pensar que o 100% significa estar usando a capacidade máxima da CPU.  Mas esse percentual, não tem nada a ver com capacidade, até porquê ou há algo executando na CPU, ou não há.

Essa informação é calculada usando um intervalo de tempo previamente definido. A ideia é contabilizar quanto desse intervalo de tempo foi gasto processando algo na CPU. Por exemplo, se em um intervalo de 1 segundo, 400 ms foram gastos processando algo, então podemos dizer que a CPU esteve utilizada por 40% daquele intervalo de 1 segundo (400ms/1000ms).

E é assim que a maioria das ferramentas do Windows fazem: Elas coletam o tempo total de CPU de uma thread, e após um certo intervalo, coletam novamente. Então, para calcular quanto foi gasto de CPU nesse intervalo, elas apenas subtraem a segunda coleta pela primeira, e dividem pelo intervalo que se passou. A fórmula é essa:

(T2 – T1)/Intervalo

Onde T2, é o tempo total de CPU da segunda coleta, T1 é o da primeira, e Intervalo é tempo que se passou.

Ou seja, uma thread que gaste 100% de CPU, significa que durante todo o intervalo que se passou, ela não saiu da CPU. Geralmente, esse intervalo é de 1 segundo, sendo que a maioria das ferramentas de monitoramento permite que você altere este valor.

Repare a diferença entre total de cpu, e percentual de utilização de CPU. O total de cpu é um valor acumulado que representa tudo o que um recurso (thread, query, request, sessão, etc.) gastou durante toda sua vida. Já o percentual de uso de CPU é um valor relativo, indicando o uso de CPU em um dado momento no tempo.

 

Tempo de CPU x Percentual de CPU

Isso cria situações interessantes com as quais você pode se deparar: Por exemplo, imagine uma query que tenha gasto 2 segundos para executar na CPU. Esse é o tempo total de CPU. Dependendo de diversos fatores, ela pode ter ocasionado vários padrões de percentual uso de CPU:

 

Exemplo 1

  • Entre o intervalo 1 e 2, gastou 500 ms de CPU, causando 50% de uso
  • Entre o intervalo 2 e 3 não gastou nada (por esperar disco por exemplo), ocasionando 0% de CPU
  • Entre o intervalo 3 e 4, gastou 1 segundo, ocasionando 100%
  • Entre o intervalo 4 e 5, não gastou nada (devido a locks, por exemplo), ocasionando 0% de CPU
  • Entre o valor 5 e 6, ter gasto mais 500ms, ocasionando 50%

 

Exemplo 2

  • Entre o intervalo 1 e 2,  gastou 700ms, ocasionando 70% de uso
  • Entre o intervalo 2 e 3, gastou 1s, ocasionando 100% de uso
  • Entre o intervalo 3 e 4, e 4 e 5, gastou nada, ocasionando 0%
  • Entre o intervalo 5 e 6, gastou 300ms, ocasionando 30% de uso

Exemplo 3

  • Entre o intervalo 1 e 2,  gastou nada (0%)
  • Entre o intervalo 2 e 3, gastou 1s (100%)
  • Entre o intervalo 2 e 3, gastou 1s (100%)

 

Perceba como a mesma query, com o mesmo tempo total de CPU, pode apresentar diferentes padrões de percentual de utilização, devido a teoria por detrás destes conceitos! Isso nos leva a uma terceira definição de tempo que está envolvido nisso tudo: O tempo de duração! O tempo de duração (elapsed time) é o tempo total gasto para executar a query, incluindo o tempo de CPU e o tempo de espera.

Por exemplo, no exemplo 1 e 2, a query teve duração de 6 segundos, desses 6, 2 segundos foram gastos com CPU! Já no exemplo 3, a duração foi de 3 segundos, gastando os mesmos 2 segundosde CPU.

É por esse simples motivo, que o SQL Server reporta tempos completamente diferentes e que as vezes não fazem sentido algum quando você compara com a porcentagem reportada no Gerenciador de Tarefas.  Na maior parte das informações fornecidas pelo SQL Server, ele está falando de “tempo total de CPU” e não de “percentual de utilização”.  O tempo total de CPU é um acumulado durante toda a operação, enquanto que o percentual de utilização é baseado em determinado momento no tempo. Então, não tem como você dizer quantos por cento foi gasto por uma query que gastou 2 segundos CPU no passado. Você  precisaria ter capturado isso no momento em que ela foi executada.

O SQL Server fornece informações de consumo de CPU em vários níveis, como por query, por sessão, por requests, etc. Ao longo de outros posts desta série, iremos explorar mais estes conceitos e observar com alguns exemplos práticos como podemos utilizá-los para fazer uma melhor análise do consumo de CPU no SQL Server!

E se você quer aprender mais sobre SQL Server e o Windows, não deixe de fazer o curso Fundamentos de Windows para DBA SQL Server – Módulo 1! Tem várias perguntas pra testar seu conhecimento, certificado de conclusão, e mais de 30 minutos FREE sobre algumas ferramentas que todo usuário do Windows precisa conhecer! Confira em https://cursos.fabriciolima.net e enxergue novas formas de administrar seu ambiente SQL!

 

 

 

 

Navegue na série << Desempenho do Processador x Desempenho do SQL Server – Parte 3
Compartilhe este post!

Comments ( 5 )

  1. / ReplyTássio Carlini
    Olá Rodrigo, Primeiramente parabéns pela sequência de post, muito esclarecedor. fiquei com 1 dúvida referente ao cálculo da % por exemplo: SQL Server Execution Times: CPU time = 8344 ms, elapsed time = 4647 ms. ou Seja (8344/4647) = 1,79% de CPU, no total da execução ou por segundo de execução?
    • / ReplyRodrigo Ribeiro Gomes
      Opa Tássio! Muito obrigado! Vou esclarecer pra você! Há vários pontos nestes dados que você mandoi! Primeiro, explicando esses números: CPU Time é o tempo total de CPU que foi necessário para executar essa query, enquanto que o elapsed time é o tempo de duração (isto é, o tempo que levou para a query executar e retornar um resultado). A divisão que você fez não necessariamente representa o percentual reportado no Gerenciador de Tarefas. Vou fazer com um exemplo mais simples: Imagine: CPU time = 2000 ms, elapsed time = 4000 ms Neste caso, a query levou 4 segundos, sendo 2 segundos de CPU. Esses 2 segundos, representam 50% desses 4 segundos. Mas, no gerenciador de tarefas, dependendo de certas várias como acesso a discos, locks, etc., o consumo de CPU não foi de 2 segundos direto. Ele pode ter rodado 100ms, depois aguardo por locks, depois rodando mais 500ms, etc. Ou seja, você poderia ter visto o seu gerenciador de tarefas bater 10%, depois 50%, etc. Então, quando você divide CpuTime por ElapsedTime você está obtendo o percentual do total da execução, e não dos intervalos por segundo. E como eu disse no post, apenas com esses números é impossível dizer qual foi o padrão de gasto. Podem ter N padrões de percentual de uso, e tudo isso vai depender de uma série de fatores. Um segundo ponto, que irei abordar em outros posts da série, é sobre o fato de que no seu exemplo, CpuTime > ElapsedTime. Alguém pode ser perguntar: Mas como minha query demorou 4.6 segundos, mas gastou 8 segundos de Cpu? Viagem no tempo? rs! Não mesmo. Isso é um belo indicativo de que houve paralelismo. O CpuTime é a soma do tempo considerando todas as threads que foram usados para rodar a query. Sempre que ele for maior, a gente sabe que houve paralelismo (e o inverso nem sempre é verdade: pode haver CpuTime <= ElapsedTime e mesmo assim ter acontecido paralelismo). Ou seja, mais de uma CPU no seu gerenciador de tarefas pode ter apresentado diferentes padrões de uso de CPU. E, de novo, isso a gente só consegue saber observando na hora ou capturando isso de algum forma. A sua divisão (8344/4647) resultou em 1,79 (e não 1.79%). Para transformar em porcentagem, você multiplica por 100, resultado em 179%. Quando comparando com o tempo de duração, fica meio confuso dizer que a query consumiu 179% do tempo de execução. Quando lidamos com paralelismo, os resultados tendem a ficar mais confusos, mas você vai ver que em posts futuros, todas essas métricas de consumo de CPU devem ser enxergadas como apenas um número representando o quanto do seu poder de processamento está sendo usado, e não necessariamente comparar ele com o tempo de duração da query. Se ainda ficou com alguma dúvida, é so dizer que responderei com todo prazer, ou tento ser mais claro.
      • / ReplyTássio Carlini
        A agora encaixou melhor as coisas hehe, muito obrigado pela atenção rodrigo.
  2. / ReplyAnderson
    Fantástica essa série! Eu geralmente uso como métrica a XE System Health para ver o consumo de CPU. Mas com suas explicações, vou buscar analisar melhor quando o assunto for CPU. Em todos o servidores de banco de dados de produção, sempre configuro para a parte de energia para alto desempenho (peguei essa dica num post do Fabrício) e realmente é de se assustar na diferença. Estarei no aguardo de mais posts para aprender melhor sobre o CPUxSQL Server. Sucesso para você
    • / ReplyRodrigo Ribeiro Gomes
      Booa Anderson! Se você estiver se referindo a systemCpuUtilization e sqlCpuUtilization, elas são boas informações. É um ponto de partida. Esses valores são percentuais, logo são baseados em um total (não posso dizer necessariamente se é baseado em um intervalo de 1 segundo, mas vou atrás dessa resposta e respondo aqui pra você, quando tiver). Essa questões do alto desempenho realmente faz uma grande diferença em muitos casos. Eu sempre recomendo testar usando alguma ferramenta como CPU-Z ou gerenciador de tarefas, nas versões a partir do 2012, para confirmar que o servidor está realmente operando na velocidade máxima! Em breve solto mais posts! Obrigado!!

Leave a Reply to Rodrigo Ribeiro Gomes Cancel reply