Dicas de Tuning – Como o Paralelismo pode afetar o CPU Time?

Olá Humanos,

Você já se deparou com a situação abaixo onde o “CPU Time” é maior do que o “Elapsed Time”? O que isso significa? Você conhece as configurações de paralelismo do SQL Server?

Nesse post mostro alguns exemplos para explicar essa situação. Então vamos ao que interessa…


Conceitos Básicos:

O Rodrigo Ribeiro tem uma série sensacional sobre “Desempenho do Processador x Desempenho do SQL Server”. Recomendo fortemente que você leia essa série, caso queira se aprofundar um pouco mais no assunto.

http://thesqltimes.com/blog/2019/02/20/desempenho-do-processador-x-desempenho-do-sql-server-parte-1/

O Rodrigo é o DBA Team Líder do #TeamFabricioLima e tenho o privilégio de trabalhar diariamente com esse monstro no SQL Server kkk. No post de hoje, vou compartilhar um aprendizado de um dos treinamentos internos que tivemos e que foi ministrado por ele.

Em primeiro lugar, precisamos entender alguns conceitos básicos sobre a CPU:

  • CPU Time:

Mede a quantidade total de CPU que foi utilizada pela query que foi executada, medido em milissegundos.

  • Elapsed Time:

Mede a quantidade total de tempo da query que foi executada, desde o início até a conclusão da mesma, também medido em milissegundos.


Montando o Cenário:

Dessa vez, vou aproveitar a tabela que criei no post anterior. Populei essa tabela com 200 mil registros para ter uma massa de dados para a realização dos testes.

https://luizlima.net/casos-do-dia-a-dia-cuidado-ao-utilizar-o-comando-collate/

Iremos utilizar o comando abaixo para retornar algumas informações sobre o tempo da CPU. Basta executá-lo na sessão antes de executar os testes.

A configuração inicial do paralelismo do SQL Server será:

  • Cost Threshold for Parallelism: 35 
  • Max Degree of Parallelism: 4

Teste – Sem Paralelismo:

Nesse primeiro teste, iremos utilizar o HINT “OPTION(MAXDOP 1)” para forçar o SQL Server a utilizar no máximo um core na execução da query, ou seja, estamos impedindo que o SQL Server utilize o Paralelismo.

OBS: Deixei alguns links no final do post com mais informações sobre o HINT “OPTION(MAXDOP 1)”.

  • Plano de Execução:

Repare que nesse caso o Plano de Execução não utilizou o paralelismo e o “CPU Time” ficou menor do que o “Elapsed Time”!

  • Desempenho:

Teste – Com Paralelismo:

Agora vamos executar a mesma query do teste anterior, mas vamos remover o HINT “OPTION(MAXDOP 1)”.

  • Plano de Execução:

Observe que dessa vez o Plano de Execução está utilizando bastante o Paralelismo e o “CPU Time” ficou maior do que o “Elapsed Time”!

  • Desempenho:

Conclusão – Testes Paralelismo:

Quando o Plano de Execução utiliza o paralelismo, o tempo de execução de cada CPU utilizada no paralelismo será somado. Com isso, o “CPU Time” fica maior que o “Elapsed Time” em alguns casos.


Configurações de Paralelismo:

Em muitos clientes que atendemos na Consultoria de Banco de Dados do #TeamFabricioLima, é muito comum encontrar a Configuração de Paralelismo utilizando os valores default e isso não é muito legal.

Para conferir os valores do paralelismo, vá no “Object Explorer”, depois clique com o botão direito em cima do nome da instância e escolha a opção “Properties”. Por fim, clique na aba “Advanced”, conforme a figura abaixo:

  • Cost Threshold for Parallelism:

Especifica o valor limite do custo para que uma determinada query possa utilizar um Plano de Execução com paralelismo. O valor default é 5. Com isso, quando uma query é estimada com um custo maior do que 5, ela poderá gerar um plano de execução com paralelismo.

Uma sugestão de boa prática seria utilizar os valores entre 35 a 50. Mas isso pode variar de um ambiente para o outro.

  • Max Degree of Parallelism:

Limita o número de cores que podem ser utilizados por uma query em um Plano de Execução com paralelismo. O valor default é 0, ou seja, uma determinada query pode utilizar todos os cores disponíveis. O valor 1 indica que não irá utilizar o paralelismo, pois está limitando a execução com apenas um core.

Uma sugestão de boa prática para calcular o valor seria:

maxdop = nº de cores / 2

Caso o número de cores seja maior que 16, utilize o valor máximo 8 para o maxdop. Mas isso pode variar de um ambiente para o outro.

https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

CUIDADO: Ao alterar essa configuração, alguns caches do SQL Server serão “zerados”, conforme podemos ver nas mensagens que são exibidas no Error Log.

OBS: Após alterar alguma dessas duas configurações, o novo valor entra em vigor imediatamente sem precisar de Restart.

Segue abaixo um script de exemplo para alterar os valores dessas configurações.


Testes – Cost Threshold for Parallelism:

Agora vamos fazer alguns testes para mostrar o impacto de alterar o Cost Threshold for Parallelism”.

Inicialmente ele está configurado com o valor 35. Com isso, vamos executar a query abaixo:

  • Plano de Execução:

Repare que o custo estimado da query foi de “27,821”. Como esse valor está abaixo de 35, o plano de execução não utilizou o paralelismo. Repare também que o “Degree of Parallelism” está com o valor 1, ou seja, está utilizando apenas um core.

  • Desempenho:
Agora vamos alterar o Cost Threshold for Parallelism” para o valor 20 e executar novamente a query.
  • Plano de Execução:

Dessa vez, o custo estimado da query que era “27,821” é maior do que 20. Com isso, o plano de execução passou a utilizar o paralelismo.

  • Desempenho:

Testes – Max Degree of Parallelism:

Por fim, vamos fazer alguns testes alterando a configuração “Max Degree of Parallelism” e observar o impacto gerado na query. Vamos considerar o Cost Threshold for Parallelism” com o valor 20 para que a query possa utilizar o paralelismo.

  • Max Degree of Parallelism = 4:

  • Max Degree of Parallelism = 2:

  • Max Degree of Parallelism = 1:

  • Comparação de Desempenho:

Segue uma tabela com o comparativo do tempo dos testes que foram realizados:


Conclusão – “Cost Threshold for Parallelism” e “Max Degree of Parallelism”:

Portanto, podemos concluir que quanto maior o valor do MAXDOP, maior o número de cores que podem ser utilizados na query. Consequentemente, maior será o “CPU Time” e em contra partida, menor será o “Elapsed Time”. Contudo, em alguns casos uma query pode demorar mais quando executa com paralelismo do que quando executa utilizando apenas um core por exemplo. Lembre-se que quase toda regra tem sua exceção kkk.

Também podemos observar que o valor do “Cost Threshold for Parallelism” pode influenciar no plano de execução de uma query, dependendo do valor do custo estimado da mesma.

Para finalizar, tenha muito cuidado ao alterar essas configurações de paralelismo, pois pode gerar grandes impactos no ambiente dependendo dos valores que forem utilizados. Recomendo que sempre após fazer a alteração, faça um monitoramento para verificar se alguma query está sendo impactada e demorando mais do que o normal.


Download “Como o Paralelismo pode afetar o CPU Time”:

Segue o link para baixar o script utilizado nesse Post:

Link: https://github.com/luizvitorf/SQLServer/blob/master/Scripts/CPU/Como%20o%20Paralelismo%20pode%20afetar%20o%20CPU%20Time.sql


Referências:

Segue abaixo mais alguns links, caso você queira se aprofundar no assunto:

https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads

https://www.red-gate.com/simple-talk/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

https://www.mssqltips.com/sqlservertip/5404/parallelism-in-sql-server-execution-plan/

https://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/

https://docs.microsoft.com/pt-br/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option

https://littlekendra.com/2016/07/14/max-degree-of-parallelism-cost-threshold-for-parallelism/

https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

https://ericksonricci.wordpress.com/2012/09/27/o-que-acontece-quando-se-altera-a-configuracao-maxdop/


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

6 comentários em “Dicas de Tuning – Como o Paralelismo pode afetar o CPU Time?

Deixe uma resposta