В двух прошлых статьях я разобрал Index REBUILD в Enterprise и Standard editions. Настало время осветить Index Reorganize - то есть Index Rebuild для бедных. Рекомендую заглянуть в прошлые статьи по ссылкам выше - там описан скрипт, который выполняет rebuild или reorg, контролируя течение процесса.
Ведь index reorganize не держит долгих блокировок, почти не нагружает сервер, работая в одном треде, поэтому он безопасен... правда?
Сравнение качества дефрагментации
На копии одной из характерных баз с production у нас, я провел тесты и получил следующую картину. Индексы перестраивались с помощью reorganize, а также rebuild с тремя опциями: без компрессии (серия данных rebuilt на графиках), с compression=page и compression=row.
Начнем с некластерных индексов:
По оси X отложен начальный уровень фрагментации индекса. По оси Y отложено изменения размера индекса на диске после операции. То есть Y=100% означает, что размер индекса не поменялся. Y=50% означает сжатие в два раза, значение больше 100% означает, что после операции размер индекса стал даже больше, чем до нее, что очень плохо.
Вас может насторожить, что вы не видите синих точек (серия rebult). Это потому, что их полностью накрыли точки серии page. Как я понимаю, индексы у нас 'хорошие' (по целым и guid), и там паковать особо нечего. А вот compression=row иногда сильно раздувает размер индекса, хотя есть случаи, когда она помогает. То есть тут надо решать case by case.
Желтые точки (reorg) всегда выше остальных - дефрагментация так себе. Вы также можете видеть скопление желтых точек около Y=100% - там reorganize не смог улучшить ничего.
Для кластерных индексов ситуация другая:
Как видно, compression=page, как правило, лучшая. reorganize работает на уровне простой перестройки индексов, без компрессии.
Сравнение времени работы
Даже при MAXDOP=1, rebuild оказывается в несколько раз быстрее, чем reorganize.
Красные точки - rebuild, синие - reorganize. Reorganize оказывается дольше для обычных индексов в 3-5-7 раз, для clustered indexes разница больше и иногда доходила до 30 раз. От чего конкретно это зависит я не изучал. Иногда reorganize не заканчивается никогда - но об этом позже.
Конечно, хотелось бы получить оценку времени выполнения reorganize. После большого количества экспериментов я получил эмпирические формулы. Исходные данные для формул:
W - Writes - sys.dm_exec_requests (для коннекции которая делает reorganize)
L - logical_reads из sys.dm_exec_requests
P - page_count - размер индекса в страницах - из sys.dm_db_index_physical_stats
F - fragment_count - число фрагментов - из sys.dm_db_index_physical_stats
D - density - из sys.dm_db_index_physical_stats - требует режима 'DETAILED'
Процент выполненной работы тогда для кластерных индексов:
Для получения процентов домножьте на 100. Для некластерных индексов:
Опять число надо домножить на 100. Точность метода - плюс минус 7-10%, иногда ошибка бывает больше. Но это лучшее, что я мог сделать.
Как всегда, по скрипт будет вам показывать процент сделанной работы и примерное время окончания операции.
Проблемы при index reorganize
Мем как бы намекает, что проблемы есть. Да, reorganizе не держит долгих блокировок на данные. Что не мешает ему лочить процессы по schema lock. Впрочем, так как скрипт был научен прерывать операцию и уступать блокировке, это не так опасно на боевой среде.
После прерывания скрипт запоминает процент выполненной работы, чтобы при возобновлении reorganize прибавлять ее к уже сделанной. При этом наблюдается эффект: после возобновления операции некоторое время процент не растет - сервер движется по тому месту, где reorganize уже был, и там особенно делать нечего. Через некоторое время, когда 'каретка' дойдет до 'целины', процент начинает расти. Для больших индексов время 'перемотки' может быть довольно большим, и увы - если блокировки бывают часто, то большие индексы не получится закончить - все время между операциями будет идти 'перемотка'
С третьей проблемой я столкнулся, когда в ряде случаев процесс начинал очень сильно замедлять процессы на PROD. При этом процент выполненной работы рос выше 120%, доходя до 200% и более. Эти два события всегда происходили вместе. Эвристическая формула все таки не ошибается в разы, и у меня есть одно объяснение.
В ряде случаев область дефрагментации наталкивается на область активной записи (потому что у нас 24/7), и они начинают лочить и мешать друг другу. Только reorganize что-то сделает, тут же произойдет insert, который расщепит страницы, а reorganize снова подойдет к этому месту.
В случае превышения 120% по эвристический формуле скрипт завершит операцию и пересчитает статистику - что сделано, то сделано, больше он трогать этот индекс не будет и перейдет к следующему. Через меню скрипта по Ctrl-C операцию можно также прервать в любой момент, пересчитав статистику (команда R)
Актуальная версия скрипта находится тут: https://github.com/tzimie/GentleRebuild
Сайт проекта: https://www.actionatdistance.com/gentlerebuild