В этой статье мы рассмотрим опцию FILLFACTOR (FF) в SQL Server и ее влияние на производительность индекса. Опция FILLFACTOR указывает, насколько заполняются листовые страницы индекса, в то время как опция Pad Index указывает, будут ли промежуточные страницы индекса использовать тот же коэффициент заполнения, что и указан для индекса.

По умолчанию коэффициент заполнения индекса извлекается из настроек базы данных экземпляра. Значение по умолчанию равно 0, что означает, что движок SQL Server полностью заполняет все страницы индекса данными, что эквивалентно установке значения 100%. Однако установка и поиск оптимального коэффициента заполнения для вашей базы данных не является простой задачей. Это требует обдумывания и тестирования, чтобы убедиться, что значения, которые вы придумали, будут работать удовлетворительно.

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

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

Давайте рассмотрим несколько тестовых сценариев, чтобы понять влияние различных коэффициентов заполнения на распределение данных и производительность.

Тестовые сценарии

В нашем тесте мы создадим две таблицы с одним столбцом с именами _TEST1 и _TEST2. На таблице _TEST1 будет создан индекс INDEX1 с коэффициентом заполнения 100%. На таблице _TEST2 будет создан индекс INDEX2 с коэффициентом заполнения 50%. Обе таблицы содержат 8 записей.

Запуск скрипта позволит легко проверить распределение данных:

USE tempdb
GO

SELECT 
    CAST(OBJECT_NAME(S.object_id, DB_ID('tempdb')) AS VARCHAR(20)) AS 'Table Name', 
    I.name AS IndexName, 
    CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type', 
    CASE WHEN I.fill_factor IN (0,100) THEN 100 ELSE I.fill_factor END AS fill_factor, 
    CASE WHEN I.is_padded = 1 THEN 'On' ELSE 'Off' END AS 'Pad Index', 
    avg_fragmentation_in_percent AS 'Avg % Fragmentation', 
    record_count AS 'RecordCount', 
    page_count AS 'Pages Allocated', 
    avg_page_space_used_in_percent AS 'Avg % Page Space Used', 
    avg_record_size_in_bytes, 
    CASE WHEN Index_level = 0 THEN 'Leaf' ELSE 'Intermediate' END AS 'Index Level' 
FROM 
    sys.dm_db_index_physical_stats (DB_ID('tempdb'), OBJECT_ID('_TEST'), NULL, NULL, 'DETAILED') S 
INNER JOIN 
    sys.indexes I ON (I.object_id = S.object_id AND I.index_id = S.index_id)

Из вывода видно, что для таблицы _TEST1 с INDEX1 коэффициент заполнения был указан как 100%, и движок заполнил всю страницу данных. 8 записей помещаются на одну листовую страницу. С другой стороны, для таблицы _TEST2 с INDEX2 коэффициент заполнения был указан как 50%, и движок заполнил половину страниц данных. Для хранения записей и указателей на листовые страницы данных потребовались две листовые страницы и одна промежуточная страница.

Производительность выборки

Теперь давайте проанализируем производительность выборки для обеих таблиц/индексов. Мы включим опцию SET STATISTICS IO, чтобы получить информацию о вводе-выводе, необходимую для анализа разницы в производительности:

-- Производительность выборки...
PRINT 'Запуск первого оператора... с использованием INDEX 1'
SET STATISTICS IO ON;
SELECT col_1 FROM _TEST1 WHERE col_1 = 'Тестовые данные - Столбец 1'

PRINT ''

PRINT 'Запуск второго оператора... с использованием INDEX 2'
SELECT col_2 FROM _TEST2 WHERE col_2 = 'Тестовые данные - Столбец 2'
SET STATISTICS IO OFF;

Запуск вышеуказанных скриптов сгенерирует статистику, показывающую логические чтения, необходимые для каждого оператора SELECT. Как и ожидалось, так как INDEX1 (коэффициент заполнения 100%) более компактен, требовалось всего 2 логических чтения, в то время как для выборки той же самой информации из INDEX2 (коэффициент заполнения 50%) требовалось 4 логических чтения.

Разделение страниц

Теперь давайте добавим новую запись в обе таблицы и наблюдаем влияние на разделение страниц:

SET STATISTICS IO ON;
INSERT INTO _TEST1 (Col_1) VALUES ('A Тестовые данные - Столбец 1')
INSERT INTO _TEST2 (Col_2) VALUES ('A Тестовые данные - Столбец 2')
SET STATISTICS IO OFF;

Из наблюдений видно, что новая запись не поместится на ту же страницу данных для таблицы _TEST1 – INDEX1, и поэтому движок будет вынужден ввести промежуточную страницу и начать новую листовую страницу. С другой