Правда о fill-factor в MS SQL Server

Сначала, мы должны понять основы того, как организованы индексы. SQL Server организовывает индексы как Би-дерево, с одной корневой страницей, являющейся отправной точкой для построения индекса. Корневая страница может иметь указатели на две или более страницы следующего уровня индекса, и, каждая из этих страниц, может иметь указатели на множество страниц следующего уровня. Последний уровень индекса - это уровень листьев, который должен содержать все индексные ключевые значения в сортируемой последовательности. В SQL Server 2000 эта последовательность может быть или возрастающей или в порядке убывания. Более ранние версии поддерживали все индексы в порядке возрастания. В кластерном индексе, уровень листьев - хранит данные, так что SQL Server хранит данные в отсортированном порядке. В не кластерном индексе, уровень листьев содержит указатели на данные. Fill factor - значение, которое Вы можете определить при создании индекса, чтобы сообщить SQL Server, насколько заполненными Вы хотите иметь страницы индекса. Вы можете определить fill factor в инструкции CREATE INDEX или когда Вы используете инструкцию CREATE TABLE или ALTER TABLE для определения первичного ключа или уникального ограничения на таблицу, после чего SQL Server автоматически построит индекс, и установит первичные ключи и уникальные ограничения. Конечно, Вы также можете определить fill factor, используя Enterprise Manager, при создании индекса или добавлении ограничений к таблице. Если Вы не определяете явно значение fill-factor, SQL Server установит значение fill factor по умолчанию. Чтобы определить, какое было принято значение fill factor по умолчанию, Вы можете выполнить следующую команду:

Вначале, нужно установить расширенный вывод конфигурационных значений:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

Затем, непосредственно смотрим значение по умолчанию 'fill factor':

EXEC sp_configure 'fill factor'
GO

В завершение, возвращаем изменённое значение назад:

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Если Вы не изменяли значение по умолчанию, Вы сможете увидеть в отчёте значение fill factor для параметра run_value (значение, используемое в настоящее время), установленное в "0":

Name………………minimum….maximum….config_value….run_value
---------------------------------------------------------------------------------------
fill factor (%)………0…………..100…………0………………0……….

fill factor установленный в "0" будет означать, что страницы индекса будут полностью заполнены, но в верхних уровнях индекса оставлено место для одной - двух индексных строк, которые могут быть добавлены позже. Фактически, Вы явно никогда не сможете присвоить fill factor значение "0" при создании индекса, потому что "0" является недопустимым значением для команды CREATE INDEX. Единственный способ получить fill factor равный "0" состоит в том, чтобы принять значение по умолчанию, если Вы не изменяли его до этого. Вы можете изменить значение fill factor по умолчанию, выполнив команду sp_configure и добавив второй параметр, который должен соответствовать желаемому, новому значению fill factor по умолчанию:

EXEC sp_configure 'fill factor', 80
GO
RECONFIGURE
GO

Вы должны остановить и перезапустить SQL Server для того, чтобы изменения вступили в силу. Новый fill factor будет действительно установлен, когда Вы увидите новое значение в столбце run_value.

Если Вы создаете индекс с fill factor = 80, страницы будут на 80 процентов заполнены, и верхние уровни индекса будут иметь достаточно места, оставленного в них для учёта одной или двух индексных строк.

Обратите внимание, что SQL Server не поддерживает оригинальное значение fill-factor, в момент добавления к таблице новых строк. fill factor только управляет степенью заполнения ваши страницы при первоначальном создании индекса. SQL Server хранит первоначальное значение fill factor в системной таблице sysindexes, в столбце OrigFillFactor. Если Вы выполняете команду DBCC DBREINDEX, которая перестраивает один или все индексы таблицы, определение fill factor равным "0" сообщает SQL Server, что необходимо перестроить индексы с оригинальным значением fill factor, и что индекс будет построен не обязательно со значением по умолчанию, которое Вы установили для вашего сервера.

Значение fill-factor может быть важно в среде, где выполняется очень много вставок. Уровень листьев индекса должен хранить все индексные, ключевые значения в сортируемой последовательности, так, что если кто-то будет вставлять новую строку в таблицу, индексное, ключевое значение в этой строке будет определять позицию строки в индексе (или таблице, если индекс кластерный). Например, если Вы имеете индекс по имени человека, вставка строки с именем "Marlin" потребует, чтобы SQL Server вставил новую индексную строку в ту же самую страницу с другими именами, которые начинаются на "Ma", возможно, между Margolin и Martin. Если страница, которой новая строка должна принадлежать, полностью заполнена, SQL Server разобьет её и свяжет с новой страницей в цепочку страницы. SQL Server переместит приблизительно половину строк из первоначальной, заполненной страницы в новую страницу. Мало того, что такое разделение страниц является ресурсёмкой операцией, которая может заметно сказаться на эффективности операций вставки, но вследствие того, что новая страница вероятнее всего окажется физически не непрерывной по отношению к первоначальной странице, Вы получите в результате фрагментацию индекса или таблицы.

Создание индекса с низким значением fill factor означает, что в вашей таблице зарезервировано место для последующего роста, что предотвращает разбиение страниц, откладывая, таким образом, связанные с этим проблемы. Однако, если ваши страницы заполнены только частично, чтобы хранить данные потребуется большее количество страниц и индекс может достичь весьма большого размера. Микрософт определил значение по умолчанию для fill-factor равное "0", как компромисс между наличием места для последующего роста и минимизацией размера таблицы и её индексов после создания. С fill factor = "0", уровень листьев будет заполнен, но страницы в верхних уровнях индекса, которые могут быть подвержены разбиению, если они станут заполненными, имеют некоторый резерв места для последующего роста.

В версиях SQL Server до 6.5, значение fill-factor = "100" подразумевало, что не только уровень листьев индекса, но и все уровни были бы полностью заполнены. Вообще, это значение используется только для таблиц, которые являются read-only (только для чтения). В SQL Server 6.5, Микрософт добавил новую опцию к команде CREATE INDEX. Если Вы также включаете опцию WITH PAD_Index, безотносительно значения fill-factor, Вы предпишете серверу устанавливать значение fill-factor всем уровням индекса, а не только уровню листьев. Так, создание индекса со значением fill-factor = "0" и включение опции PAD_Index, будет иметь тот же самый эффект, как использование значения fill-factor = "100". Но что будет, если fill factor = "100" с обычным значением PAD_Index?

Обратите внимание, что Вы можете определить значение fill-factor при определении первичного ключа или уникального ограничения на таблицу, потому что SQL Server автоматически строит индекс, чтобы поддержать эти ограничения. Однако, Вы не можете определить опцию PAD_Index одновременно с ограничением. Вы можете использовать её только в инструкции CREATE INDEX.