Мифы о коэффициенте заполнения (fill factor)

Автор: Paul Randal, A SQL Server DBA myth a day: (24/30) twenty six restore myths

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

Миф №25: различные мифы о коэффициенте заполнения (fill factor).

Все они ЛОЖНЫ

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

Нет. Из Books Online:

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

25б) коэффициент заполнения 0 отличается от коэффициента заполнения 100

Нет. Из Books Online:

Примечание: значения коэффициента заполнения 0 и 100 полностью одинаковы во всех отношениях.

25в) коэффициент заполнения 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);
 
-- корневая страница, из вывода хранимой процедуры
DBCC PAGE (foo, 1, 164, 3);
GO
 
-- идентификатор страницы в выводе DBCC PAGE выше
DBCC PAGE (foo, 1, 162, 1);
GO

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