Ваш план обслуживания перестраивает индексы, которыми никто не пользуется

Автор: Pinal Dave, Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses

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

То, во что все верят

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

На флеш-хранилище штраф за разбросанные чтения может быть гораздо меньше. Если страницы уже находятся в буферном пуле, SQL Server вовсе не нужно извлекать их из хранилища. Плотность страниц по-прежнему имеет значение: полупустые страницы тоже занимают память.

Так что одна историческая причина для перестроения часто значит меньше, чем когда-то, а сама задача осталась.

Что перестроение на самом деле вам даёт

Две выгоды обычно приписывают не той причине, и их стоит разделить.

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

Одна оговорка. Более низкий коэффициент заполнения (fill factor) резервирует это пространство намеренно. Низкая плотность заслуживает перестроения только тогда, когда она совпадает с издержками рабочей нагрузки, на которые вы можете указать.

Статистика. Обычное, несекционированное перестроение rowstore также обновляет статистику индекса, читая каждую строку. Я проверил это, а не просто повторил. Я взял выборку из индекса на двести тысяч строк с десятипроцентной выборкой. rows_sampled вернулось как 56 045, ближе к двадцати восьми процентам, потому что SQL Server выбирает целые страницы. После ALTER INDEX REBUILD та же статистика показала 200 000. Каждая строка, без отдельного задания по статистике.

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

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

Если именно это вам помогает, проверьте целевое обновление статистики отдельно. Оно может дать то же улучшение с гораздо меньшим объёмом работы, чем перестроение индекса.

Во что это вам обходится

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

«Минимально журналируемое» не означает «без журнала». Операции всё ещё нужно место, чтобы завершиться и откатиться, поэтому исследуйте вашу модель восстановления, параметры перестроения, версию и реплики, прежде чем что-либо оценивать.

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

Это верно только до тех пор, пока перестроение происходит после полной резервной копии, относительно которой измеряются эти дифференциалы. Обычная полная резервная копия после этого сбрасывает эту базу. Копия COPY_ONLY — нет. На порядок ваших заданий стоит взглянуть раньше, чем на их расписание.

Я видел воскресное задание обслуживания, которое утраивало размер понедельничного дифференциала каждую неделю в течение многих лет, в компании, которая платила за офсайтовое хранилище по гигабайтам.

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

Что на самом деле делать

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

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

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

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

Прежде чем настраивать расписание, посмотрите, что у вас на самом деле есть:

SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
       OBJECT_NAME(ps.object_id) AS TableName,
       i.name AS IndexName,
       ps.partition_number,
       ps.page_count,
       CAST(ps.avg_fragmentation_in_percent AS decimal(5,1)) AS FragPct,
       CAST(ps.avg_page_space_used_in_percent AS decimal(5,1)) AS PageFullnessPct
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps
JOIN sys.indexes AS i
  ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.page_count >= 1000
  AND ps.index_level = 0
  AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
  AND i.type IN (1, 2)
ORDER BY ps.avg_fragmentation_in_percent DESC;

Полнота страницы учитывается наряду с фрагментацией, но не является окончательным вердиктом. Высокие девяностые означают, что выборочные листовые страницы упакованы и восстанавливать особо нечего. Если процент заполнения составляет 50 или 60, это повод проверить сканирование, нагрузку на память и разбиение страниц. Это не означает, что перестроение обязательно, поскольку при низком коэффициенте заполнения это пространство могло быть зарезервировано намеренно.

При использовании этого запроса с sys.dm_db_index_physical_stats, начинайте со значения параметра: SAMPLED, когда вам нужна информация о плотности страниц. Обычно он выбирает около одного процента страниц, но SQL Server автоматически использует DETAILED для индексов или куч меньше десяти тысяч страниц. Избегайте широкого запуска DETAILED на рабочей среде, потому что он читает каждую страницу.

Передача NULL для объекта сканирует всю базу данных. Фильтр по количеству страниц сокращает вывод, а не работу. Запускайте эту инвентаризацию в нерабочие часы; используйте явные идентификаторы объектов и индексов для плановых проверок.

До SQL Server 2022 этому запросу нужно разрешение VIEW DATABASE STATE; запросу об использовании ниже нужно VIEW SERVER STATE. Начиная с SQL Server 2022 используйте соответственно VIEW DATABASE PERFORMANCE STATE и VIEW SERVER PERFORMANCE STATE.

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

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

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       COALESCE(us.user_seeks, 0)
         + COALESCE(us.user_scans, 0)
         + COALESCE(us.user_lookups, 0) AS ReadOperations,
       COALESCE(us.user_updates, 0) AS UpdateOperations
FROM sys.indexes AS i
JOIN sys.tables AS t
  ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
  ON us.object_id = i.object_id
 AND us.index_id = i.index_id
 AND us.database_id = DB_ID()
WHERE i.index_id > 1
  AND i.type = 2
  AND t.is_memory_optimized = 0
  AND i.is_hypothetical = 0
  AND i.is_disabled = 0
  AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ReadOperations, UpdateOperations DESC;

Запрос явно исключает таблицы, оптимизированные для памяти. Одного лишь type недостаточно, чтобы поймать их некластерные индексы. У использованных представлений нет для них счётчиков, и LEFT JOIN превратил бы эти отсутствующие счётчики в нули. Отсутствие — не то же самое, что неиспользование. Проверяйте такие таблицы отдельно в sys.dm_db_xtp_index_stats.

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

Тест, который даёт вам ответ

Если кто-то в вашей команде уверен, что перестроение критично, есть контролируемый способ проверить это утверждение. Это также продуктивнее, чем спорить.

Приостановите только шаг перестроения, в рамках обычного контроля изменений, с планом отката. Охватите представительный деловой цикл, а не тихую двухнедельную паузу вместо конца месяца. Продолжайте обновление статистики. Используйте Query Store или вашу существующую базовую линию для сравнения длительности, CPU, логических чтений, ожиданий и изменений планов.

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

На некоторых системах что-то всё же ухудшается. Это кандидат, а не вывод. Данные растут, планы меняются, люди развёртывают новые вещи.

Сначала обновите статистику затронутого индекса, используя FULLSCAN, чтобы соответствовать обычному несекционированному перестроению. Если это устраняет регрессию, у вас есть исправление, не требующее перестроения.

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

Любой исход — это шаг к победе. Единственная проигрышная позиция — та, где никто никогда не проверял.

Почему эту задачу никогда не ставят под сомнение

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

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

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

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