Три мифа о коэффициенте заполнения (fill factor)

Автор: Paul Randal, A SQL Server DBA myth a day: (25/30) fill factor

Это короткая статья о мифах вокруг коэффициента заполнения (fill factor) — тему, которую я настойчиво прояснял ещё в Books Online для SQL Server 2005.

Миф №25: разные заблуждения о коэффициенте заполнения.

Все ложные

25a) коэффициент заполнения соблюдается всегда

Нет. Из Books Online:

Important:
Параметр fill‑factor применяется только при создании или перестройке индекса. Движок базы данных SQL Server не поддерживает заданный процент свободного места на страницах динамически. Попытка поддерживать дополнительное пространство на страницах данных свела бы на нет смысл коэффициента заполнения, потому что для поддержания указанного процента свободного места на каждой странице по мере вставки данных движку пришлось бы выполнять расщепления страниц.

25b) fill factor со значением 0 отличается от значения 100

Нет. Из Books Online:

Note:
Значения fill‑factor 0 и 100 во всех отношениях эквивалентны.

25c) fill factor, равный 0, оставляет свободное место на верхних уровнях индекса

Нет. Этого нет в Books Online, и я не знаю, откуда взялся этот миф, но он полностью неверен. В этом легко убедиться таким скриптом:

CREATE DATABASE foo;
GO
USE foo;
GO
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR(1000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO
SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 10000

Теперь проверьте, что коэффициент заполнения равен 0, и выполните перестройку индекса.

SELECT [fill_factor]
FROM sys.indexes
WHERE name = 't1c1'
  AND [object_id] = OBJECT_ID('t1');
GO
ALTER INDEX t1c1 ON t1 REBUILD WITH (FILLFACTOR = 100);
GO

Затем определите страницы индекса выше уровня листа и посмотрите на значение m_freeCnt в заголовке страницы — это объём свободного места на странице:

EXEC sp_allocationMetadata 't1';
GO
DBCC TRACEON (3604);
-- корневая страница (root) из вывода процедуры выше
DBCC PAGE (foo, 1, 164, 3);
GO
-- идентификатор страницы из предыдущего вывода DBCC PAGE
DBCC PAGE (foo, 1, 162, 1);
GO

Я вижу значение 10 байт — очевидно, никакого «резервирования» места не было. Это миф. Кстати, скрипт sp_allocationMetadata можно взять из той самой публикации в блоге.