Что такое fill factor? Или когда происходит разделение страницы?sql-37

1. Что такое Fill Factor?

Fill Factor (фактор заполнения) — это параметр, определяющий, насколько процентов будет заполнена каждая страница индекса при его создании или перестроении. Оставшееся пространство резервируется для будущих вставок.

-- Создание индекса с fill factor 80%
CREATE INDEX idx_customer_name ON customers(last_name)
WITH (FILLFACTOR = 80);

Ключевые аспекты:

  • Значение от 1 до 100% (обычно 70-90%)
  • Влияет на производительность:
    • Высокий fill factor → эффективное чтение
    • Низкий fill factor → эффективные вставки/обновления
  • Применяется только при создании/перестроении индекса

2. Когда происходит разделение страницы ?

Разделение страницы — это процесс, при котором SQL Server разделяет переполненную страницу на две, когда необходимо вставить новую запись, а свободного места не хватает.

Условия для page split:

  1. Вставка новой записи в середину индекса (для clustered)
  2. Обновление записи с увеличением размера
  3. Недостаток свободного места на странице

3. Как Fill Factor влияет на Page Splits?

-- Мониторинг page splits
SELECT cntr_value AS page_splits_per_sec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Splits/sec'
AND object_name LIKE '%Buffer Manager%';

Высокий Fill Factor (90-100%):

  • Плюсы: Максимальная плотность данных, лучше для чтения
  • Минусы: Частые page splits при вставках

Низкий Fill Factor (50-70%):

  • Плюсы: Реже page splits, лучше для OLTP с частыми вставками
  • Минусы: Больше страниц → больше I/O операций

4. Оптимальные сценарии использования

Высокий Fill Factor:

  • Таблицы только для чтения
  • Data warehouse системы
  • Индексы, которые редко обновляются

Низкий Fill Factor:

  • OLTP системы с частыми вставками
  • Индексы с случайными вставками (не последовательными)
  • Таблицы с часто обновляемыми столбцами переменной длины

5. Как определить проблемы с page splits?

Мониторинг через DMV:

SELECT
    object_name(object_id) AS table_name,
    index_id,
    leaf_allocation_count AS page_splits
FROM sys.dm_db_index_operational_stats(
    DB_ID(), NULL, NULL, NULL);

Рекомендации:

  • Если > 100 splits/sec — возможна проблема
  • Сравнивайте с базовым уровнем

6. Практический пример настройки

Для таблицы с частыми вставками:

-- Перестроение индекса с оптимальным fill factor
ALTER INDEX idx_orders_date ON orders REBUILD
WITH (FILLFACTOR = 70, ONLINE = ON);

7. Дополнительные соображения

  • PAD_INDEX: Распространяет fill factor на промежуточные страницы индекса
  • Автоматическое управление: В SQL Server 2019+ можно использовать automatic tuning
  • Фрагментация: Частые page splits приводят к фрагментации индекса

Резюмируем: Fill Factor — это важный параметр настройки производительности, который балансирует между эффективностью хранения данных и стоимостью операций модификации. Разделение страниц — неизбежный процесс при работе с индексами, но правильная настройка fill factor помогает минимизировать его негативное влияние.

Вопросы

Собеседования