Desempenho do Processador x Desempenho do SQL Server – Parte 2

Olá caro leitor,

Esta é mais uma série de posts, e o assunto da vez é o desempenho do processador. No post anterior, vimos alguns conceitos básicos sobre velocidade da CPU. Agora vamos aplicar um pouco desses conceitos no SQL Server e observar alguns efeitos na prática.

Revisão:

• Clock = dispositivo da CPU que liga e desliga em uma certa frequência (clock tick)
  • 1 Hertz = 1 vez por segundo
  • 1 KHz = Mil vezes por segundo
  • 1 MHz = Um  milhão de vezes por segundo
  • 1 GHz = 1 bilhão de vezes por segundo
• Cada instrução leva X clocks ticks. Quanto mais rápido um processodor, mais rápida as instruções executam.

No caso de código SQL, uma infinidade de instruções são executadas, não somente para acessar os dados e fazer alguma operação em cima deles, mas também para:

• verificar locks/latches
• checar permissões
• acionar disco, rede

São centenas de milhares de instruções sendo executadas por uma simples consulta SQL. Isto significa que qualquer alteração na velocidade da CPU, a query poderá ser impactada (para melhor, ou para pior).

Vamos fazer um simples exercício, baseado numa query simples como esta:

SELECT SUM(Valor) FROM Pedidos

• Imagine que a query acima gaste  10 instruções. E pra simplificar mais ainda, imagine que cada instrução desta gaste apenas 1 tick do nosso clock.
• Imagine que essa query execute num incrível processador de 100 Hertz (isso mesmo, cem ticks por segundo)
• Primeiro, calcule o tempo de um tick:
  • 1 tick = 1000ms/100
  • 10ms = Cada tick nesse nosso incrível processador gasta apenas 10 ms!
• Ora, se nossa query gasta 10 ticks, e cada tick tem 10ms, então ela vai gastar 100 ms de CPU (10ms * 10 ticks = 100 ms).
• NOTA MUITO IMPORTANTE:  Aqui é só uma simulação de um gasto SOMENTE de CPU. Não estou colocando outras variáveis, como disco, memória, rede, etc.

Um exemplo de uma query que gasta um número X de instruções. O tempo total de CPU, vai sempre depender da velocidade do processador

Quando otimizamos essa query, criando um índice, ou adicionando mais filtros, estamos reduzindo o número de instruções (ou escolhendo instruções que gastam menos tempo).  E por isso o tempo de CPU diminui. Por exemplo, imagine que após criar um índice, causando a leitura de menos dados, a query execute com  apenas 4 instruções, então o tempo, no processador de mesma velocidade, será de 40ms ( 10ms * 4 ticks).

Simulando a otimização da query. Por exemplo, saindo de um clustered index scan, para um index scan, poderia causar a execução de menos instruções, devido a um menor número de páginas, menos colunas, etc. Tudo isso impacta na quantidade de instruções no final.

Simples, não?

Velocidade do Processador na prática

Vamos fazer o seguinte: Vamos brincar com nosso processador e observar como a velocidade do mesmo impacta em nossas consultas.

Os processadores mais modernos permitem alterar sua velocidade dinamicamente para poupar energia. Um clock que opera a 2.5 GHz gasta mais energia do que o mesmo clock operando a somente 100 MHz. Geralmente, os sistemas operacionais nos fornecem várias opções de controlar o gerenciamento de energia do processador.

Vamos criar um simples banco de dados com uma tabela grande e alguma quantidade considerável de registros:

-- Cria um banco de testes...
CREATE DATABASE CpuSpeed;
-- Usando nosso banco de testes...
USE CpuSpeed;
-- Cria uma tabela simples...
CREATE TABLE Pedidos(valor bigint);
-- Insere alguns valores aleatorios. pode demorar um pouco...
INSERT INTO Pedidos SELECT abs(checksum(newid())) FROM sys.objects o1,sys.objects o2,sys.objects o3;

• Este código simplesmente cria um banco novo, uma tabela neste banco e insere alguns registros aleatórios.
• Eu faço CROSS JOINs entre várias instâncias da view “sys.objects” apenas para gerar um grande número de registros.
• O abs(CHECKSUM(newid())) gera um número aleatório para cada linha.

Antes de executar alguma query, vamos garantir que nosso computador irá operar no seu potencial máximo:

1. Procure as opções de energia do seu computador. Você pode digitar powercfg.cpl no menu iniciar.
2. Irá abrir tela tela semelhante a esta:

   

3. Clique em “Alterar configurações do plano”, no respectivo plano que está marcando com a bolinha preta (No meu caso, o plano se chama “Dell”).
4. Na próxima tela, cliquem em “Alterar configurações de energia avançadas”:

   

5. Na janela que abrir, procure por “gerenciamento de energia do processador”:
   
6. Altere o valor de mínimo para 100. Certifique-se que o máximo esteja em 100%, caso contrário, ajuste também. Se você está usando notebook, mas ele está usando a bateria como fonte de energia, então faça as alterações na opção “Na Bateria”. Se quiser garantir, pode alterar a configuração tanto para bateria, quanto para tomada. Memorize os valores que estavam antes para voltar a configuração original depois, ok?
7. Pronto, clique em Aplicar, depois ok.
8. Agora observe no Gerenciador de Tarefas (caso utilize um gerenciador de tarefas de uma versão anterior ao 2012 do Windows, você pode baixar a ferramenta CPU-Z):

   

9. A velocidade deverá ficar sempre maior ou igual ao “velocidade base”.

O quê esta opção fez? Nós apenas solicitamos que o nosso processador sempre trabalhe no máximo de velocidade possível. Com isso,  mais energia será gasta, porém, nosso processador nunca irá baixar a velocidade. No meu caso, meu processador consegue trabalhar em 3.1 GHz (3 bilhões de ticks por segundo).

Esse percentual de CPU que você configurou NÃO é o mesmo percentual de CPU reportado pela maioria das ferramentas de monitoramento, como o próprio gerenciador de tarefas, process explorer, perfmon, etc. Seu processador ainda poderá bater 100% de uso! Explicaremos melhor depois.

Pronto, acabamos de deixar o processador em seu desempenho máximo, vamos rodar uma consulta e observar o tempo de CPU. Utilize o seguinte comando para obter o tempo de CPU:

SET STATISTICS TIME ON

Agora, execute a seguinte query:

-- Executando uma query com maxdop 1 para garantir que somente uma CPU será usada
-- O recompile é pra forçar um novo plano de execução sempre (e observarmos o tempo de CPU gasto na compilação também)
select avg(valor) from Pedidos option(maxdop 1, recompile)

Na aba “Messages” observe o tempo da execução. Se você acabou de reiniciar a instância, execute novamente, para garantir que iremos fazer leitura somente de dados em memória. Você está interessado no valor da informação “Cpu Time”:

O primeiro grupo de informação é referente ao tempo de compilação/recompilação da query. Ele rodou abaixo de 1ms, e por isso exibe zero.

Já o segundo grupo, este sim nos interessa. Ele nos diz quanto de CPU a query gastou, através da informação “Cpu time”. Note que o valor é de 266ms.

Agora vamos reduzir o máximo do nosso processador pela metade, e observar o que acontece com nossa query. Acesse novamente o powercfg.cpl, e repita o procedimento:

• Escolha alterar configuração do plano atual
• Depois vá em alterar configurações de energia avançadas
• Vá em gerenciamento de energia do processador
• Coloque o mínimo e o máximo em 50% (lembre-se, se não estiver conectado na tomada, altere o da bateria).

Confirme no Gerenciador de tarefas, ou no CPU-Z, que a velocidade do clock diminuiu.

Diminuindo o clock do processador para 50% da sua velocidade. Não espere nunca uma redução de exatos 50%. Poderão sofrer algumas pequenas variações, porque isso depende do processador, do sistema operacional, etc.

Note que a velocidade do clock de meu processador caiu para 1.29 Ghz

Perceba que agora minha CPU estará rodando com cerca de metade da força. Como será que isso vai impactar no tempo da minha query?

Minha query saiu de 266ms para 594ms. Um aumento de 123% no consumo de CPU, e tempo da query.

Muito interessante como a velocidade da CPU pode afetar o tempo de execução das consultas. No próximo post vou explicar melhor o que aconteceu e falaremos de outras coisas mais!

Até lá!

Rodrigo Ribeiro Gomes

DBA Team Leader na Power Tuning