SQL-Ex blog

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

Почему фрагментация это плохо?

Фрагментированные индексы могут вызвать проблемы с производительностью. Скажем, у вас есть 1Мб данных, они теоретически занимают 128 страниц данных (1024Кб / 8Кб = 128 страниц), однако из-за внутренней фрагментации эти 1Мб данных могут находиться на множестве частично заполненных страниц. Когда SQL Server выполняет запрос к таблице, который включает сканирование индекса, это сканирование должно исполняться на каждой странице. Сканирование 128 полных страниц было бы значительно быстрей, чем сканирование 200+ фрагментированных страниц. Теперь еще вспомним, что разыскиваемые данные могут находиться в различных местах диска из-за внешней фрагментации. Дополнительное сканирование в сочетании с ростом операций ввода/вывода могут вызвать большие проблемы производительности.

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

Как устранить фрагментацию?

Имеется табличнозначная функция с именем sys.dm_db_index_physical_stats, которая позволит вам проанализировать степень фрагментации индексов. После ее использования для идентификации проблематичных индексов вы сможете решить, что применить к ним - реорганизацию (reorganise) или перестройку (rebuild).

reorganise - название не вполне отражает действие, оно переупорядочивает страницы. Это помогает решить проблему внешней фрагментации, но содержимое страниц при этом не меняется.

rebuild удаляет индекс и заново строит его с нуля. Это более долгий и более трудоемкий процесс, но он решает проблемы как с внутренней, так и с внешней фрагментацией.

Что такое коэффициент заполнения (Fill-Factor)?

Для борьбы с фрагментацией многие администраторы баз данных применяют пользовательский коэффициент заполнения. Коэффициент заполнения - это значение, которое определяет процент заполнения страницы и сколько пространства будет намеренно оставлено свободным. Например, установка значения 80 оставит 20% страницы пустым, но разве мы сознательно не вводим внутреннюю фрагментацию? Ну, да, но теперь, когда вы вставляете данные, на странице есть место для них, что позволяет избежать расщепления с появлением новой страницы где-нибудь на диске. Другими словами, вы приносите в жертву более высокий уровень внутренней фрагментации, чтобы ограничить величину внешней фрагментации.

Тут нет одного значения при установке коэффициента заполнения на все случаи жизни, поэтому он должен устанавливаться на уровне индекса, а не на уровне сервера, в зависимости от того, как используется индекс. Статичная таблица, которая используется только для чтения, больше выиграет от значения, близкого к 0 (100% использование страницы), чем таблица, в которую регулярно вставляются и из которой удаляются данные. Вот где неоценимы навыки администратора баз данных.