Степени параллелизма и степени неопределенности в Microsoft SQL Server

Во-первых, автор хотел бы рассеять любые сомнения по поводу того, что указанная опция устанавливает, сколько процессоров может использовать SQL Server при обслуживании нескольких подключений (или пользователей) - это не так! Если SQL Server имеет доступ к четырем неактивным процессорам, и он настроен на использование всех четырёх процессоров, он будет использовать все четыре процессора, независимо от максимальной степени параллелизма.

Так, что же эта опция даёт? Эта опция устанавливает максимальное число процессоров, которые SQL Server может использовать для одного запроса. Если запрос к SQL Server должен вернуть большой объём данных (много записей), его иногда имеет смысл распараллелить, разбив на несколько маленьких запросов, каждый из которых будет возвращать своё подмножество строк. Таким образом, SQL Server может использовать несколько процессоров, и, следовательно, на многопроцессорных системах большое количество записей всего запроса потенциально может быть возвращено быстрее, чем на однопроцессорной системе.

Есть множество критериев, которые должны быть учтены до того, как SQL Server вызовет "Intra Query Parallelism" (разбивку запроса на несколько потоков), и нет смысла их здесь детализировать. Вы можете найти их в BOL, поискав фразу "Degree of parallelism". Там написано, что решение о распараллеливании основано на доступности памяти процессору и, особенно, на доступности самих процессоров.

Итак, почему мы должны продумать использование этой опции - потому что, оставляя её в значении по умолчанию (SQL Server сам принимает решение о распараллеливании), иногда можно получить нежелательные эффекты. Эти эффекты выглядят примерно так:

• Распараллеленные запросы выполняются медленнее.
• Время исполнения запросов может стать недетерминированным, и это может раздражить пользователей. Время исполнения может измениться потому что:
  • Запрос может иногда распараллеливаться, а иногда нет.
  • Запрос может блокироваться параллельным запросом, если перед этим процессоры были перегружены работой.

Прежде, чем мы продолжим, автор хотел бы заметить, что нет особой необходимости погружаться во внутреннюю организацию параллелизма. Если же Вы этим интересуетесь, Вы можете почитать статью "Parallel Query Processing" в Books on Line, в которой эта информация изложена более детально. Автор считает, что есть только две важные вещи, которые стоит знать о внутренней организации параллелизма:

1. Параллельные запросы могут породить больше потоков, чем указано в опции "Max degree of parallelism". DOP 4 может породить более двенадцати потоков, четыре для запроса и дополнительные потоки, используемые для сортировок, потоков, агрегатов и сборок и т.д.
2. Распараллеливание запросов может провоцировать разные SPID ожидать с типом ожидания CXPACKET или 0X0200. Этим можно воспользоваться для того, что бы найти те SPID, которые находятся в состоянии ожидания при параллельных операциях, и имеют в sysprocesses waittype: CXPACKET. Для облегчения этой задачи, автор предлагает воспользоваться имеющейся в его блоге хранимой процедурой: track_waitstats.

И так "Запрос может выполняться медленнее при распараллеливании" почему?

• Если у системы очень слабая пропускная способность дисковых подсистем, тогда при анализе запроса, его декомпозиция может выполняться дольше, чем без параллелизма.
• Возможен перекос данных или блокировки диапазонов данных для процессора, порождённые другим, используемым параллельно и запущенным позже процессом, и т.д.
• Если отсутствует индекс для предиката, что приводит к сканированию таблицы. Параллельная операция в рамках запроса может скрыть тот факт, что запрос выполнился бы намного быстрее с последовательным планом исполнения и с правильным индексом.

Из всего этого следует рекомендация проверять исполнение запроса без параллелизма (DOP=1), это поможет идентифицировать возможные проблемы.

Упомянутые выше эффекты параллелизма, сами собой должны навести Вас на мысль о том, что внутренняя механика распараллеливания запросов не подходит для применения в OLTP - приложениях. Это такие приложения, для которых изменение времени исполнения запроса может раздражать пользователей и для которых сервер, одновременно обслуживающий множество пользователей, вряд ли выберет параллельный план исполнения из-за присущих этим приложениям особенностей профиля рабочей нагрузки процессора.

Поэтому, если Вы собираетесь использовать параллелизм, то, скорее всего это понадобится, для задач извлечения данных (data warehouse), поддержки принятия решений или отчётных систем, где не много запросов, но они являются достаточно тяжёлыми и исполняются на мощном сервере с большим объёмом оперативной памяти.

Если Вы решили использовать параллелизм, какое же значение нужно установить для DOP?. Хорошей практикой для этого механизма является то, что если Вы имеете 8 процессоров, тогда устанавливайте DOP = 4, и это с большой степенью вероятности будет оптимальной установкой. Однако, нет никаких гарантий, что так оно и будет работать. Единственный способ убедиться в этом - протестировать разные значения для DOP. В дополнение к этому, автор хотел предложить свой, основанный на эмпирических наблюдениях совет, никогда не устанавливать это число больше, чем половине от числа процессоров, которые есть в наличии. Если бы автор имел процессоров меньше шести, он установил бы DOP в 1, что просто запрещает распараллеливание. Он мог бы сделать исключение, если бы имел базу данных, которая поддерживает процесс только одного пользователя (некоторые технологии извлечения данных или задачи отчётности), в этом случае, в порядке исключения, можно будет установить DOP в 0 (значение по умолчанию), которое позволяет SQL Server самому принимать решение о необходимости распараллеливания запроса.

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

Наконец, ваш запрос может замедляться при распараллеливании из-за ошибок, поэтому убедитесь, что на вашем сервере установлен последний сервисный пакет (service pack).

REATE proc track_waitstats
 (
 @num_samples int=10
 ,@delaynum int=1
 ,@delaytype nvarchar(10)='minutes'
 )
AS
-- T. Davidson
-- This stored procedure is provided =AS IS= with no warranties, 
-- and confers no rights. 
-- Use of included script samples are subject to the terms 
-- specified at https://www.microsoft.com/info/cpyright.htm
-- @num_samples is the number of times to capture waitstats, 
-- default is 10 times. default delay interval is 1 minute
-- delaynum is the delay interval. delaytype specifies whether 
-- the delay interval is minutes or seconds
-- create waitstats table if it does not exist, otherwise truncate

set nocount on
if not exists (select 1 from sysobjects where name = 'waitstats')
 create table waitstats ([wait type] varchar(80), 
 requests numeric(20,1),
 [wait time] numeric (20,1),
 [signal wait time] numeric(20,1),
 now datetime default getdate())
else truncate table waitstats

dbcc sqlperf (waitstats,clear) -- clear out waitstats

declare @i int
 ,@delay varchar(8)
 ,@dt varchar(3)
 ,@now datetime
 ,@totalwait numeric(20,1)
 ,@endtime datetime
 ,@begintime datetime
 ,@hr int
 ,@min int
 ,@sec int

select @i = 1
select @dt = case lower(@delaytype)
 when 'minutes' then 'm'
 when 'minute' then 'm'
 when 'min' then 'm'
 when 'mm' then 'm'
 when 'mi' then 'm'
 when 'm' then 'm'
 when 'seconds' then 's'
 when 'second' then 's'
 when 'sec' then 's'
 when 'ss' then 's'
 when 's' then 's'
 else @delaytype
end

if @dt not in ('s','m')
begin
 print 'please supply delay type e.g. seconds or minutes'
 return
end

if @dt = 's'
begin
 select @sec = @delaynum % 60
 select @min = cast((@delaynum / 60) as int)
 select @hr = cast((@min / 60) as int)
 select @min = @min % 60
end

if @dt = 'm'
begin
 select @sec = 0
 select @min = @delaynum % 60
 select @hr = cast((@delaynum / 60) as int)
end

select @delay = right('0'+ convert(varchar(2),@hr),2) + ':' + 
 + right('0'+convert(varchar(2),@min),2) + ':' + 
 + right('0'+convert(varchar(2),@sec),2)

if @hr > 23 or @min > 59 or @sec > 59
begin
 select 'hh:mm:ss delay time cannot > 23:59:59'
 select 'delay interval and type: ' + convert (varchar(10)
 ,@delaynum) + ',' + @delaytype + ' converts to ' 
 + @delay
 return
end

while (@i <= @num_samples)
begin
 insert into waitstats ([wait type], requests, [wait time]
 ,[signal wait time]) 
 exec ('dbcc sqlperf(waitstats)')
 select @i = @i + 1
 waitfor delay @delay
End

--- create waitstats report
execute get_waitstats

--//--//--//--//--//--//--//--//--//-//--//--//--//--//--//--//--//--/ 

CREATE proc get_waitstats
AS
-- This stored procedure is provided =AS IS= with no warranties, and 
-- confers no rights. 
-- Use of included script samples are subject to the terms specified 
-- at https://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by 
-- percentage
-- can be run when track_waitstats is executing

set nocount on

declare @now datetime
 ,@totalwait numeric(20,1)
 ,@endtime datetime
 ,@begintime datetime
 ,@hr int
 ,@min int
 ,@sec int

select @now=max(now),@begintime=min(now),@endtime=max(now)
from waitstats where [wait type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total

select @totalwait = sum([wait time]) + 1 from waitstats 
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE'
 , 'Total', '***total***') and now = @now

-- insert adjusted totals, rank by percentage descending

delete waitstats where [wait type] = '***total***' and now = @now

insert into waitstats select '***total***'
 ,0 
 ,@totalwait
 ,@totalwait
 ,@now 

select [wait type]
 ,[wait time]
 ,percentage = cast (100*[wait time]/@totalwait as numeric(20,1))
from waitstats 
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
and now = @now
order by percentage desc