Optsiya Maximum Degree of Parallelism

Не секрет, что обдумывая проблемы конфигурирования  SQL сервера, связанные с увеличением производительности, IT-специалисты в своем большинстве, делают выбор в пользу увеличения аппаратных средств. Но всегда ли это оправдано? Все ли методы настройки сервера при этом уже использованы?  Известно, что работа с  параметрами конфигурации и изменение их значений по умолчанию, способна улучшить производительность и другие характеристики данной системы. Среди этих опций конфигурации  SQL есть одна опция,  с которой связано много вопросов, это опция -  Max degree of parallelism (DOP) -  вот о ней и поговорим.

Опция Maximum Degree of Parallelism (DOP) определяет число  потоков, на которые SQL Server может распараллелить запрос  и означает число используемых процессоров сервера.  Параметр этот умолчанию имеет значение 0 – максимальную степень параллелизма. Например, если вы имеете 24 ядра – тогда  значение ‘max degree of parallelism’ будет равно 24 и оптимизатор, если он посчитает нужным, может задействовать все процессоры на выполнение одной инструкции, то есть  запрос будет распараллелен на 24 потока.  Для большинства случаев это хорошо, но не для всех. Также, далеко не всегда хорошо, использование значение этого параметра по умолчанию. Конфигурирование этого параметра может быть нужно, например, в следующей ситуации: допустим, у нас есть приложение, в которое все сотрудники вносят информацию о ежедневных операциях, и,  в определенный промежуток времени каждый из пользователей выполняет запрос, который строит отчет обо всех операциях пользователя за некий промежуток времени. Естественно, что если промежуток времени большой, этот запрос будет выполняться долго и, при установке DOP по умолчанию, займет все доступные процессоры, что, естественно, скажется на работе остальных пользователей. Следовательно, изменяя значение DOP, мы можем без изменения самого запроса повысить время отклика SQL сервера у других пользователей.
MS рекомендует устанавливать значение следующим образом:

Установка параметра на TSQL целиком для сервера:

EXEC sp_configure 'max degree of parallelism', 4;
reconfigure

Так же вы можете установить это значение для конкретного TSQL запроса:

USE AdventureWorks2008R2 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

В этом примере «хинт» maxdop меняет значение по умолчанию параметра max degree of parallelism на 2. Посмотреть текущую настройку можно так :

EXEC sp_configure 'Show Advanced',1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism'

Теперь давай те посмотрим как влияет это значение на скорость выполнения запроса. Для того что бы тестовый запрос, написанный выше, выполнялся более продолжительное время, добавим в него ещё один select. Запрос приобретет следующий вид :

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total
FROM Sales.SalesOrderDetail dt,
(
SELECT * FROM  Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
) dt2
WHERE dt.UnitPrice < $5.00
GROUP BY dt.ProductID, dt.OrderQty
ORDER BY dt.ProductID, dt.OrderQty

На моей тестовой машине значение ‘max degree of parallelism’ выставлено в 0. MSSQL запущен на машине с 4-х ядерным процессором. Я провел серию экспериментов с разными значениями MAXDOP: равным 1 – без распараллеливания запроса; равным 2 -  с использованием только 2 ядер; равным 4 – с использованием всех и без хинта для определения варианта, который использует сиквел по умолчанию. Для того, чтобы получить статистику выполнения, в запрос нужно включить опцию SET STATISTICS TIME ON, а также включить кнопку отображения плана запроса в Management studio. Для усреднения полученных результатов я выполнял каждый запрос в цикле 3 раза. Результаты можно видеть ниже:

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total
FROM Sales.SalesOrderDetail dt,
(
SELECT * FROM  Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
) dt2
WHERE dt.UnitPrice < $5.00
GROUP BY dt.ProductID, dt.OrderQty
ORDER BY dt.ProductID, dt.OrderQty
OPTION (MAXDOP 1);
SQL Server Execution Times:
   CPU time = 45942 ms,  elapsed time = 46118 ms.
SQL Server Execution Times:
   CPU time = 45926 ms,  elapsed time = 46006 ms.
SQL Server Execution Times:
   CPU time = 45506 ms,  elapsed time = 45653 ms.

На плане запроса видно что при установке хинта (MAXDOP 1) запрос выполнялся без распараллеливания. Среднее время выполнения запроса 45925.66 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total
FROM Sales.SalesOrderDetail dt,
(
SELECT * FROM  Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
) dt2
WHERE dt.UnitPrice < $5.00
GROUP BY dt.ProductID, dt.OrderQty
ORDER BY dt.ProductID, dt.OrderQty
OPTION (MAXDOP 2);

SQL Server Execution Times:
   CPU time = 51684 ms,  elapsed time = 28983 ms.
SQL Server Execution Times:
   CPU time = 51060 ms,  elapsed time = 26165 ms.
SQL Server Execution Times:
   CPU time = 50903 ms,  elapsed time = 26015 ms.

При установке хинта (MAXDOP 2) запрос выполнялся параллельно на 2 cpu, это можно увидеть на Number of Execution в плане выполнения запроса. Среднее время выполнения запроса 27054.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total
FROM Sales.SalesOrderDetail dt,
(
SELECT * FROM  Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
) dt2
WHERE dt.UnitPrice < $5.00
GROUP BY dt.ProductID, dt.OrderQty
ORDER BY dt.ProductID, dt.OrderQty
OPTION (MAXDOP 4);
SQL Server Execution Times:
   CPU time = 82275 ms,  elapsed time = 23133 ms.
SQL Server Execution Times:
   CPU time = 83788 ms,  elapsed time = 23846 ms.
SQL Server Execution Times:
   CPU time = 53571 ms,  elapsed time = 27227 ms.
 

При установке хинта (MAXDOP 4) запрос выполнялся параллельно на 4 cpu. Среднее время выполнения запроса 24735.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total
FROM Sales.SalesOrderDetail dt,
(
SELECT * FROM  Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
) dt2
WHERE dt.UnitPrice < $5.00
GROUP BY dt.ProductID, dt.OrderQty
ORDER BY dt.ProductID, dt.OrderQty
SQL Server Execution Times:
   CPU time = 85816 ms,  elapsed time = 23190 ms.
SQL Server Execution Times:
   CPU time = 85800 ms,  elapsed time = 23307 ms.
SQL Server Execution Times:
   CPU time = 58515 ms,  elapsed time = 26575 ms.

запрос выполнялся параллельно, так же  4 cpu. Среднее время выполнения запроса 24357.33ms

links : http://support.microsoft.com/kb/2023536

Интересная статья?  Подпишитесь на обновления блога и получите еще больше информации по RSS Vladimir Shurygin RSS  ,   RSS Vladimir Shurygin Email   или  twitter Vladimir Shurygin twitter !
Поделитесь с друзьями этой статьей в: