Bom dia pessoal.
Durante o último treinamento de internals do SQL Server 2008 que ministrei, acabei pensando em um artigo interessante que pode mostrar como a falta de conhecimento do funcionamento do SQL Server pode impactar o seu ambiente. Hoje vamos falar do affinity mask.
Como vocês já sabem, o affinity mask define quais processadores o SQL Server vai utilizar, então se você possui oito processadores lógicos com o affinity mask definido para 85 (01010101 em binário), está indicando para o SQL Server utilizar o primeiro, terceiro, quinto e sétimo processadores. Por padrão o SQL Server configura o affinity mask como zero (“0”), o que indica que ele pode utilizar todos os processadores.
Com base nas afirmações acima, podemos tirar uma conclusão básica para minha máquina, que possui dois processadores (um slot dual-core): affinity mask = 0 é a mesma coisa que affinity mask 3 (11 em binário), pois ambos indicam que todos os processadores serão utilizados, certo?
Bem, infelizmente a afirmação acima não é totalmente verdadeira, o SQL Server vai utilizar os dois núcleos que tenho disponível, mas seu comportamento é diferente. Quer ver?
SQLOS basicsSabemos que o SQL Server 2005 (e 2008) é NUMA aware (SQL Server 2000 com SP4 também? Hhhuuumm, bem +/-), o que significa que ele identifica os nós NUMA na sua máquina (hard ou soft) e associa um número X de schedulers a cada um dos nós. Sabendo que os schedulers não migram entre nós e os workers (threads ou fibers – aqui indiferente) estão vinculados a somente um scheduler, o SQLOS tenta balancear a carga entre schedulers através do load factor.
Mas e se, por acaso do destino, um scheduler receber alguns workers que estão executando tarefas mais pesadas e demoradas, haverá um desbalanceamento de uso da CPUs dentro do mesmo nó NUMA? A resposta é não, por padrão um scheduler poderá escolher qual CPU vai utilizar para colocar o worker para trabalhar, deixando aberto um grau de dinamismo entre CPU/scheduler, já que o worker está vinculado ao scheduler.
É claro que o SQL Server somente permite que CPUs do mesmo nó sejam utilizadas por um scheduler, evitando que o ganho pela localidade dos dados na memória do nó seja perdido com a alocação de uma CPU em um nó remoto. Quando falamos em paralelismo isso é factível de acontecer (novos workers em nós diferentes), mas aí com o particionamento dos dados pelo paralelismo você pode ganhar (ao invés de perder) usando as características de uma arquitetura NUMA.
O comportamento que citei acima somente pode acontecer quando o affinity mask está definido como zero. Isto é, se você especificar um affinity mask qualquer, mesmo que indique que todos os processadores serão utilizados, o SQLOS vai entender que um scheduler está vinculado a uma CPU, matando esse pequeno grau de dinamismo e somente confiando no load factor do scheduler.
DemonstraçãoVamos a um exemplo simples. Minha máquina possui somente um nó (sys.dm_os_nodes) e dois schedulers (sys.dm_os_schedulers) que são utilizados para as requisições que chegam (deixe de lado hidden schedulers e o reservado para o DAC).
Com affinity mask = 0 eu começo a monitorar o contador “% processor time” (ou “% tempo do processador”) para os dois núcleos e vejo que minha máquina está trabalhando com uma média de 20% (cada um), mas quando eu abro uma nova conexão e executo o script abaixo (script 01) vejo que a média de utilização dos dois contadores sobe para uns 70%.
(Script 01)
DECLARE @I BIGINT = 0
DECLARE @S varchar(200)
WHILE @I < 100000000
BEGIN
select @S = @@VERSION
SET @I += 1
END
O que está acontecendo? A requisição (veja em sys.dm_exec_requests) recebeu um worker e está vinculada a um scheduler, mas esse por sua vez utiliza os dois processadores (em quantuns diferentes) para executar a tarefa, resultado em um uso equilibrado dos núcleos. O número resultante para o contador bate bem em cima do esperado, com um aumento de 50% para cada um.
Vamos mudar um pouco o cenário? Altero o affinity mask para três, indicando que o SQL Server também pode usar os dois núcleos disponíveis. Como resultado, temos a figura 01.
exec sp_configure 'affinity mask', 3
reconfigure
Veja que a diferença entre os dois momentos é clara (figura 01), em um primeiro momento a carga de 100% ficou dividida entre os núcleos, enquanto no segundo momento o SQL Server vinculou o scheduler a somente um núcleo, fazendo com que esse fique constantemente em 100% enquanto o outro fica com aproximadamente 20%.
(Figura 01)
Com isso eu demonstro meu ponto, affinity mask = 0 é diferente de affinity mask = 0xffffff (isto é, usando todos os núcleos), pois no segundo caso o SQL Server mantém uma afinidade entre scheduler e CPU.
O que isso significa para sua empresa? Talvez você não sinta um impacto direto dessa configuração, mas podemos supor um cenário potencial onde, mesmo o SQLOS usando o load factor dos schedulers para definir onde uma tarefa será executada, é factível termos tarefas pesadas caindo sobre um mesmo núcleo, demorando mais e usando de forma desigual os recursos de processamento disponíveis.
Se você quer que o SQL Server trabalhe com processadores específicos (affinity mask diferente de zero), mas não deseja que essa afinidade entre CPU/Scheduler seja respeitada, existe luz no fim do túnel, basta habilitar o trace flag 8002. De onde tirei isso? Leia a série inside SQL Server.
Gostou? Viu como um detalhe simples pode causar um eventual impacto, não reproduzível facilmente, no seu ambiente? Então entenda como o SQL Server funciona para não ser surpreendido...
Prefere ler o PDF deste artigo? Pegue no skydrive.
[]s
Luciano Caixeta Moreira - {Luti Nimbus}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
E-mail: luciano.moreira@srnimbus.com.br
