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 Microsoft Fabric

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

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 Banco de Dados SQL do Azure e no Banco de Dados SQL no Fabric, a configuração maxdop padrão 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 ainda mais o MAXDOP nas 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.

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:

Considerations

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

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

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

• 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 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 a execução paralela, como DBCC CHECKTABLE, DBCC CHECKDBe DBCC CHECKFILEGROUP.

Recommendations

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 de pico podem operar melhor com um MAXDOP diferente de outros. Um MAXDOP configurado corretamente deve reduzir o risco de incidentes de desempenho e disponibilidade e, em alguns casos, pode reduzir os custos, evitando a utilização desnecessária de recursos e, portanto, reduzir para um objetivo de serviço mais baixo.

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.

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 potenciais problemas futuros devido ao paralelismo excessivo se um banco de dados for expandido ou se as configurações futuras de hardware fornecerem mais núcleos para o mesmo objetivo de serviço do banco de dados.

Modificar MAXDOP

Se você determinar que uma configuração MAXDOP diferente do padrão é ideal para sua carga de trabalho, você pode usar a ALTER DATABASE SCOPED CONFIGURATION instrução T-SQL. 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 Exemplos usando Transact-SQL.

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 à escalabilidade de leitura, replicação geográfica e réplicas secundárias da camada de serviço 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.

Permissions

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.

Examples

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

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, prevendo 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 para o Banco de Dados SQL do Azure, o SQL Server Management Studio (SSMS), a extensão MSSQL para Visual Studio Code ou o editor de consultas SQL no portal do Fabric para executar consultas T-SQL.

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, prevendo 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);

Próxima etapa

Conteúdo relacionado

• ALTERAR CONFIGURAÇÃO ESPECÍFICA DO BANCO DE DADOS (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Configurar operações paralelas de índice
• Dicas de consulta (Transact-SQL)
• Definir opções de índice
• Entender e resolver problemas de bloqueios