«

»

maio 29

Cost threshold for parallelism

Hoje vamos falar sobre uma opção importante mas cheia de controversas. Você sabe o que é paralelismo?

O SQL tem a capacidade de quebrar a query de forma a utilizar todas as CPUs lógicas (como em processadores HT, vários processadores físicos ou que possuem múltiplos núcleos em cada processador físico). Desta forma, tarefas são executadas simultaneamente trazendo o resultado final mais rapidamente, em teoria. Esta opção garante que, ao se criar o Execution Plan, caso o custo (valor interno do SQL) seja superior a 5, a query é quebrada para que todas as CPUs sejam utilizadas e após o processamento estas partes quebradas são unidas novamente. Porém, esta opção tem o valor 5 por default desde pelo menos o SQL2000 e nesta época não tínhamos com tanta facilidade servidores com 4, 8, até 64 núcleos como temos hoje em dia. Partindo deste princípio, entendemos que esta opção precisa obrigatoriamente ser ajustada.

Esta teoria possui diversas variantes e não é uma receita certa. Este trabalho de quebrar a query e juntar os resultados também possui um custo e você pode perder performance ao invés de ganhar diminuindo esta configuração para algo menor que 5. Segundo o Brent Ozar, devido a grande capacidade computacional dos servidores de hoje em dia, estes valores deveriam beirar entre 50 e 100. A boa notícia é que você pode fazer alguns testes e caso perca performance ao invés de ganhar, basta voltar a utilizar o valor default.

Se você não puder fazer estes testes no servidor em produção, uma boa forma de testar é utilizar o MAXDOP em sua query, como no exemplo abaixo:

use AdventureWorks2008R2
GO

select * from sales.SalesOrderDetail
order by LineTotal DESC
OPTION (MAXDOP 1)
GO 

No exemplo acima, estamos dizendo que somente um núcleo de CPU pode ser utilizado e podemos comparar a mesma query com o MAXDOP igual a 1 (sem paralelismo) ou com o MAXDOP igual a quantidade de núcleos que você dispõe (com paralelismo).

use AdventureWorks2008R2
GO

select * from sales.SalesOrderDetail

order by LineTotal DESC
OPTION (MAXDOP 16)
GO

Devemos lembrar que o cost threshold for parallelism é uma opção avançada, portanto, o parâmetro ‘show advanced config’ deve estar com o valor 1.

–para exibir as opções avançadas
sp_configure ‘show advanced config’ , 1

reconfigure

Para alterar esta opção não poderia ser diferente:

–para setar seu valor em 3 
sp_configure ‘cost threshold for parallelism’ , 3

reconfigure

É possível alterá-la também pela interfáce gráfica, onde vamos alterar de volta para 5:

paralelismo
Agora, até mesmo as querys com custo de execução 5 serão quebradas e executadas em paralelo.

É importante salientar que o valor do Cost Threshold for Parallelism é baseado no custo da query e não em segundos como até eu já pensei um dia.

Outro fator importante é que alterar o Cost Threshold for Parallelism vai fazer com que os planos de execução que estão no cache não sirvam mais, já que uma query pequena que estava sendo quebrada pode passar a ser executada em apenas um core. Portanto, procure não fazer em produção e perceba que alguma lentidão após a alteração pode ser resultado do cache de plano de execuções sendo repopulado.

Já o MAXDOP não deve utilizar todos os cores do processador. Para servidores com 8 cores, teste o MAXDOP em 2 e 4 e veja qual resultado é melhor. Servidores com 16 cores, teste com 4 e 6 cores e faça testes com 6 ou 8 cores em servidores com 24 ou mais cores.

 

 

 .

Deixe uma resposta