Configure o grau máximo de paralelismo (MAXDOP) na Base de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

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

Nota

Este conteúdo é focado 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 sejam diferentes. Para obter mais informações sobre MAXDOP no SQL Server, consulte Configurar o grau máximo de paralelismo Opção de configuração do servidor.

Descrição geral

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

Na Base de Dados SQL do Azure, a predefinição do MAXDOP para cada nova base de dados individual e base de dados de conjunto elástico é 8. Esse padrão evita a utilização desnecessária de recursos, ao mesmo tempo em que permite que o mecanismo de banco de dados execute consultas mais rapidamente usando threads paralelos. Normalmente, não é necessário configurar ainda mais o MAXDOP em cargas de trabalho do Banco de Dados SQL do Azure, embora ele possa fornecer benefícios como um exercício avançado de ajuste de desempenho.

Nota

Em setembro de 2020, com base em anos de telemetria no serviço Banco de Dados SQL do Azure, o MAXDOP 8 tornou-se o padrão para novos bancos de dados, como o valor ideal para a maior variedade de cargas de trabalho de clientes. Esse padrão ajudou a evitar problemas de desempenho devido ao 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.

Regra geral, se o motor de base de dados optar por executar uma consulta com o paralelismo, o tempo de execução será mais rápido. Todavia, o paralelismo excessivo pode consumir recursos do processador adicionais sem melhorar o desempenho das consultas. Em escala, o paralelismo excessivo pode afetar negativamente o desempenho de todas as consultas em execução na mesma instância do motor de base de dados. Tradicionalmente, definir um limite superior para paralelismo tem sido um exercício comum de ajuste de desempenho 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 MAXDOP diferentes:

MAXDOP Comportamento
= 1 O mecanismo de banco de dados usa um único thread serial para executar consultas. 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.

Nota

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

Uma consulta executada 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 MAXDOP especificado ou o número total de processadores lógicos. Para obter mais informações, consulte Agendando tarefas paralelas.

Considerações

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

    • No nível da consulta, usando a dica de consulta MAXDOP.
    • No nível do banco de dados, usando a configuração de escopo do banco de dados MAXDOP.

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

  • As operações de índice que criam ou recriam um índice, ou que descartam um índice clusterizado, podem consumir 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 CREATE INDEX instrução or ALTER INDEX . O valor MAXDOP é aplicado à instrução em 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 de consultas e operações de índice, a opção de configuração com escopo de banco de dados para MAXDOP também controla o paralelismo de outras instruções que podem usar 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 no desempenho da consulta e na utilização de recursos, tanto positivo quanto negativo. No entanto, não há um único valor MAXDOP que seja ideal para todas as cargas de trabalho. As recomendações para definir o MAXDOP são matizadas e dependem de muitos fatores.

Algumas cargas de trabalho simultâneas de pico podem operar melhor com um MAXDOP diferente de outras. Um MAXDOP configurado corretamente deve reduzir o risco de incidentes de desempenho e disponibilidade e, em alguns casos, pode reduzir os custos ao ser capaz de evitar a utilização desnecessária de recursos e, assim, reduzir para um objetivo de serviço mais baixo.

Paralelismo excessivo

Um MAXDOP mais alto geralmente reduz a duração de consultas com uso intensivo de CPU. No entanto, o paralelismo excessivo pode piorar o desempenho de outras cargas de trabalho simultâneas, privando outras consultas de recursos 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 aplicativos.

Gorjeta

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

O paralelismo excessivo torna-se mais problemático quando há mais solicitações simultâneas do que podem ser suportadas pelos recursos de thread de trabalho e CPU fornecidos pelo objetivo de serviço. Evite o MAXDOP 0 para reduzir o risco de possíveis problemas futuros devido ao paralelismo excessivo se um banco de dados for ampliado 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.

Modificando o 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, poderá usar a ALTER DATABASE SCOPED CONFIGURATION instrução T-SQL. Para obter exemplos, consulte a seção Exemplos usando Transact-SQL abaixo. Para alterar MAXDOP para um valor não padrão para cada novo banco de dados criado, adicione esta etapa ao seu processo de implantação de banco de dados.

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

Teste completamente suas alterações de configuração MAXDOP com testes de carga envolvendo cargas de consulta simultâneas realistas.

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

Segurança

Permissões

A ALTER DATABASE SCOPED CONFIGURATION instrução deve ser executada como administrador do servidor, como membro da função db_ownerde banco de dados ou como usuário ao qual foi concedida a ALTER ANY DATABASE SCOPED CONFIGURATION permissão.

Exemplos

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

PowerShell

Configuração do escopo do banco de dados MAXDOP

Este exemplo mostra como usar a instrução ALTER DATABASE SCOPED CONFIGURATION para definir a MAXDOP configuração como 2. A configuração entra em vigor imediatamente para novas consultas. O cmdlet do PowerShell Invoke-SqlCmd executa as consultas T-SQL para definir e retornar a configuração com escopo do banco de dados MAXDOP.

$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 é para uso com os Bancos de Dados SQL do Azure com réplicas de expansão de leitura habilitadas, replicação geográfica e réplicas secundárias de Hiperescala do Banco de Dados SQL do Azure. Como exemplo, a réplica primária é definida como um MAXDOP padrão diferente como réplica secundária, antecipando que pode haver diferenças entre uma carga de trabalho de leitura-gravação e uma carga de trabalho 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, o SQL Server Management Studio (SSMS) ou o 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 onde você deseja alterar o MAXDOP. Não é possível alterar as configurações do escopo do banco de dados no master banco de dados.

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

Configuração do escopo do banco de dados MAXDOP

Este exemplo mostra como determinar a configuração atual do escopo do banco de dados MAXDOP usando a exibição de 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 MAXDOP configuração como 8. A configuração entra em vigor imediatamente.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Este exemplo é para uso com os Bancos de Dados SQL do Azure com réplicas de expansão de leitura habilitadas, replicação geográfica e réplicas secundárias de hiperescala . Como 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-gravação e somente leitura. Todas as instruções são executadas na réplica primária. A value_for_secondary coluna do sys.database_scoped_configurations contém 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 para 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 reconstruir um índice usando a opção index para forçar o max degree of parallelism to 12.

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

Consulte também

Próximos passos