Мой Дорогой Друг Шон недавно написал пост, рассказывающий о том, как люди неправильно обслуживают индексы. Я собираюсь пойти немного дальше и поговорить о том, что сам метод, которым ваш скрипт, обслуживающий индексы, оценивает фрагментацию индексов - неправильный.
Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и то же самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к sys.dm_db_index_physical_stats.
Все эти запросы используют столбец avg_fragmentation_in_percent, чтобы понять - нужно ли перестроить индекс. Документация (по ссылке выше) про этот столбец имеет сказать следующее:
Это мера логической фрагментации. Логическая фрагментация - это когда страницы "перемешаны" на диске.
Если вы используете приличные диски, даже на SAN, или у вас нормальный объём ОЗУ, вы можете понять из Великого Поста Шона, что фрагментация - это не самая худшая участь, которая может выпасть на долю ваших индексов. Если вы обслуживаете статистику, всё будет в порядке.
Кэши рулят
Если вы человек, который заботится о различных кэшах на сервере, вроде буферного пула или кэша планов выполнения, то вы бы хотели измерить что-то совершенно иное. Вы бы хотели измерить сколько свободного пространства у вас есть на каждой странице данных, потому что с кучей свободного пространства на каждой странице, ваши данные будут занимать больше места в памяти, когда вы будете считывать их с диска.
Вы могли бы сделать это с помощью столбца avg_page_space_used_in_percent.
НО...
Ваше любимое решение по обслуживанию индексов позаботится о вас и запустит, по умолчанию, dm_db_index_physical_stats в режиме LIMITED. Это всё потому что более подробные измерения могут быть очень тяжёлыми на сервере, где хранится множество данных, и, блин, даже LIMITED может выполняться очень долго.
Но, если бы я собирался принять решение о том нужно ли перестраивать индекс, это именно та метрика, которую я бы хотел использовать. Потому что этого неиспользуемого пространства может быть очень много.
Штука в том, что между avg_fragmentation_in_percent и avg_page_space_used_in_percent, нет особой корреляции.
Локальная БД
Посмотрим на фрагментацию в моей локальной БД Stack Overflow 2013:
Обе таблицы достаточно фрагментированы, чтобы привлечь внимание обслуживающего скрипта, но перестройка индекса, на самом деле, помогает только таблице Posts, несмотря на то, что мы перестроили оба.
В таблице Comments, avg_page_space_used_in_percent слегка уменьшается, а в Posts становится лучше примерно на 10%.
Количество страниц для Comments не изменяется, но уменьшается примерно на 500 тысяч для Posts.
Вот это то, что мне нравится. Я был бы рад читать на 500 тысяч меньше страниц при сканировании таблицы целиком.
Но при этом, я вообще-то не хочу сканировать целиком таблицу, если говорить не об отчётах или хранилищах данных.
Если мы говорим об OLTP, мы обычно избегаем сканирования больших таблиц и чтобы этого добиться, мы создаём некластерные индексы, которые помогают искать данные эффективно, и пишем запросы с чёткими условиями, которые обеспечивают эффективное использование этих индексов.
Правильно?
Правильно
Подумайте о настройках обслуживания индексов
Вероятно они на стандартных 5% и 30% для реорганизации и перестроения. Дело не только в том, что они абсурдно низкие, но и в том, что они даже измеряют не тот тип фрагментации. Даже при 84% "фрагментации" мы видели страницы, заполненные на 75%.
Это не идеально, но едва ли это катастрофа.
Да вы возможно размышляли о том, чтобы установить fill factor ещё меньше, чтобы избежать фрагментации.
Что ещё хуже, вы, вероятно, смотрите все таблицы > 1000 страниц, т.е. примерно 8МБ. Но если у вас проблемы с тем, чтобы прочитать и удержать в памяти 8 мегабайт - может пора сгонять в магазин?
Спасибо, что прочитали!
Примечание переводчика
Тема достаточно холиварная. Erik Darling и Brent Ozar достаточно давно относятся к той группе, которая топит за то, что, в общем случае, индексам не нужно обслуживание. В противовес им можно поискать посты Paul S. Randal и Paul White, которые наоборот считают, что индексы нужно регулярно обслуживать.
На Хабре не нашёл постов/переводов, представляющих такую точку зрения, поэтому решил сделать сам. Ну и интересно, как обслуживают индексы dba на Хабре - принимайте участие в опросе.
