В одной из моих предыдущих статей я рассказал о скрипте с названием GentleRebuild, который делал index rebuild в базах, работающих под нагрузкой 24/7, когда нет maintenance window, в Enterprise Edition. Там можно использовать опции ONLINE=ON и даже RESUMABLE=ON, вежливо уступая основной нагрузке базы.
А как же Standard Edition, где этого нет? Каюсь, раньше у меня в скрипте даже стояла проверка, и для Standard Edition скрипт сразу завершался. Но шеф меня попросил заняться и серверами со Standard Edition, и мне пришлось выжать из ситуации максимум.
Человек на мосту
![](https://habrastorage.org/getpro/habr/upload_files/58c/53d/691/58c53d691a267c7757cdfddc9cbdea73.jpg)
Изображение не случайно.
Итак, у нас все операции непрерываемые. Скрипт по прежнему следит за тем, как он может мешать основной нагрузке на базу. Напомню, что скрипт следит за
слишком высоким CPU
слишком большим объемом заполненности лога (LDF)
слишком большим ростом AlwaysOn queue (репликация лога не успевает за IO)
административными ограничениями (время, объем перестроенных индексов)
кастомными условиями
наличием процессов, блокированных операцией INDEX REBUILD
Если в Enterprise Edition скрипт может для большинства условий временно прервать (throttle) перестройку индекса в середине, то Standard Edition проверяет все те же условия, но только по окончании каждой операции. Большинство из условий не такие жесткие, кроме одного - блокировок. REBUILD большого индекса может занять минуты и часы, и если интерактивный процесс остановится и будет ждать этой операции, то все будет плохо.
Таким образом, наличие блокировки (какой-то процесс или процессы ждут REBUILD) является основанием для прерывания операции. Но в Standard Edition это прерывание вызывает rollback, и откат может занять больше времени, чем достроить индекс до конца! Так и родилась у меня аналогия с задачей про мост.
Вы идете по узкому мосту, а навстречу поезд. Куда бежать - назад или вперед? Вперед, навстречу поезду, бежать лучше, если осталось чуть-чуть. Но как это рассчитать точно и как узнать длину моста? Ведь для операций без RESUMABLE процент выполненной работы не показывается!
Длина моста, или оценка процента выполненной работы
После ряда выполненных экспериментов, я остановился на линейной зависимости между page_count (числа страниц в исходном индексе) и logical_reads из sys.dm_exec_requests:
![](https://habrastorage.org/getpro/habr/upload_files/9ea/7f9/abd/9ea7f9abd3f09f27cc19eda10ca14c33.png)
Для многих таблиц явно проглядывает линейная зависимость. Увы, отношение зависит, видимо, от ряда внутренних факторов, которые я не знаю. В итоге я остановился на формуле:
да, она не абсолютно точна, но это лучше, чем ничего.
Куда бежать? Дождаться конца операции, или делать kill/rollback?
Я написал программу для проведения экспериментов. Начнем с MAXDOP=1:
![](https://habrastorage.org/getpro/habr/upload_files/ec4/b1b/d85/ec4b1bd850b1f4a7d1b9182a3f772e81.png)
Оранжевый график - это сколько осталось времени до конца операции в процентах к полному времени выполнения. Очевидно, это прямая. Синяя линия - сколько времени будет идти rollback, если его выполнить в данный момент (время тоже в процентах к полному времени операции)
Как видно, до примерно 75% быстрее сделать rollback, а позже быстрее дождаться окончания rebuild.
Попробуем менять MAXDOP. Сразу скажу, что указание MAXDOP для INDEX REBUILD иногда игнорируется и операция идет в один поток - для некоторых индексов MS SQL иначе не умеет, видимо. Если же он умеет делать работу во много потоков, то образуется MAXDOP+1 запись в sysprocesses - одна управляющая и 'рабочие'. Итак,
![](https://habrastorage.org/getpro/habr/upload_files/af3/3e7/29e/af33e729e46688abfc0cf1df237e019b.png)
При MAXDOP=2 "point of no return" находится уже на 63%. А дальше начинаются чудеса:
![](https://habrastorage.org/getpro/habr/upload_files/ab0/6a8/915/ab06a89157c8248fa791514da37d69de.png)
Эффект "гребенки" неслучаен и хорошо воспроизводится. Обратите внимание, что по Y есть точки выше линии 100% - то есть rollback занимает дольше, чем вся операция от начала и до конца. Еще больше это проявляется при дальнейшем повышении MAXDOP:
![](https://habrastorage.org/getpro/habr/upload_files/5a2/93f/ad0/5a293fad05df816070b80c094a1d9db8.png)
Тут первая point of no return вообще на 42%. Идем дальше:
![](https://habrastorage.org/getpro/habr/upload_files/2d4/f73/3db/2d4f733dba542a88feb5087a16a742f0.png)
![](https://habrastorage.org/getpro/habr/upload_files/3b9/df2/71f/3b9df271f2717186e56ec5640b070d3f.png)
Вау! Впрочем, неудивительно - rollback то идет в один поток.
Для NONCLUSTERED индексов графики немного более плавные и имеют немного другие "points of no return".
При MAXDOP>2 систему сильно колбасит, с другой стороны, при увеличении MAXDOP работа будет закончена все быстрее и быстрее:
![](https://habrastorage.org/getpro/habr/upload_files/eeb/d97/3f0/eebd973f06eb1200470da3303981a404.png)
Здесь время в секундах. Как видно, при увеличении MAXDOP все ускоряется (синяя линия), а красная линия показывает наихудшее время rollback.
Вывод
Лучше использовать MAXDOP=1 или 2, если планируете убивать мешающий основным процессам rebuild. Либо можно перестраивать индекс в режиме кошки, которая прижав уши (будь что будет) перебегает дорогу как можно быстрее. Тогда лучше поставить MAXDOP побольше - чем короче операция, тем меньше вероятности блокировки, и выставить у скрипта параметр killnonresumable=0 - тогда блокировки не будут приводить к kill/rollback.
В обычном режиме (killnonresumable=1), скрипт GentleRebuild при наличии блокировки убивает операцию, только если она не дошла до "point of no return", которая определяется эвристикой (в $maxdop находится число записей в sysprocesses - это MAXDOP+1, либо 1, если MS SQL не послушался вас):
# returns point of no return value
# note that for MAXDOP=n there are typically n+1 threads, so $maxdop=2 should never occur
function PNRheuristic([int]$maxdop, [string]$itype) {
if ($maxdop -gt 9) { return 40. }
if ($itype -eq "CLUSTERED") { $heur = 75., 63., 63., 55., 45., 43., 42., 40., 37. }
else { $heur = 86., 74., 74., 70., 64., 55., 50., 45., 40. }
return $heur[$maxdop-1]
}
Сортировка жертв
Но всегда ли блокировка - это повод для паники? Есть процессы, которые могут подождать - UPDATE STATISTICS, разные джобы, которые грузят данные. А есть коннекции от интерактивных процессов, и если они ждут, то пользователь видит замерший экран или ловит таймаут.
Для сортировки жертв блокировки на критичные и не очень администратору предлагается отредактировать функцию, которая изначально выглядит вот так:
function VictimClassifier([string] $conn, [int] $spid) {
# returns:
# cmd - details of the blocked command
# cat - category this connection falls into
# waittime - time this connection already waited, sec
# maxwaitsec - maximum time for this connection allowed to wait
# $waitdescr, $waitcategory, $waited, $waitlimit
$q = @"
declare @cmd nvarchar(max), @job sysname, @chain int
select @job=J.name from msdb.dbo.sysjobs J
inner join (
select
convert(uniqueidentifier, SUBSTRING(p, 07, 2) + SUBSTRING(p, 05, 2) +
SUBSTRING(p, 03, 2) + SUBSTRING(p, 01, 2) + '-' + SUBSTRING(p, 11, 2) + SUBSTRING(p, 09, 2) + '-' +
SUBSTRING(p, 15, 2) + SUBSTRING(p, 13, 2) + '-' + SUBSTRING(p, 17, 4) + '-' + SUBSTRING(p, 21,12)) as j
from (
select substring(program_name,charindex(' 0x',program_name)+3,100) as p
from sysprocesses where program_name like 'SQLAgent - TSQL JobStep%' and spid=$spid) Q) A
on A.j=J.job_id
select @cmd=Event_Info from sys.dm_exec_input_buffer($spid, NULL)
select @chain=count(*) from sysprocesses where blocked=$spid and blocked<>spid
select distinct @cmd as cmd,isnull(@job,'') as job, @chain as chain, waittime/1000 as waittime,open_tran,rtrim(program_name) as program_name
from sysprocesses P where P.spid=$spid
"@
$vinfo = MSSQLscalar $conn $q
$prg = $vinfo.program_name
$cmd = $vinfo.cmd # top level command from INPUTBUFFER
$job = $vinfo.job # job name if it is a job, otherwise ""
$chain = $vinfo.chain # >0 if a process, locked by Rebuild, also locks other processes (chain locks)
$waittime = $vinfo.waittime # already waited
$trn = $vinfo.open_tran # has transaction open
# build category based on info above
$cat = "INTERACTIVE"
if ($cmd -like '*UPDATE*STATISTICS*') { $cat = "STATS" }
elseif ($job -like "*ImportantJob*") { $cat = "CRITJOB" }
elseif ($job -gt "") {
$cat = "JOB"
$cmd = "Job $job"
}
elseif ($prg -like "*Management Studio*") { $cat = "STUDIO" }
elseif ($prg -like "SQLCMD*") { $cat = "SQLCMD" }
if ($chain -gt 0) { $cat = "L-" + $cat } # prefix L- is added when there are chain locks, it is critical
elseif ($trn -gt 0) { $cat = "T-" + $cat } # prefix T- means that locked process is in transaction
$cmd = $prg + " " + $cmd
# allowed wait time before it thrrottles or kills rebuild
$maxwaitsecs = @{
"INTERACTIVE" = 30
"STATS" = 36000
"JOB" = 600
"CRITJOB" = 60
"STUDIO" = 3600
"SQLCMD" = 600
"T-INTERACTIVE" = 30
"T-STATS" = 600
"T-JOB" = 600
"T-CRITJOB" = 30
"T-STUDIO" = 1800
"T-SQLCMD" = 600
"L-INTERACTIVE" = 20
"L-STATS" = 20
"L-JOB" = 20
"L-CRITJOB" = 20
"L-STUDIO" = 20
"L-SQLCMD" = 20
}
return $cmd, $cat, $waittime, $maxwaitsecs[$cat]
}
Как вы видите, процедура определяет SQL команду (текст), имя job (если есть), program_name, и на этом основании процесс жертва классифицируется в одну из категорий. Здесь их 6, однако, если процесс жертва в транзации (а значит больше вероятность цепочки блокировок) или такие цепочки уже возникли, то к имени категории дописывается "T-" (в транзакии) и "L-" (цепочка блокировок).
В самом конце на основании категории вычисляется время, которое процесс может подождать (предполагается, что эти параметры может подправить DBA под свою систему).
Итак, мы знаем время, которое процесс УЖЕ ждал ($waittime) и максимальный срок, который он может без последствий ждать. Дальнейшая логика базируется на следующих комбинациях условий:
Мы уже за "point of no return" - увы, как бы ни было плохо, уже лучше подождать окончания операции.
Иначе, если процесс уже ждал больше, чем ему позволено, то придется делать kill/rollback
Иначе сложим время, которое он ждал с "projected time" - ожидаемое время до конца операции, которое скрипт рассчитывает на основе процента выполненной работы (как говорилось выше, эта оценка не совсем точная) и текущей скорости работы. Если wait time + projected time больше позволенного максимума, то мы делаем kill/rollback заранее, потому что до конца операции время ожидания данного процесса "перезреет" и превысит допустимый максимум
Если заблокированных процессов много, то для kill/rollback достаточно условия по любому
Вот как это выглядит на реальной системе:
![реальный PROD реальный PROD](https://habrastorage.org/getpro/habr/upload_files/029/28c/f43/02928cf43ca84fb61c80efd4ba22596b.png)
Вся эта логика отчасти купирует боль, и позволяет пройти много дальше не влияя сильно на рабочие процессы. Однако, могут быть ситуации где мы попали в красную зону. Например, не совсем правильно оцененный процент прогресса. Или заблокированный интерактивный процесс, возникший на позднем этапе.
Опция offlineretries=n заставляет скрипт пытаться повторить операцию после kill/rollback снова и снова после kill/rollback (после окончания попыток он переходит к следующему индексу), и иногда скрипту удается проскочить в окно возможностей даже для больших таблиц. Но будем честны - если у вас есть большая таблица к которой 24/7 идут обращения интерактивных пользователей нон стоп, то на Standard Edition вы не сможете сделать ничего, не влияя на работу.
Некоторые плюшки Standard Edition (для оптимизма)
В OFFLINE INDEX REBUILD есть и хорошие вещи. Например, он не влияет на планы операций. Для RESUMABLE усложнения execution plans может быть проблемой.
RESUMABLE не работает с SORT_IN_TEMPDB. Опция sortintempdb=n добавляет опцию SORT_IN_TEMPDB=ON, если таблица меньше n мегабайт.
Наконец, опция forceoffline=1 "превращает" Enterprise Edition в Standard, если вы не хотите пользоваться ONLINE и RESUMABLE.
Актуальная версия скрипта находится тут: https://github.com/tzimie/GentleRebuild
Сайт проекта: https://www.actionatdistance.com/gentlerebuild
P.S. А как же INDEX REORGANIZE? Stay tuned. Я не хочу включать возможность использования этой операции без оценки прогресса ее выполнения. А это требует отдельного исследования.