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

Aplica-se a:Banco de Dados SQL do AzureBanco de Dados SQL no Fabric

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

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

No Banco de Dados SQL do Azure e no Banco de Dados SQL na Malha, a configuração MAXDOP padrão para cada novo banco de dados único e banco de dados de pool 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.

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 excesso de paralelismo pode consumir recursos adicionais do processador sem melhorar o desempenho da consulta. Em escala, o excesso de paralelismo pode afetar negativamente o desempenho da consulta para todas as consultas executadas na mesma instância do mecanismo de banco 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:

Considerations

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

  • Ao nível da consulta, utilizando a dica de consulta MAXDOP.
  • No nível da base de dados, utilizando a configuração de escopo de base 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 e ao Banco de Dados SQL na Malha.

• 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 instrução CREATE INDEX ou 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 CHECKDBe DBCC CHECKFILEGROUP.

Recommendations

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 do que 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 tarefas simultâneas, privando outras consultas de recursos de CPU e fios de execução. 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.

O paralelismo excessivo torna-se mais problemático quando há mais solicitações simultâneas do que as que podem ser suportadas pelos recursos de thread de trabalho e CPU fornecidos pelo objetivo de serviço. Evite MAXDOP 0 para reduzir o risco de potenciais problemas futuros devidos ao paralelismo excessivo, caso um banco de dados seja ampliado ou se futuras configurações de hardware proporcionem mais núcleos para o mesmo objetivo de desempenho do serviço de base de dados.

Modificar o MAXDOP

Se você determinar que uma configuração MAXDOP diferente do padrão é ideal para sua carga de trabalho, poderá usar a ALTER DATABASE SCOPED CONFIGURATION instrução T-SQL. Para obter exemplos, consulte a seção Exemplos usando o 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, pode substituir o MAXDOP ao nível da consulta adicionando a dica OPTION (MAXDOP). Para obter exemplos, consulte Exemplos usando Transact-SQL.

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 da camada de serviço Hyperscale . Por padrão, todas as réplicas secundárias herdam a configuração MAXDOP da réplica primária.

Permissions

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

Examples

Esses exemplos usam o banco de dados de exemplo mais recente AdventureWorksLT quando a SAMPLE opção é escolhida para um novo banco de dados único.

PowerShell

Configuração do escopo do banco de dados MAXDOP

Este exemplo mostra como usar a instrução ALTER DATABASE SCOPED CONFIGURATION para configurar MAXDOP para 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 de 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 escala de leitura habilitadas, réplicas de replicação geográficae réplicas secundárias de Azure SQL Database Hyperscale . Como exemplo, a réplica primária é configurada com um MAXDOP padrão diferente da réplica secundária, antecipando que podem existir diferenças entre as cargas de trabalho de leitura-gravação e 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

Pode usar o editor de consultas do portal Azure para Azure SQL Database, SQL Server Management Studio (SSMS), a extensão MSSQL para Visual Studio Code, ou o editor de consultas SQL no portal Fabric para executar consultas T-SQL.

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 banco de dados master.

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 ilustra como determinar a configuração de escopo atual do banco de dados MAXDOP através da vista 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 configurar MAXDOP para 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áficae réplicas secundárias Hyperscale. Por exemplo, a réplica primária está configurada com 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 coluna value_for_secondary 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 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 reconstruir um índice usando a opção index 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);

Próximo passo

Conteúdo relacionado

• ALTERAR CONFIGURAÇÃO DO ESCOPO DO BANCO DE DADOS (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Configurar operações de índice paralelo
• Dicas de consulta (Transact-SQL)
• Definir opções de índice
• Compreender e resolver problemas de bloqueio