MSSQL: ребилд индексов в высоко нагруженных системах, Standard Edition

В одной из моих предыдущих статей я рассказал о скрипте с названием GentleRebuild, который делал index rebuild в базах, работающих под нагрузкой 24/7, когда нет maintenance window, в Enterprise Edition. Там можно использовать опции ONLINE=ON и даже RESUMABLE=ON, вежливо уступая основной нагрузке базы.

А как же Standard Edition, где этого нет? Каюсь, раньше у меня в скрипте даже стояла проверка, и для Standard Edition скрипт сразу завершался. Но шеф меня попросил заняться и серверами со Standard Edition, и мне пришлось выжать из ситуации максимум.

Человек на мосту

Изображение не случайно.

Итак, у нас все операции непрерываемые. Скрипт по прежнему следит за тем, как он может мешать основной нагрузке на базу. Напомню, что скрипт следит за

• слишком высоким 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:

Для многих таблиц явно проглядывает линейная зависимость. Увы, отношение зависит, видимо, от ряда внутренних факторов, которые я не знаю. В итоге я остановился на формуле:

да, она не абсолютно точна, но это лучше, чем ничего.

Куда бежать? Дождаться конца операции, или делать kill/rollback?

Я написал программу для проведения экспериментов. Начнем с MAXDOP=1:

Оранжевый график — это сколько осталось времени до конца операции в процентах к полному времени выполнения. Очевидно, это прямая. Синяя линия — сколько времени будет идти rollback, если его выполнить в данный момент (время тоже в процентах к полному времени операции)

Как видно, до примерно 75% быстрее сделать rollback, а позже быстрее дождаться окончания rebuild.

Попробуем менять MAXDOP. Сразу скажу, что указание MAXDOP для INDEX REBUILD иногда игнорируется и операция идет в один поток — для некоторых индексов MS SQL иначе не умеет, видимо. Если же он умеет делать работу во много потоков, то образуется MAXDOP+1 запись в sysprocesses — одна управляющая и ‘рабочие’. Итак,

При MAXDOP=2 «point of no return» находится уже на 63%. А дальше начинаются чудеса:

Эффект «гребенки» неслучаен и хорошо воспроизводится. Обратите внимание, что по Y есть точки выше линии 100% — то есть rollback занимает дольше, чем вся операция от начала и до конца. Еще больше это проявляется при дальнейшем повышении MAXDOP:

Тут первая point of no return вообще на 42%. Идем дальше:

Вау! Впрочем, неудивительно — rollback то идет в один поток.

Для NONCLUSTERED индексов графики немного более плавные и имеют немного другие «points of no return».

При MAXDOP>2 систему сильно колбасит, с другой стороны, при увеличении MAXDOP работа будет закончена все быстрее и быстрее:

Здесь время в секундах. Как видно, при увеличении 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 достаточно условия по любому

Вот как это выглядит на реальной системе:

Вся эта логика отчасти купирует боль, и позволяет пройти много дальше не влияя сильно на рабочие процессы. Однако, могут быть ситуации где мы попали в красную зону. Например, не совсем правильно оцененный процент прогресса. Или заблокированный интерактивный процесс, возникший на позднем этапе.

Опция 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. Я не хочу включать возможность использования этой операции без оценки прогресса ее выполнения. А это требует отдельного исследования.