Ступеня паралелізму і ступеня невизначеності в Microsoft SQL Server

Переклад Олександра Гладченко

Max degree of parallelism (DOP) – додаткова опція конфігурації 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).





CREATE 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 http://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 http://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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*