Некролог на ночное задание по перестроению индексов, 1998–2026

Автор: Pinal Dave, The Nightly Index Rebuild Job, 1998 to 2026: An Obituary

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

Ранние годы задания по перестроению индексов

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

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

Диски изменились в 2010-х. Задание этого не заметило.

Обследование

Прежде чем написать это извещение, я сделал покойному внимательное "вскрытие" на SQL Server 2025. Были построены три таблицы, настолько фрагментированные, насколько таблица вообще может быть.

По миллиону строк в каждой, с кластерным индексом по случайному GUID. Они вставлялись тысячей пакетов по тысяче, так что страницы разделялись по мере заполнения. Каждая получилась с фрагментацией 99,3 процента и плотностью страниц 68 процентов. Это 38 000 страниц там, где упорядоченной таблице понадобилось бы 26 000.

Одну оставили в покое. Одну подвергли REORGANIZE. Одну — REBUILD. Затем каждую читали холодной и читали тёплой. Тест поиска находится ниже, и надо отметить, что моя первая версия его была неверной.

сканирование кластерного индекса	фрагментация	страницы	плотность	логические чтения	холодные физические	холодное	тёплое
оставлена в покое	99,26%	38 269	68%	38 445	42 833	884 мс	383–431 мс
после REORGANIZE	0,48%	26 409	98%	26 579	26 623	696 мс	292–317 мс
после REBUILD	0,01%	26 317	98%	26 402	26 457	610 мс	283–314 мс

Холодное означает CHECKPOINT, а затем DROPCLEANBUFFERS. Тёплое — это разброс за три прохода, ни один из которых не касался диска. Моя первая версия этой таблицы сбрасывала чистые буферы без CHECKPOINT. Её столбец «холодное» измерял что-то, чему я не могу дать название. Те числа отозваны.

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

В запомнилось хорошего о нём

Сканирование стало быстрее. Чтения упали с 38 445 до 26 402 после перестроения и до 26 579 после реорганизации. Тёплое время упало примерно на четверть.

Посмотрите почему. Чтения упали на ту же долю, что и количество страниц. Перестроение не сделало страницы быстрее для чтения. Оно сделало их меньше, заполнив каждую до 98 процентов вместо 68. Цифры поиска ниже взяты из отдельного прогона на таблицах, построенных таким же образом.

Плотность и фрагментация в этом тесте менялись вместе, поэтому он не может их разделить. Что он может показать — так это то, что чтения упали ровно так же, как упало количество страниц. Сканирование читает страницы, а не строки.

В чём оно было не так хорошо

Поиски. Мой первый прогон создал индекс поиска после обслуживания. Все три таблицы искали через свежий индекс, и числа вышли идентичными. Это ничего не измеряло, и рецензент это поймал.

Исправленный прогон построил индекс поиска первым и дал ему фрагментироваться вместе с таблицей. Затем его обслуживали вместе со всем остальным. Он начал с фрагментации 99 процентов и плотности 71 процент на всех трёх.

индекс поиска (ix_n)	фрагментация	страницы	плотность
оставлен в покое	99,36%	4 520	71%
после REORGANIZE	0,52%	3 249	99%
после REBUILD	0,00%	3 216	100%

10 000 поисков	логические чтения	физические чтения	CPU	затраченное время
оставлен в покое, холодный	30 113	4 592	447 мс	604 мс
после REORGANIZE, холодный	30 089	3 288	338 мс	437 мс
после REBUILD, холодный	30 071	3 176	411 мс	568 мс
оставлен в покое, тёплый	30 094	0	320 мс	320 мс
после REORGANIZE, тёплый	30 070	0	321 мс	321 мс
после REBUILD, тёплый	30 055	0	319 мс	318 мс

Холодное означает CHECKPOINT, а затем DROPCLEANBUFFERS перед каждой таблицей. Моя вторая попытка пропустила CHECKPOINT и получила числа, которые льстили обслуживанию. Это третья попытка.

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

Каждый поиск в среднем занимал около трёх логических чтений — 30 055 на 10 000. Дерево имеет три уровня глубины в любом состоянии листа.

Большинство транзакционных систем весь день занимаются поисками по страницам, которые остаются в кэше. Для них этот прогон не нашёл ничего, что можно было бы почувствовать.

Цена привычки

REORGANIZE заняло 16 секунд на миллионе строк и записало 595 МБ в журнал транзакций. Оно перемещает страницы по одной и регистрирует каждое перемещение.

REBUILD заняло 0,85 секунды и записало 1,1 МБ. Эта база данных была в простом режиме восстановления, а перестроение там минимально протоколируется. В полном режиме восстановления оно записало бы весь индекс. Перестраивайте каждый индекс каждую ночь в полном режиме восстановления, и объём журнала станет собственной проблемой. Я не измерял, насколько большим, поэтому не буду называть числа.

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

Причина смерти

Твердотельное хранилище, которое убрало читающую головку, для защиты которой задание и было построено.

Улучшенное обслуживание статистики, которое, как оказалось, было тем, что люди в основном и получали от перестроения, сами того не зная. Обычное перестроение rowstore обновляет собственную статистику этого индекса полным сканированием — как побочный эффект. Секционированные и возобновляемые перестроения имеют свои собственные правила. Привычка годами держалась на этом побочном эффекте.

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

Пережившие

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

Вместо цветов

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

Если задание всё ещё работает в вашей организации и никто не знает, для чего оно, я помогу вам написать надгробную речь.

Фрагментация — не болезнь, это симптом, который старый диск просто чувствовал.