Boas práticas configurando paralelismo no SQL Server

Já ficou em dúvida sobre as boas práticas ao configurar paralelismo no SQL Server? Nesse post vou tentar explicar o que são NUMA nodes, pra que servem, alguns waits relacionados a paralelismo e o que a Microsoft recomenda ao configurar paralelismo na sua instância SQL Server.

NUMA Nodes: dividir para conquistar

NUMA: Non-Uniform Memory Access. Repare bem o “Non-Uniform”, você já vai entender. Em sistemas de multiprocessamento simétrico (SMP), são utilizados múltiplos cores (CPUs) sob o mesmo sistema computacional para fazer uso de paralelismo. Um dos grandes problemas dessa arquitetura é que todos os CPUs compartilham o mesmo barramento de memória e em algum momento a escalabilidade é prejudicada. Isto é: não adianta você colocar mais cores se o acesso à RAM será um gargalo. Entretanto, as fabricantes perceberam o problema, dividiram esses processadores em “grupos” e deram a cada um deles seu próprio banco de memória.

Imagine um servidor com 16 processadores. Esses 16 processadores podem, por exemplo, ser divididos em grupos de 4 processadores cada. A partir de agora, para simplificar, vou chamar esses grupos apenas de nodes. Para isso, eles comunicam-se através de canais (interconnect) e apesar da preferência de um processador acessar a memória reservada ao seu próprio node, eles podem acessar memória de um outro node (memória remota) a uma latência maior, esse tipo de acesso é chamado de “acesso estrangeiro”. Agora sabemos porque o acesso é chamado de Non Uniform.

E o que isso tem a ver com SQL Server?

A configuração do paralelismo na sua instância está diretamente ligada ao número de NUMA nodes do seu servidor. Em um plano de execução existem diversos operadores, que são como pequenos “programas”. Eles recebem uma entrada, processam e retornam uma saída. Alguns desses “programinhas” são elegíveis para serem executados em paralelo, ou seja, em várias threads, acelerando o processo. Nosso papel, como DBAs, é configurar o SQL Server para que essas threads paralelizadas sejam executadas, de preferência, dentro de um mesmo NUMA node, evitando o acesso estrangeiro, isto é: acessar banco(s) de memória de outro NUMA node. Sendo assim, o SQL Server, por padrão, prefere executar tarefas paralelizadas dentro de schedulers de um mesmo NUMA node.

Soft-NUMA

A partir do SQL Server 2016, quando a instância é iniciada, é feita uma contagem da quantidade de cores físicos por NUMA Node. Se esse número for maior que 8, será feita o soft-NUMA que é uma divisão lógica dos NUMA Nodes físicos. Nesse caso não existe divisão do barramento de memória. Ou seja, soft-NUMA nodes compartilharão o memory bus do NUMA node “pai”. Você vai ver uma mensagem como essa no log:

Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

MAXDOP

A Microsoft tem um KB bem completo sobre como o configurar o MAXDOP na sua instância, que vai depender no número de NUMA nodes e do número de processadores lógicos por NUMA node. É avisado, mas é legal ressaltar que o número de NUMA nodes deve levar em consideração os soft-NUMA nodes criados pelo SQL Server 2016 (130) e versões acima. Ou seja, consulte a view sys.dm_os_nodes e desconsidere o node reservado ao DAC.

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

Recomendações p/ SQL Server 2016 e versões acima.

Por exemplo, um servidor com SQL Server 2016 que tenha 4 NUMA nodes com 4 processadores lógicos em cada node, ficará com o MAXDOP em 4. Já um servidor com 2 NUMA nodes de 24 processadores lógicos cada, ficará com o MAXDOP em 12.

Cost threshold for parallelism: para o barato não sair caro.

Nem toda query deve ser executada em paralelo. Criar uma thread para gerenciar/coordenar outras threads, juntar os trabalhos e entregar seu resultado pode ser muito mais custoso do que simplesmente executar a tarefa com uma thread apenas. Para resolver este problema existe uma configuração a nível de instância chamada “cost threshold for parallelism”, onde é definido um threshold de custo para que a partir dele o Query Optimizer saiba quando deve ou não considerar um plano de execução utilizando paralelismo. Aqui eu destaquei a palavra “considerar” pois nem sempre um plano acima do threshold vai utilizar paralelismo.


Para entendermos melhor: o query optimizer possui 3 fases de otimização, mas é na segunda fase (search 1) em que a mágica acontece e o Query Optimizer, dentre várias outras opções de plano, pode considerar um plano de execução usando paralelismo se o melhor plano encontrado até então, tiver custo acima desse threshold. As instâncias por padrão vem com essa configuração em 5, o que é muito baixo para a tecnologia de processamento atual, já que esse valor foi definido há muitos anos atrás. Geralmente configuramos esse número em algo perto de 50, vamos observando e ajustando conforme necessário.

Configurações de MAXDOP e Cost Threshold for Parallelism

Tomem cuidado pois alterar essas configurações em uma instância fará com que o plan cache seja limpo. Aliás, mesmo que você na prática não altere o valor da configuração, mas rode o comando RECONFIGURE, para algumas configurações, o plan cache será resetado. Por exemplo, se a instância já estiver configurada com o cost threshold for parallelism em 50 e você rodar o statement abaixo:

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
RECONFIGURE

CXPACKET: inocente até que se prove o contrário.

Antes de mais nada, vamos entender porque acontece o wait CXPACKET e sanar algumas dúvidas. Há muito desentendimento sobre o assunto e já vi inclusive recomendações falando que pra esse wait sumir, devemos desabilitar o paralelismo configurando o MAXDOP da instância p/ 1 (!), o que é simplesmente absurdo.

Vejamos: o wait CXPACKET (Class eXchange packet) acontece sempre que uma thread espera o resultado de outra(s) thread(s) para que os resultados sejam agregados. Ou seja, se 4 threads executam a mesma tarefa em paralelo e uma delas termina antes, essa thread vai gerar o wait CXPACKET. Vamos a um hipotético exemplo: um operador de Index Scan deve ler cerca de 100 mil linhas. O MAXDOP da sua instância está em 4 e por conta disso, o SQL Server provisiona 4 threads para realizar esse trabalho (ler 100 mil linhas) e mais uma thread para controlar o trabalho, a thread 0. Desta maneira, digamos que o SQL Server faça o seguinte trabalho da seguinte forma:

  • Thread 0 – Vai controlar o trabalho e “juntar” os resultados.
  • Thread 1 – Leu 25 mil linhas
  • Thread 2 – Leu 25 mil linhas
  • Thread 3 – Leu 25 mil linhas.
  • Thread 4 – Leu 25 mil linhas.

Percebam que esse é um cenário ideal de paralelismo, onde todas as threads têm a mesma quantidade de trabalho, porém mesmo nesse cenário, a thread 0 (thread de controle) vai gerar o wait CXPACKET. Foi algo ruim? Não, pois a query provavelmente demoraria muito mais se rodasse sem paralelismo. Entretanto, mesmo neste cenário, o wait CXPACKET estará presente.

Isso quer dizer que o CXPACKET nunca deve ser analisado? De forma alguma. O que quis dizer é que nem todo caso é um problema. Agora percebam o seguinte cenário onde temos um workload distorcido. O que aconteceria seria o seguinte:

  • Thread 0 – Vai controlar o trabalho e “juntar” os resultados.
  • Thread 1 – Leu 85 mil linhas
  • Thread 2 – Leu 5 mil linhas
  • Thread 3 – Leu 5 mil linhas.
  • Thread 4 – Leu 5 mil linhas.

3 threads leram somente 5 mil linhas, terminaram seu trabalho bem antes e ficaram esperando a thread 1 terminar. Durante este tempo, as 3 threads sinalizaram o wait CXPACKET, além da thread 0, que durante toda a execução coordenou o trabalho. Perceba que neste cenário, o wait de CXPACKET será a soma dos waits de todas as threads e esse valor será muito maior que no primeiro caso onde tínhamos um workload balanceado. Um workload distorcido pode acontecer quando temos estatísticas desatualizadas e o query optimizer não estima o trabalho de forma adequada.

Para ver essas estatísticas em um plano de execução real, é só clicar com o botão direito no operador e ir na opção “Properties”, ao lado direito deverá aparecer uma janela com as estatísticas de execução daquele plano.

O que quero mostrar aqui, é que o wait CXPACKET deve ser analisado caso a caso. O paralelismo, se bem configurado e utilizado da maneira correta, vai ajudar e muito. Desabilitar o paralelismo (MAXDOP 1) não vai resolver nada e aquelas queries demoradas (que rodavam usando paralelismo), vão demorar muito mais. Ou seja, você arranjou outro problema.

CXCONSUMER

A partir do SQL Server 2016 (130) a Microsoft introduziu esse wait para sinalizar a espera das threads que estavam consumindo o resultado das threads que produziam. Inclusive disseram que esse wait era “benigno” e que não devíamos nos preocupar. Era só filtrar ele da sys.dm_os_wait_stats e analisar somente os waits de CXPACKET, que nesse caso, se tornariam mais significativos. Maaas… já vimos que não é bem assim. Aqui vai um post do Erik Darling sobre o assunto:

https://www.brentozar.com/archive/2018/07/cxconsumer-is-harmless-not-so-fast-tiger/

Conclusão

Neste post vimos alguns conceitos sobre paralelismo no SQL Server, sabemos o que é NUMA. vimos como devemos configurar o MAXDOP da instância, entendemos como funciona o cost threshold for parallelism e desvendamos alguns mitos sobre waits relacionados a paralelismo (CXPACKET e CXCONSUMER)

Gostou do post? Quer dar alguma sugestão ou ficou com alguma dúvida? Fique a vontade para deixar um comentário ou me adicione no LinkedIn.

Abraços!

Post criado 10

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Posts Relacionados

Comece a digitar sua pesquisa acima e pressione Enter para pesquisar. Pressione ESC para cancelar.

De volta ao topo