Configurar o MAXDOP (grau máximo de paralelismo) no Banco de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Este artigo descreve a definição de configuração MAXDOP (grau máximo de paralelismo) no Banco de Dados SQL do Azure.

Observação

Este conteúdo está concentrado no Banco de Dados SQL do Azure. O banco de dados SQL do Azure é baseado na versão estável mais recente do mecanismo de banco de dados do Microsoft SQL Server, portanto, grande parte do conteúdo é semelhante, embora as opções de solução de problemas e configuração possam ser diferentes. Para saber mais sobre MAXDOP no SQL Server, confira Definir a Opção de Configuração do Servidor de grau máximo de paralelismo.

Visão geral

O MAXDOP controla o paralelismo intraconsulta no mecanismo de banco de dados. Valores mais altos de MAXDOP geralmente resultam em mais threads paralelos por consulta e execução de consulta mais rápida.

No entanto, no Banco de Dados do SQL do Azure, a configuração padrão MAXDOP para cada novo banco de dados individual e banco de dados de pool elástico é 8. Esse padrão impede a utilização desnecessária de recursos, enquanto ainda permite que o mecanismo de banco de dados execute consultas mais rapidamente usando threads paralelos. Normalmente, não é necessário configurar o MAXDOP em cargas de trabalho do Banco de Dados SQL do Azure, embora possa fornecer benefícios como um exercício avançado de ajuste de desempenho.

Observação

Em setembro de 2020, com base em anos de telemetria no serviço de Banco de Dados SQL do Azure o MAXDOP 8 tornou-se o padrão para novos bancos, como o valor ideal para a maior variedade de cargas de trabalho do cliente. Esse padrão ajudou a evitar problemas de desempenho por paralelismo excessivo. Antes disso, a configuração padrão para novos bancos de dados era MAXDOP 0. O MAXDOP não foi alterado automaticamente para bancos de dados existentes criados antes de setembro de 2020.

Em geral, se o mecanismo de banco de dados optar por executar uma consulta usando paralelismo, o tempo de execução será mais rápido. No entanto, o paralelismo excessivo pode consumir recursos adicionais do processador sem melhorar o desempenho da consulta. Em escala, o paralelismo excessivo pode afetar negativamente o desempenho da consulta para todas as consultas em execução na mesma instância do mecanismo de banco de dados. Tradicionalmente, a definição de um limite superior para paralelismo tem sido um exercício de ajuste de desempenho comum em cargas de trabalho do SQL Server.

A tabela a seguir descreve o comportamento do mecanismo de banco de dados ao executar consultas com valores de MAXDOP diferentes:

MAXDOP Comportamento
= 1 O mecanismo de banco de dados usa um único thread serial para executar consultas. Os threads paralelos não são usados.
> 1 O mecanismo de banco de dados define o número de agendadores adicionais a serem usados por threads paralelos para o valor MAXDOP, ou o número total de processadores lógicos, o que for menor.
= 0 O mecanismo de banco de dados define o número de agendadores adicionais a serem usados por threads paralelos para o número total de processadores lógicos, ou 64, o que for menor.

Observação

Cada consulta é executada com pelo menos um Agendador e um thread de trabalho nesse Agendador.

Uma consulta em execução com paralelismo usa agendadores adicionais e threads paralelos adicionais. Como vários threads paralelos podem ser executados no mesmo Agendador, o número total de threads usados para executar uma consulta pode ser maior do que o valor de MAXDOP especificado ou o número total de processadores lógicos. Para obter mais informações, consulte Agendamento de pacotes.

Considerações

  • No Banco de Dados SQL do Azure, você pode alterar o valor padrão de MAXDOP:

  • Considerações e recomendações do MAXDOP do SQL Server de longa duração são aplicáveis ao Banco de Dados SQL do Azure.

  • As operações de índice que criam ou reconstroem um índice ou descartam um índice clusterizado podem usar muitos recursos. Você pode substituir o valor MAXDOP do banco de dados para operações de índice, especificando a opção de índice MAXDOP na instrução CREATE INDEX ou ALTER INDEX. O valor MAXDOP é aplicado à instrução no tempo de execução e não é armazenado nos metadados do índice. Para obter mais informações, consulte Configurar operações de índice paralelo.

  • Além das consultas e das operações de índice, a opção de configuração no escopo do banco de dados para MAXDOP também controla o paralelismo de outras instruções que podem usar a execução paralela, como DBCC CHECKtable, DBCC CHECKDB e DBCC CHECKFILEGROUP.

Recomendações

Alterar o MAXDOP para o banco de dados pode ter um grande impacto positivo ou negativo no desempenho da consulta e na utilização de recursos. No entanto, não há um valor de MAXDOP único ideal para todas as cargas de trabalho. As recomendações para definir MAXDOP são sutis e dependem de muitos fatores.

Algumas cargas de trabalho simultâneas máximas podem funcionar melhor com um MAXDOP diferente. Um MAXDOP configurado corretamente deve reduzir o risco de incidentes de desempenho e disponibilidade e, em alguns casos, pode reduzir os custos ao evitar a utilização desnecessária de recursos e, portanto, escalar verticalmente para um objetivo de serviço inferior.

Paralelismo excessivo

Um MAXDOP maior geralmente reduz a duração de consultas com uso intensivo de CPU. No entanto, o paralelismo excessivo pode piorar outro desempenho de carga de trabalho simultâneo, privando recursos de outras consultas de CPU e thread de trabalho. Em casos extremos, o paralelismo excessivo pode consumir todos os recursos do banco de dados ou do pool elástico, causando tempos limite de consulta, erros e interrupções de aplicativo.

Dica

Recomendamos que os clientes evitem definir MAXDOP como 0, mesmo que ele não pareça causar problemas no momento.

O paralelismo excessivo se torna mais problemático quando há excesso de solicitações simultâneas para os recursos de CPU e thread de trabalho fornecidos pelo objetivo de serviço. Evite MAXDOP 0 para reduzir o risco de possíveis problemas futuros devido ao paralelismo excessivo, se um banco de dados for expandido ou se futuras configurações de hardware no Banco de Dados SQL do Azure fornecerem mais núcleos para o mesmo objetivo de serviço de banco de dados.

Modificação do MAXDOP

Se você determinar que uma configuração MAXDOP diferente do padrão é ideal para sua carga de trabalho do Banco de Dados SQL do Azure, você pode usar a instrução T-SQL ALTER DATABASE SCOPED CONFIGURATION. Para obter exemplos, consulte a seção Exemplos com o Transact-SQL abaixo. Para alterar MAXDOP para um valor não padrão para cada novo banco de dados que você criar, adicione essa etapa ao processo de implantação do banco de dados.

Se MAXDOP não padrão beneficiar apenas um pequeno subconjunto de consultas na carga de trabalho, você poderá substituir MAXDOP no nível de consulta adicionando a dica OPTION (MAXDOP). Para obter exemplos, consulte a seção Exemplos com o Transact-SQL abaixo.

Teste exaustivamente as alterações de configuração do MAXDOP com testes de carga que envolvem cargas de consulta simultâneas reais.

O MAXDOP para as réplicas primárias e secundárias pode ser configurado independentemente se configurações de MAXDOP diferentes forem ideais para suas cargas de trabalho de leitura/gravação e somente leitura. Isso se aplica à expansão de leitura, replicação geográficae réplicas secundárias de hiperescala do Banco de Dados SQL do Azure. Por padrão, todas as réplicas secundárias herdam a configuração MAXDOP da réplica primária.

Segurança

Permissões

A instrução ALTER DATABASE SCOPED CONFIGURATION deve ser executada como o administrador do servidor, como um membro da função de banco de dados db_owner ou um usuário que tenha recebido a permissão ALTER ANY DATABASE SCOPED CONFIGURATION.

Exemplos

Esses exemplos usam o banco de dados de exemplo AdventureWorksLT mais recente quando a opção SAMPLE é escolhida para um novo Banco de Dados individual SQL do Azure.

PowerShell

database scoped configuration do MAXDOP

Este exemplo mostra como usar a instrução ALTER DATABASE SCOPED CONFIGURATION para definir a configuração MAXDOP como 2. A configuração é válida imediatamente para novas consultas. O cmdlet Invoke-SqlCmd do PowerShell executa as consultas T-SQL a serem definidas e retorna a MAXDOP database scoped configuration.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP = 8

$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP + ';
     SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Este exemplo se destina ao uso com os bancos de dados SQL do Azure com as réplicas de expansão de leitura habilitadas, a replicação geográficae as réplicas secundárias da Hiperescala do Banco de Dados SQL do Azure. Por exemplo, a réplica primária é definida como um MAXDOP padrão diferente da réplica secundária, antecipando que pode haver diferenças entre uma carga de trabalho de leitura e gravação e uma de somente leitura.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP_primary = 8
$desiredMAXDOP_secondary_readonly = 1
 
$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP_primary + ';
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = ' + $desiredMAXDOP_secondary_readonly + ';
    SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Transact-SQL

Você pode usar o Editor de consultas do portal do Azure, SSMS (SQL Server Management Studio) ou Azure Data Studio para executar consultas T-SQL em seu Banco de Dados SQL do Azure.

  1. Abra uma nova janela de consulta.

  2. Conecte-se ao banco de dados no qual você deseja alterar o MAXDOP. Não é possível alterar as configurações de escopo do banco de dados master.

  3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar.

database scoped configuration do MAXDOP

Este exemplo mostra como determinar a configuração no escopo do banco de dados MAXDOP do banco de dados atual usando a exibição do catálogo do sistema sys.database_scoped_configurations.

SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

Este exemplo mostra como usar a instrução ALTER DATABASE SCOPED CONFIGURATION para definir a configuração MAXDOP como 8. A configuração entra em vigor imediatamente.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Este exemplo é para uso com bancos de dados SQL do Azure com réplicas de expansão de leitura habilitadas, replicação geográficae réplicas secundárias de hiperescala. Por exemplo, a réplica primária é definida como um MAXDOP diferente da réplica secundária, antecipando que pode haver diferenças entre as cargas de trabalho de leitura e gravação e de somente leitura. Todas as instruções são executadas na réplica primária. A coluna value_for_secondary de sys.database_scoped_configurations contém as configurações para a réplica secundária.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;
SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

dica de consulta MAXDOP

Este exemplo mostra como executar uma consulta usando a dica de consulta para forçar o max degree of parallelism a 2.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM SalesLT.SalesOrderDetail  
WHERE UnitPrice < 5  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

opção de índice MAXDOP

Este exemplo mostra como recriar um índice usando a dica de consulta para forçar o max degree of parallelism a 12.

ALTER INDEX ALL ON SalesLT.SalesOrderDetail 
REBUILD WITH 
   (     MAXDOP = 12
       , SORT_IN_TEMPDB = ON
       , ONLINE = ON);

Consulte também

Próximas etapas