Entre profissionais que trabalham com SQL Server muito se conversa sobre paralelismo e sobre os seus benefícios e malefícios (quando mal utilizado!).
Existem diversas formas de lidar com ele, mas muitas vezes a conversa se resume a configurar o Max Degree of Parallelism (MAXDOP) da instância ou a nível de comando T-SQL através de hints.
A questão é que existem outras formas de lidar com o paralelismo e hoje vou escrever sobre uma configuração a nível de instância que muitas vezes é esquecida, mas que tem uma grande influência sobre ele.
A configuração em questão é a opção “Cost threshold for parallelism” (CTP) que você pode configurar tanto pela GUI como através de T-SQL.
Pela GUI basta acessar as propriedades de servidor, ir na guia advanced e alterar o valor, que por padrão é 5, dentro da sessão “Parallelism”
Cost Threshold for Parallelism
Já através de T-SQL basta utilizar o comando abaixo, onde estou alterando o valor para 10:
EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’cost threshold for parallelism’, N’10′
GO
RECONFIGURE WITH OVERRIDE
GO
Nesse ponto você deve estar se perguntando o que é na verdade esse CTP e qual valor deve utilizar nessa configuração.
Bem, para falarmos de CTP precisamos antes entender alguns conceitos relacionados ao Query Optimizer do SQL Server.
De uma forma BEM RESUMIDA o SQL Server utiliza um modelo baseado no “custo” de cada consulta para definir se a mesma deve ser executada ou não utilizando paralelismo, ou seja, múltiplas threads.
Obviamente isso depende de como está configurado o Max Degree of Parallelism da instância e outros fatores. Para quem quiser maiores detalhes sobre o tema, indico o blog do Fabiano que é especialista no assunto.
Então, caso a sua consulta tenha custo estimado superior ao valor indicado na opção de CTP de sua instância a mesma terá um plano de execução utilizando paralelismo gerado, caso contrário haverá apenas um plano serial.
Ai vem a questão, será que todas as consultas com custo maior que 5 devem realmente executar em paralelo? Será que o valor 5, que é default já há alguns anos no SQL Server, é o valor ideal para utilizarmos? E afinal, devo ter consultas utilizando paralelismo em meu ambiente OLTP?
Não há resposta ideal para nenhuma dessas perguntas, mas podemos aqui iniciar um raciocínio que deve ser adaptado para cada ambiente!
Há uma linha (mais radical na minha opinião) que defende que em ambientes OLTP deve-se evitar paralelismo, pois o mesmo seria voltado a consultas pesadas, normais de ambientes OLAP. Eu sou contra esse raciocínio, pois ele se baseia na ideia, bem antiga, de que o uso de múltiplos núcleos de CPU para uma consulta OLTP é um luxo/desperdício.
Ora, se temos muitas CPUs hoje em dia e as consultas em ambientes OLTP são muito mais “pesadas”, por que não se beneficiar do paralelismo e executar as consultas de forma mais rápida?
Obviamente, devemos monitorar o paralelismo para que não se torne um mal no servidor e permita situações onde uma consulta utiliza todos os núcleos e outras fiquem esperando por CPU, é a velha regra do “nem 8 nem 80″. Ou seja, monitore os waits por CXPACKET e tenha calma antes de assumir que, se 80% dos seus waits são desse tipo você tem um “problema” com o paralelismo.
Já há outra linha de raciocínio que defende que devemos utilizar o paralelismo ao máximo, mas para as consultas que realmente precisam de tal recurso. O problema aqui é, como defino quais consultas precisam realmente de paralelismo?
Aqui não há resposta pronta, vai muito do quanto o DBA conhece seu ambiente e de uma análise do plan cache verificando as consultas que estão executando com paralelismo com as configurações atuais.
Seguindo essa linha de raciocínio uma boa ideia seria analisar o plan cache, ver o padrão das consultas que executam em seu servidor e a partir dai definir um valor de CTP que faça com que consultas relativamente simples não executem com paralelismo, mas sem evitar que consultas mais pesadas possam usufruir de tal recurso.
Aqui um ponto de grande importância é a quantidade de vezes que a consulta é executada dentro de um determinado período de tempo. Não adianta impedir que consultas leves que executam uma vez por mês deixem de executar com paralelismo e ao mesmo tempo impedir que uma consulta pesada que é executada várias vezes também deixe de utilizar tal recurso.
Sendo assim um bom padrão seria elevar o valor do CTP, talvez gradativamente, a um valor que, no seu ambiente, atenda a essas características citadas acima de permitir paralelismo para as consultas mais “pesadas”.
Uma ótima forma de iniciar sua análise seria utilizar o script abaixo, retirado desse ótimo artigo do Jonathan Kehayias. Ele exibe as consultas do plan cache do seu servidor que executaram com paralelismo, o custo da consulta e o número de execuções, entre outras informações.
Vale lembrar que antes de definir um novo valor para o CTP, uma boa opção é analisar os planos de execução das consultas e fazer as devidas otimizações, como criação de índices, por exemplo.
Segue o script:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
SELECT
query_plan AS CompleteQueryPlan,
n.value(‘(@StatementText)[1]‘, ‘VARCHAR(4000)’) AS StatementText,
n.value(‘(@StatementOptmLevel)[1]‘, ‘VARCHAR(25)’) AS StatementOptimizationLevel,
n.value(‘(@StatementSubTreeCost)[1]‘, ‘VARCHAR(128)’) AS StatementSubTreeCost,
n.query(‘.’) AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS qn(n)
WHERE n.query(‘.’).exist(‘//RelOp[@PhysicalOp="Parallelism"]‘) = 1
E você, como costuma lidar com essa configuração em seu servidor? Deixe um comentário!
Referências:
