Max Threshold of Paralleslim(MAXDOP) & Cost Threshold of Parallelism

 

Uma dúvida, que eu tive muito no início da minha carreira como DBA, e que muitas pessoas podem ter é referente ao MAXDOP. Eu particularmente adoro saber o que faz cada processo, porém amo mais ainda entender a fundo como a configuração e cada processo funcionam.

Com o intuito de ajudar quem já teve essa dúvida, nesse artigo falaremos sobre Paralelismo. Para iniciarmos o assunto precisamos entender o que é o processamento paralelo.

O processamento paralelo tem como objetivo quebrar tarefas grandes em pequenas tarefas, o SQLServer usa essa mesma lógica, pega processos com cargas de trabalho excessiva e processa de forma paralela.

O Query Optimizer leva em conta três configurações ao gerar um plano de execução que se beneficie de processamento paralelo. São elas:

 

• Limite de custo para o paralelismo
• Grau máximo de paralelismo (MAXDOP)
• Máscara de Afinidade (Affinity Mask)

 

Agora que já entendemos um pouco de processamento paralelo, precisamos entender o que é MAXDOP!

Grau máximo de paralelismo, ou (MAXDOP), é um parâmetro que pode ser configurado a nível de servidor, de banco ou de consulta, para determinar o número máximo de processadores lógicos, que podem ser utilizados para consultas candidatas a um plano de execução paralelo. Por padrão o valor para o parâmetro MAXDOP é 0.

Cost Threshold for Parallelism é um parâmetro que o Query Optimizer utiliza para criar planos de execução para consultas candidatas a paralelismo. O SQLServer executa os planos de execução paralelos, criados pelo Query Optimizer, somente quando o custo estimando para criar um plano serial for maior do que o valor do parâmetro Cost Threshold for Parallelism.

O Cost Threshold for Parallelism pode ser definido com valores entre 0 e 32767, por default esse valor vem setado como 5. Ele pode ser configurado e visualizado a nível de instância e através da sp_configure.

Antes de configurarmos o MAXDOP, por boas práticas para acharmos o valor ideal para o MAXDOP temos que calcular, utilizando a metade da quantidade de processadores por NUMA NODE, assim, encontraremos o valor ideal para o MAXDOP!

 

Interessante as informações acima, e como poderíamos configurar o MAXDOP, e o Cost Threshold for Parellelism?

Exemplo 1:

            A nível de instância e temos as duas formas abaixo:

Exemplo 2:

               A nível de consulta:

Exemplo 3:

            A nível de banco de dados, podemos configurar das duas formas abaixo:

 

Quando analisamos um plano de execução é fácil identificar consultas que se beneficiam do uso de paralelismo, uma das formas de identificar e através dos Exchanges Operators(Operadores de troca).

Nas imagens a seguir vamos falar um pouco desses operadores e como eles funcionam:

Outra forma rápida para identificar consultas que fazem paralelismo, é através do Wait CXPACKET. O que esse Wait Type?

CXPACKET – O tempo de espera elevado nesse Wait indica que estão ocorrendo ou ocorreram operações utilizando plano de execução paralelo. Se a espera for excessiva, e não puder ser reduzida através do ajuste da consulta, com a criação de índices por exemplo, considere ajustar o Cost Threshold for Parallelism e o MAXDOP.

Demo 1:

Abaixo vamos rodar uma consultas utilizando paralelismo com as configurações padrões do SQLServer. Não sei se lembram anteriormente, falamos que por padrão o Cost Threshold for Parallelism vem com o valor = 5. Em alguns casos, um plano paralelo pode ser escolhido mesmo que o plano de custo da consulta seja menor que o Cost Threshold atual para o valor de paralelismo. Isso pode acontecer porque a decisão de usar um plano paralelo ou serial é baseada em uma estimativa de custo fornecida anteriormente no processo de otimização.

Na execução da demo 1, nosso Degree of Parallelism está como 8, isso significa que como deixamos o valor que vem por default para o SQLServer, e ele está utilizando todos os meus processadores para executar a consulta.

Nessa primeira demo, além de nortamos o no CPUTime e Elapsed Time, elevados ocupando todos os Threads, notamos no WaitStats o Wait CXPACKET com maior tempo, rodando uma consulta que pega os Waits, também é possível ver esse Wait com tempo elevado quando existem consultas candidatas a um plano de execução paralelo.

 SQL Server Execution Times:

   CPU time = 12951 ms, elapsed time = 37489 ms.

Demo 2:

Iremos configurar o MAXDOP, e o Cost Threshold, lembrando que para o meu caso tenho uma NUMA NODE com 8 Processadores, por isso meu maxdop = 4. Podemos notar a diferença em relação a demo 1, que eu deixei os valores default.

 

--Definir MAXDOP e Cost Threshold

EXEC sys.sp_configure

@configname = 'show advanced options',

@configvalue = 1;

RECONFIGURE;

 

EXEC sys.sp_configure

@configname = 'cost threshold for parallelism',

@configvalue = 10;

RECONFIGURE;

 

EXEC sys.sp_configure

@configname = 'max degree of parallelism',

@configvalue = 4;

RECONFIGURE;

Utilizando agora a configuração recomendada por NUMA NODE, temos um CPUTime menor do que na Demo 1.

 SQL Server Execution Times:

   CPU time = 10657 ms, elapsed time = 37545 ms.

Demo 3:

Iremos configurar o MAXDOP, e o Cost Threshold, utilizando um exemplo que já vi sendo utilizado em alguns lugares. alterando o MAXDOP para 2 e testando. Podemos notar que utilizando 2 processadores o meu Estimated Subtree Cost aumentou.

Nessa demo o nosso CPUTime reduziu, porém temos um Elapsed Time maior, do que em demos anteriores.

SQL Server Execution Times

    CPU time = 9000 ms, elapsed time = 38521 ms.

Demo 4 :

O que acontece se colocarmos o MAXDOP, com o valor 1, será que utilizará somente 1 processador e irá melhorar em questões de performance?

Como assim, sumiu o paralelismo?

Na verdade ele foi inibido, porém esse cenário não pode ocorrer, temos que ter esses parâmetros configurados para que as consultas candidatas a um plano de execução paralelo, possam se beneficiar do paralelismo, trazendo a melhor performance. Quando colocamos nosso MAXDOP com o valor 1, o SQLServer suprime o Cost Threshold. Porém isso tem consequências como podemos notar nos tempos abaixo CPUTime que é quantidade de ciclos de CPU gasto pelos Threads para executar as etapas da instrução, em relação as outras demos, nosso Elapsed Time, que é o tempo total para executar a instrução aumentou, e nosso Estimated Subtree Cost também.

SQL Server Execution Times:

    CPU time = 6672 ms, elapsed time = 42960 ms.

Conclusões:

Lógico, nos exemplos acima estou rodando somente uma consulta, então ela não vai se beneficar o máximo de paralelismo, para que isso ocorra eu teria que estar rodando mais consultas em paralelo. É recomendado utilizar a metade de processadores por NUMA NODE como um bom valor para o MAXDOP. Porém o mais ideal seria testar para chegarmos em um Degree of Parallelim que irá favorecer todas as consultas, ou a maioria das consultas que utilizam paralelismo, para isso é preciso alguns testes, e horas de pesquisa, só então conseguiremos chegar no melhor para cada ambiente de banco de dados.

Espero que esse artigo possa ajudar, e salientar sobre a importância de se configurar o Cost Threshold e o MAXDOP corretamente, esse é um assunto muito vasto e gostoso de ser explorado, porém se entramos afundo dá um livro, esse artigo. Seguem abaixo algumas referências!

Referências:

MAXDOP Microsoft

Cost Threshold Microsoft