Нужно ли перестраивать индекс при большом количестве обновлений

Что проверяют

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

Как отвечать

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

Дальше по делу:

• фрагментацию измеряют, а не предполагают: в SQL Server это sys.dm_db_index_physical_stats, и у маленьких индексов перестроение смысла не имеет;
• ALTER INDEX ... REORGANIZE дефрагментирует листовой уровень на месте, работает порциями и его можно прервать; REBUILD строит индекс заново, тяжелее, но заодно обновляет статистику;
• REBUILD держит блокировку, если не включён онлайн-режим, а он доступен не во всех редакциях;
• часто обновляемую колонку лучше вынести из ключа индекса в INCLUDE или убрать совсем;
• фактор заполнения оставляет свободное место на странице и снижает число расщеплений.

Где спотыкаются

Путают перестроение и реорганизацию, называя вторую «пересозданием». Это разные операции: реорганизация не трогает статистику и не строит структуру заново, а перестроение делает и то и другое ценой блокировок и журнала.

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

Списывают деградацию на фрагментацию, не открыв план запроса. Чаще виноваты неподходящий состав индекса, выборка всех колонок ради двух и функция над колонкой в условии, из-за которой поиск по индексу превращается в сканирование.