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?
|
1 2 |
SQL Server Execution Times: CPU time = 9152 ms, elapsed time = 7408 ms. |
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/
|
1 2 3 |
USE Traces EXEC sp_spaceused TESTE_COLLATION_TABELA_1 |
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.
|
1 |
SET STATISTICS TIME ON |
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)”.
|
1 2 3 4 5 6 7 8 9 |
-- SEM PARALELISMO SELECT * FROM TESTE_COLLATION_TABELA_1 TAB1 JOIN TESTE_COLLATION_TABELA_1 TAB2 ON TAB1.ID = TAB2.ID JOIN TESTE_COLLATION_TABELA_1 TAB3 ON TAB2.ID = TAB3.ID JOIN TESTE_COLLATION_TABELA_1 TAB4 ON TAB3.ID = TAB4.ID ORDER BY TAB1.ENDERECO 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:
|
1 2 |
SQL Server Execution Times: CPU time = 3062 ms, elapsed time = 7790 ms. |
Teste – Com Paralelismo:
Agora vamos executar a mesma query do teste anterior, mas vamos remover o HINT “OPTION(MAXDOP 1)”.
|
1 2 3 4 5 6 7 8 9 |
-- COM PARALELISMO USE Traces SELECT * FROM TESTE_COLLATION_TABELA_1 TAB1 JOIN TESTE_COLLATION_TABELA_1 TAB2 ON TAB1.ID = TAB2.ID JOIN TESTE_COLLATION_TABELA_1 TAB3 ON TAB2.ID = TAB3.ID JOIN TESTE_COLLATION_TABELA_1 TAB4 ON TAB3.ID = TAB4.ID ORDER BY TAB1.ENDERECO |
- 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:
|
1 2 |
SQL Server Execution Times: CPU time = 9152 ms, elapsed time = 7408 ms. |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'cost threshold for parallelism', N'35' GO EXEC sys.sp_configure N'max degree of parallelism', N'4' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
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:
|
1 2 3 4 5 |
USE Traces SELECT * FROM TESTE_COLLATION_TABELA_1 TAB1 ORDER BY NOME |
- 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:
|
1 2 |
SQL Server Execution Times: CPU time = 515 ms, elapsed time = 3294 ms. |
|
1 2 3 4 5 6 7 8 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'cost threshold for parallelism', N'20' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
- 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:
|
1 2 |
SQL Server Execution Times: CPU time = 672 ms, elapsed time = 2850 ms. |
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.
|
1 2 3 4 5 |
USE Traces SELECT * FROM TESTE_COLLATION_TABELA_1 TAB1 ORDER BY NOME |
- Max Degree of Parallelism = 4:
|
1 2 3 4 5 6 7 8 9 10 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'cost threshold for parallelism', N'20' GO EXEC sys.sp_configure N'max degree of parallelism', N'4' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
- Max Degree of Parallelism = 2:
|
1 2 3 4 5 6 7 8 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max degree of parallelism', N'2' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
- Max Degree of Parallelism = 1:
|
1 2 3 4 5 6 7 8 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max degree of parallelism', N'1' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
- 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:
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.mssqltips.com/sqlservertip/5404/parallelism-in-sql-server-execution-plan/
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/
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
Parabéns muito clara a explicação, show!!!
Obrigado Alex! Que bom que gostou e espero ter ajudado =)
Abraço.
Luiz Vitor
Du kalaio…
Parabéns LL…
Obrigado Walisson kkkk =)
Show!!!
Abraço.
Luiz Vitor
Parabéns Luiz, muito boa a explicação!
Muito obrigado Gustavo =)
E volte sempre xD
Abraço.
Luiz Vitor