Какова цель использования OPTION (MAXDOP 1) в SQL Server?
Я никогда не понимал, как правильно использовать MAXDOP. Я знаю, что это ускоряет выполнение запроса и что это последний элемент, который я могу использовать для оптимизации запросов.
Однако у меня вопрос: когда и где он лучше всего подходит для использования в запросе?
Ответы 5
Это общая бессвязная беседа о параллелизме в SQL Server, она может не дать прямого ответа на ваш вопрос.
Из электронной книги на MAXDOP:
Sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.
См. Блог Рики Ли о параллелизме и типе ожидания CXPACKET. Это довольно интересно.
Как правило, в базе данных OLTP я считаю, что если запрос настолько дорогостоящий, что его нужно выполнять на нескольких процессорах, запрос необходимо переписать во что-то более эффективное.
Почему вы получаете лучшие результаты, добавляя MAXDOP (1)? Трудно сказать без фактических планов выполнения, но это может быть так просто, как если бы план выполнения был совершенно другим, чем без OPTION, например, с использованием другого индекса (или, что более вероятно), JOIN по-другому, с использованием MERGE или HASH объединений.
Как упоминал Кабоинг, MAXDOP(n) фактически контролирует количество ядер ЦП, которые используются в обработчике запросов.
В полностью простаивающей системе SQL Server будет пытаться как можно быстрее извлечь таблицы в память и соединить их в памяти. Возможно, в вашем случае лучше всего сделать это с одним процессором. Это может иметь тот же эффект, что и использование OPTION (FORCE ORDER), который заставляет оптимизатор запросов использовать указанный вами порядок объединений. В некоторых случаях я видел, как OPTION (FORCE PLAN) сокращает время выполнения запроса с 26 до 1 секунды.
В электронной документации далее говорится, что возможные значения для MAXDOP:
0 - Uses the actual number of available CPUs depending on the current system workload. This is the default value and recommended setting.
1 - Suppresses parallel plan generation. The operation will be executed serially.
2-64 - Limits the number of processors to the specified value. Fewer processors may be used depending on the current workload. If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used.
Я не уверен, что лучше всего использовать MAXDOP, однако я бы предположил и сказал, что если у вас есть таблица с 8 разделами на ней, вы хотели бы указать MAXDOP(8) из-за ограничений ввода-вывода, но я мог бы неправильный.
Вот несколько быстрых ссылок, которые я нашел о MAXDOP:
В SQL-сервере есть пара ошибок параллелизма с неправильным вводом. ВАРИАНТ (MAXDOP 1) обойдет их стороной.
Обновлено: Старый. Мое тестирование проводилось в основном на SQL 2005. Большинство из них, похоже, больше не существует, но время от времени мы ставим под сомнение предположение, когда SQL 2014 делает что-то глупое, и мы возвращаемся к старому способу, и он работает. Нам так и не удалось продемонстрировать, что это была не просто генерация плохого плана в более поздних случаях, хотя можно положиться на SQL-сервер, чтобы использовать старый способ в новых версиях. Поскольку все случаи были связаны с запросами ввода-вывода, MAXDOP 1 не повредит.
Не могли бы вы подробнее рассказать об этих ошибках?
Я не смог полностью квалифицировать ошибки, но в частности одну: когда ожидалось, что левое соединение будет соответствовать очень немногим% строк, будет пытаться буферизовать обе таблицы и соединение цикла, а не поиск по закладкам, только при включенном распараллеливании.
@Joshua, вы знаете, актуальны ли еще ошибки для SQL 2012, 14 или 16 версии?
@HappyTown Я ожидал, что ошибки исчезнут или, по крайней мере, изменились. В каждой основной версии SQL Server в оптимизатор запросов вносились существенные изменения. Кроме того, SQL Server 2014 получил новую оценку мощности (которая также значительно улучшилась в 2016 году). Также не совсем справедливо называть их ошибками, поскольку это не похоже на изменение результатов запроса - это могут быть ошибки оптимизации, но это не ошибки в том смысле, что ваши результаты будут плохими.
В качестве некоторого отступления, MAXDOP, по-видимому, можно использовать как обходной путь для потенциально неприятной ошибки:
Возвращаемые значения идентичности не всегда верны
ссылка не работает - зеркало
Добавляю свои два цента, исходя из наблюдаемой мной проблемы с производительностью.
Если простые запросы без надобности парелелизируются, это может принести больше проблем, чем их решение. Однако перед добавлением MAXDOP в запрос в качестве исправления «коленного рефлекса» необходимо проверить некоторые настройки сервера.
В Иеремия Пешка - Пять настроек SQL Server, которые нужно изменить MAXDOP и «ПОРОГ СТОИМОСТИ ДЛЯ ПАРАЛЛЕЛИЗМА» (CTFP) упоминаются как важные настройки, которые необходимо проверить.
Примечание: Пол Уайт упомянул max server memory также как параметр для проверки в ответе на Проблема с производительностью после миграции с SQL Server 2005 на 2012. Хорошая статья в kb для чтения - Использование большого количества памяти может привести к неэффективному плану в SQL Server.
Джонатан Кехайас - Настройка «порога стоимости параллелизма» из Plan Cache помогает найти хорошее значение для CTFP.
Почему игнорируется порог стоимости параллелизма?
Аарон Бертран - Шесть причин, по которым вам следует опасаться параллелизма обсуждает некоторый сценарий, в котором MAXDOP является решением.
Компоненты, запрещающие параллелизм, упомянуты в Пол Уайт - Форсирование плана выполнения параллельного запроса
Вы должны включить версию и пакет обновления SQL-сервера. Возможно, это было исправлено в более поздней версии ...