Настройка максимальной степени параллелизма (MAXDOP) в Базе данных SQL Azure

Область применения: База данных SQL Azure

В этой статье описывается параметр конфигурации максимальной степени параллелизма (MAXDOP) в базе данных SQL Azure.

Примечание

Сведения в этой статье относятся к Базе данных SQL Azure. База данных SQL Azure — это последняя стабильная версия ядра базы данных Microsoft SQL Server, поэтому большая часть изложенной здесь информации об устранении неполадок совпадает, а данные о настройке отличаются. Дополнительные сведения о MAXDOP в SQL Server представлены в статье Настройка параметра максимальной степени параллелизма в конфигурации сервера.

Обзор

MAXDOP управляет параллелизмом внутри запросов в ядре базы данных. Более высокие значения MAXDOP обычно приводят к большему количеству параллельных потоков на запрос и более быстрому выполнению запроса.

В Базе данных SQL Azure для каждой новой отдельной базы данных и базы данных эластичного пула параметр MAXDOP по умолчанию равен 8. Это значение по умолчанию предотвращает ненужное использование ресурсов, в то же время позволяя ядру базы данных выполнять запросы быстрее, используя параллельные потоки. Обычно нет необходимости в дальнейшей настройке MAXDOP в рабочих нагрузках базы данных Azure SQL, хотя это может дать преимущества в качестве расширенного упражнения по настройке производительности.

Примечание

В сентябре 2020 года на основе многолетней телеметрии в службе базы данных Azure SQL MAXDOP 8 был выбран по умолчанию для новых баз данных как оптимальное значение для самых разнообразных рабочих нагрузок клиентов. Это значение по умолчанию помогло предотвратить проблемы с производительностью из-за чрезмерного параллелизма. До этого параметр MAXDOP для новых баз данных имел значение 0 по умолчанию. Параметр MAXDOP не изменялся автоматически для существующих баз данных, созданных до сентября 2020 года.

Обычно ядро СУБД быстрее исполняет запросы с помощью параллелизма. Однако избыточный параллелизм может потреблять дополнительные ресурсы процессора без повышения производительности запросов. В масштабе избыточный параллелизм может отрицательно сказаться на производительности запросов для всех запросов, выполняемых в одном экземпляре ядра СУБД. Традиционно установка верхней границы параллелизма была обычным упражнением по настройке производительности в рабочих нагрузках SQL Server.

В таблице ниже описано, как ядро СУБД выполняет запросы в зависимости от значения MAXDOP.

MAXDOP Поведение
= 1 Ядро базы данных использует один последовательный поток для выполнения запросов. Параллельные потоки не используются.
> 1 Механизм базы данных устанавливает количество дополнительных планировщиков, которые будут использоваться параллельными потоками, равным значению MAXDOP или общему количеству логических процессоров, в зависимости от того, что меньше.
= 0 Механизм базы данных устанавливает количество дополнительных планировщиков, которые будут использоваться параллельными потоками, равным общему количеству логических процессоров или 64, в зависимости от того, что меньше.

Примечание

Каждый запрос выполняется по крайней мере с одним планировщиком и одним рабочим потоком в этом планировщике.

Запрос, выполняемый с параллелизмом, использует дополнительные планировщики и дополнительные параллельные потоки. Поскольку несколько параллельных потоков могут выполняться в одном и том же планировщике, общее количество потоков, используемых для выполнения запроса, может быть выше, чем указанное значение MAXDOP или общее количество логических процессоров. Для получения дополнительной информации см. раздел Планирование параллельных задач.

Рекомендации

  • В Базе данных SQL Azure можно изменить значение MAXDOP по умолчанию:

  • В отношении Базы данных SQL Azure могут применяться долгосрочные рекомендации для SQL Server MAXDOP.

  • Операции по созданию и перестройке индексов, а также по удалению кластеризованного индекса могут оказаться достаточно ресурсоемкими. Вы можете переопределить значение MAXDOP базы данных для операций с индексами, указав параметр индекса MAXDOP в операторе CREATE INDEX или ALTER INDEX. Значение MAXDOP применяется к инструкции во время выполнения и в метаданных индекса не хранится. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

  • В дополнение к запросам и операциям с индексами параметр конфигурации MAXDOP в области базы данных также управляет параллелизмом других операторов, которые могут использовать параллельное выполнение, таких как DBCC CHECKTABLE, DBCC CHECKDB и DBCC CHECKFILEGROUP.

Рекомендации

Изменение параметра MAXDOP для базы данных может оказать значительный отрицательный или положительный эффект на процесс обработки запросов и использование ресурсов. Единого оптимального значения MAXDOP для всех рабочих нагрузок не существует. Рекомендации по настройке MAXDOP содержат множество нюансов и зависят от многих факторов.

Некоторые пиковые одновременные рабочие нагрузки могут лучше обрабатываться с определенным значением MAXDOP. Правильно настроенный MAXDOP должен снизить риск сбоев производительности и доступности, а в некоторых случаях может снизить затраты за счет возможности избежать ненужного использования ресурсов и, таким образом, уменьшить масштаб до более низкой цели обслуживания.

Чрезмерный параллелизм

Высокое значение MAXDOP часто сокращает длительность запросов, интенсивно использующих ЦП. Однако чрезмерный параллелизм может ухудшать производительность других одновременных рабочих нагрузок, поскольку снижается количество других запросов ЦП и ресурсы рабочих потоков. В экстремальных случаях чрезмерный параллелизм может потреблять все ресурсы базы данных или эластичного пула, из-за чего превышается время ожидания запросов, возникают ошибки и простой приложений.

Совет

Мы рекомендуем клиентам избегать установки MAXDOP на 0, даже если в настоящее время это не вызывает проблем.

Чрезмерный параллелизм становится наиболее проблематичным, когда количество одновременных запросов превышает количество ресурсов ЦП и рабочих потоков, предоставляемых целью службы. Старайтесь не использовать значение 0 для параметра MAXDOP, поскольку в будущем из-за этого может возникнуть чрезмерный параллелизм. Это происходит при вертикальном увеличении масштаба базы данных или когда новые конфигурации оборудования для Базы данных Azure SQL имеют больше ядер для прежней цели обслуживания.

Изменение параметра MAXDOP

Если вы определили, что параметр MAXDOP, отличный от значения по умолчанию, является оптимальным для вашей рабочей нагрузки базы данных Azure SQL, вы можете использовать оператор ALTER DATABASE SCOPED CONFIGURATION T-SQL. С примерами можно ознакомиться в разделе Примеры использования Transact-SQL ниже. Чтобы изменить MAXDOP на значение, отличное от значения по умолчанию для каждой новой создаваемой базы данных, добавьте этот шаг в процесс развертывания базы данных.

Если значение MAXDOP, отличное от значения по умолчанию, приносит пользу только небольшому подмножеству запросов в рабочей нагрузке, вы можете переопределить MAXDOP на уровне запроса, добавив подсказку OPTION (MAXDOP). С примерами можно ознакомиться в разделе Примеры использования Transact-SQL ниже.

Тщательно проверьте, как работает система после изменения конфигурации MAXDOP, с помощью нагрузочного тестирования, включающего реалистичное количество одновременных запросов.

MAXDOP для первичной и вторичной реплик можно настроить независимо, если разные параметры MAXDOP оптимальны для ваших рабочих нагрузок чтения-записи и только для чтения. Это относится ко вторичным репликам базы данных Azure SQL с горизонтальным увеличением масштаба для чтения, георепликацией и гипермасштабированием. По умолчанию все вторичные реплики наследуют конфигурацию MAXDOP первичной реплики.

Безопасность

Разрешения

Эта ALTER DATABASE SCOPED CONFIGURATION инструкция должна быть выполнена в администратором сервера, у которого есть роль базы данных db_owner, или пользователем с разрешением ALTER ANY DATABASE SCOPED CONFIGURATION.

Примеры

В этих примерах используется последняя версия образца базы данных AdventureWorksLT. Для новой отдельной Базы данных SQL Azure выбран параметр SAMPLE.

PowerShell

Конфигурация области базы данных для изменения MAXDOP

В этом примере показано, как использовать оператор ALTER DATABASE SCOPED CONFIGURATION для установки конфигурации MAXDOP на 2. Настройка немедленно вступает в силу для новых запросов. Командлет PowerShell Invoke-Sqlcmd выполняет запросы T-SQL для установки и возвращает конфигурацию области базы данных для 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

Этот пример предназначен для использования с базами данных Azure SQL с включенными репликами масштабирования чтения, георепликацией и Azure SQL вторичными репликами с гипермасштабированием базы данных. Первичная реплика получает другое значение MAXDOP по умолчанию, чем вторичная, поскольку у них будут разные рабочие нагрузки только для чтения или для чтения и записи.

$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

Чтобы выполнять запросы T-SQL для Базы данных SQL Azure, можно использовать редактор запросов на портале Azure, SQL Server Management Studio (SSMS) или Azure Data Studio.

  1. Откройте новое окно запроса.

  2. Подключитесь к базе данных, в которой вы хотите изменить MAXDOP. Вы не можете изменить конфигурации области базы данных в базе данных master.

  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.

Конфигурация области базы данных для изменения MAXDOP

В этом примере показано, как определить текущую конфигурацию области базы данных MAXDOP с помощью представления системного каталога sys.database_scoped_configurations.

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

В этом примере показано, как использовать оператор ALTER DATABASE SCOPED CONFIGURATION для установки конфигурации MAXDOP на 8. Этот параметр незамедлительно вступает в силу.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Этот пример предназначен для использования с базами данных SQL Azure с включенными репликами с горизонтальным увеличением масштаба для чтения, георепликацией и гипермасштабируемыми вторичными репликами. К примеру, для первичной реплики установлено значение MAXDOP, отличное от MAXDOP для вторичной реплики, предполагая, что могут быть различия между рабочими нагрузками чтения-записи и только для чтения. Все операторы выполняются на первичной реплике. В столбце value_for_secondary в sys.database_scoped_configurations содержатся настройки для вторичной реплики.

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';

MAXDOP, указание запроса

В этом примере показано, как выполнить запрос с помощью указания, чтобы принудительно изменить параметр max degree of parallelism на 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

MAXDOP, параметр индекса

В этом примере показано, как перестроить индекс с помощью его параметра, чтобы принудительно изменить max degree of parallelism на 12.

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

См. также

Дальнейшие действия