Max Degree of Parallelism - CXPACKET - MAXDOP

De forma sucinta, o paralelismo no SQL Server foi criado com o intuito de reduzir o tempo de processamento das consultas.
 
Acompanhe o seguinte raciocínio, imagine que você possui uma tarefa em seu trabalho da qual você possa dividir com os seus colegas. Com a divisão dessa atividade para realizar essa tarefa, a tendência é que essa tarefa seja realizada de forma mais rápida, correto?
 
Agora vamos utilizar um outro exemplo relativo a divisão de atividades. Imagine que você recebesse uma atividade pequena e que essa atividade demorasse menos de um dia para ser executada, mas mesmo assim você optasse por dividi-la entre mais dois colegas. Antes de iniciar o trabalho você teria que gastar um tempo (mesmo que curto) para dividir essa atividade em tarefas menores. Ao final do trabalho, vocês teriam que verificar se cada um executou sua tarefa corretamente e precisariam sincronizar o status atual, para assim concluir se a atividade foi completada. Porém neste caso, a tarefa foi tão curta que você acabou envolvendo outras duas pessoas para dividir essa atividade e acabou gastando uma grande parte do tempo só para "acertar" com seus companheiros quem vai fazer o que, além de ao final do dia ter sincronizar se a atividade foi concluída como um todo. Neste caso, será que não teria sido melhor executar a tarefa individualmente?
 
Ao paralelizar um trabalho, o SQL Server não funciona de maneira diferente. Ele irá dividir esse trabalho ou tarefa (consulta ou rebuild de Indexes) e divide em várias tasks (tarefas) menores, em seguida cada task é atribuída a uma Worker Thread que executará parte da consulta. Após essa divisão cada thread que faz parte deste trabalho será executada em um scheduler diferente, ou seja, em um processador lógico diferente. A quantidade de schedulers que envolve os processadores que serão utilizados para execução das tarefas é chamado de DOP - Degree of Parallelism.
 
A ideia de paralelizar a Query em tarefas menores é a mesma do exemplo que anterior sobre os colegas de trabalho e obter um tempo de resposta menor para realização da de uma atividade. No caso do SQL Server é chamado de Total elapsed time ou tempo de retorno dos dados da consulta.
 
Essa separação de tarefas e sincronização ao final das execuções em paralelo é feita por operadores que chamamos de Exchange Operators. Antes do fim da execução da task pela thread, essa tarefa fica aguardando para ser sincronizada com as demais e assim retornar os dados da consulta para o usuário. Enquanto sua consulta (query) ou rebuild do índice esperapela sincronização, você notará na sys.dm_exec_requests ou sys.dm_os_waiting_tasks o famoso wait_type de CXPACKET. Ele é, nada mais nada menos, que essa sincronização feita por esses Exchange Operators.
 
Assim como na analogia feita anteriormente, pode ser que não valha tanto a pena a execução em paralelo, pelo simples fato de que o tempo gasto para sincronizar as atividades ao final com os seus companheiros, não valha a pena. No SQL Server isso também pode ocorrer. Por exemplo, imagine que dentre um conjunto de threads, a minha query foi paralelizada e com isso uma delas irá demorar um pouco mais entregar sua task realizada, consequentemente todas a outras tasks terão que esperar mais tempo aguardando para que essa task seja completa. Assim como isso poderá ocorrer casos que haja uma divisão inadequada de tarefas no início da paralelização, acarretando em mais registros para uma thread que efetuaria menos trabalho que as outras, fazendo com que o wait_type CXPACKET fique mais evidente.
 
O SQL Server, por padrão, utiliza o Cost threshold for Parallelism como 5 ou o que for atribuído na opção do sp_configure para 'Cost threshold for parallelism'. Quando uma consulta é compilada e otimizada pelo Query Optimizer, caso o custo da Query seja maior que 5 e seu servidor possuir vários processadores, ele irá gerar um plano paralelizado para sua consulta, e então decidirá qual vale mais a pena para ele, paralelo ou o serial.
 
Porque resolvi escrever sobre o assunto?
 
Porque não é raro, sendo até uma prática bastante comum, que alguns DBAs quando se depararam com um aumento de wait_type CXPACKET, rapidamente atribuírem no sp_configure o Max Degree of Parallelism igual a 1, isso por sua vez acarretará que a instância como um todo execute suas queries serialmente, ou seja, executará uma consulta utilizando apenas um Scheduler ou seja um processador lógico especifico para aquela consulta. Agora vem minha pergunta, será que isso é realmente o melhor a fazer em uma máquina com 80 ou 256 processadores? Será que ao invés de eu atribuir o Max Degree of Parallelism da instância inteira para 1 não poderia atribuir a OPTION(MAXDOP 1) para as consultas problemáticas em questão?
 
Agora gostaria esclarecer algumas dúvidas que são comuns sobre paralelismo.
 
É correto afirmar que um ambiente OLTP não deveria fazer paralelismo nas consultas?
A resposta é: Depende. Essa afirmação é válida apenas quando dizemos que em um ambiente OLTP performático executando queries e transações curtas, que possui índices adequados não faça muitos Scans nos índices nas tabelas que referencia. Se esse é o caso do ambiente de vocês, a resposta é sim e meus parabéns...rsrs, caso contrário, acredito que o paralelismo pode ajudar em várias ocasiões.
 
Se o custo da minha consulta é maior que 5, é certeza que o SQL Server vai utilizar paralelismo?
A resposta é não. O Query Optimizer verificará se o custo da consulta é maior do que 5 ou se ela tiver um custo superior ao que foi atribuído no 'Cost threshold for parallelism' no sp_configure da instância. Será gerado um plano serial e um em paralelo, em seguida o Query Optimizer analisará qual produziu um custo menor para execução depois de realizar uma otimização completa da consulta, se o plano paralelizado possuir um custo menor o optimizer optará por ele, caso contrário a execução será serial.
 
É atribuído na opção Max degree of Parallelism do sp_configure o valor igual 4. Ao analisar minha consulta na dmv sys.dm_os_waiting_tasks nunca vejo 4 tasks para minha query, hora vejo 8 tasks, hora vejo 16 tasks, porque isso ocorre?
Isso ocorre devido ao paralelismo (DOP). O DOP não remete a quantidade de tarefas em paralelo da consulta como um todo, mas sim ao número de schedulers que será utilizado pela Query. A quantidade de tasks e threads que será utilizada é por operação física e em cada operador do plano de execução, caso ele opte por um DOP plano. Por exemplo, em uma parte do seu plano de execução você acessa um índice especifico e o seu Max Degree of parallelism está com o run_value de 4, isso significa que esse acesso ao índice será uma tarefa dividida/paralelizada em 4, em seguida ele pode acessar uma outra tabela, essa operação também pode ser paralelizada em 4 e assim por diante.
 
Com isso você poderá visualizar na sys.dm_os_waiting_tasks um conjunto de threads muito maior do que você havia atribuído no seu Degree of Parallelism(DOP).
 
Veja o exemplo abaixo como de um plano com o Max degree of Parallism com 4.
 
 
 
https://msdn.microsoft.com/en-us/library/ms175097(v=sql.105).aspx
 
Existe uma fórmula mágica padrão e um número exato Max Degree of Paralelism para eu atribuir para minha instância?
 
Não, não existe. Já vimos casos de ambientes que o Max Degree of Paralelism possui o run_value em 5 e mesmo assim o tempo de sincronização (CXPACKET) não ser satisfatório. Posteriormente, a mesma instância com o valor atribuído para e o tempo de sincronização cair, ficando em um patamar satisfatório e logo após testar o run_value de 3 e voltar a ficar ruim novamente. Portanto, deve ser analisado caso a caso, o SQL Server por padrão é 0, isso significa que ele utilizará o número de schedulers que ele achar mais adequado para uma consulta paralelizada.
 
O paralelismo, por via de regra, não é algo ruim e não dever ser visto como um bicho de sete cabeças que deve ser banido, mas sim como um auxiliador na divisão de um trabalho (query ou índice) em múltiplos processadores.
O que é recomendado para essa configuração é que não se ultrapasse a quantidade de processadores por socket do nó NUMA, ou seja, não ultrapasse a quantidade de processadores que um nó NUMA possui, mas valores inferiores devem ser analisados caso a caso.
 
Geralmente o CXPACKET é um problema, quando ele está entre os wait_type entre os tops na sys.dm_os_wait_stats. Alguns gostam de dizer que o threshold para esse valor deva ser maior que 5% da quantidade total de wait_types em um ambiente OLTP, mas como dito anteriormente, novamente depende.
 
Para saber mais sobre o assunto veja os links abaixo:
 
https://blogs.msdn.com/b/jimmymay/archive/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks.aspx
 
https://technet.microsoft.com/en-us/library/ms178065(v=sql.105).aspx
 
https://msdn.microsoft.com/en-us/library/ms181007.aspx

Alberto Antonio Lima