Fill Factor

Fill Factor는 Index Data의 채우기 비율을 말한다.
이게 무슨 이야기냐 하면 SQL Server의 데이터는 PAGE라는 개념에 저장이 된다.
PAGE에 저장할 수 있는 용량은 약 8000byte 정도가 된다.
("약 8000 byte" 이라고 표현한 이유는 Heap 데이터, 클러스터인덱스, 넌클러스터 인덱스 구조냐에 따라서 실제 저장 용량이 차이가 난다.)

실제 계산을 해보자.

CREATE TABLE Table1
(
    A    INT            -- 4 byte
,    B    CHAR(900)        -- 900 byte
)
GO
이 테이블은 Row당 904 byte를 차지한다.
그렇다면 한 개 PAGE에는 8개(904 * 8 = 7232 byte)의 Row가 들어간다.

INSERT INTO Table1 VALUES( 1, 'A1' )    -- 1
INSERT INTO Table1 VALUES( 2, 'B2' )    -- 2
INSERT INTO Table1 VALUES( 3, 'C3' )    -- 3
INSERT INTO Table1 VALUES( 4, 'D4' )    -- 4
INSERT INTO Table1 VALUES( 5, 'E5' )    -- 5
INSERT INTO Table1 VALUES( 6, 'F6' )    -- 6
INSERT INTO Table1 VALUES( 7, 'G7' )    -- 7
INSERT INTO Table1 VALUES( 8, 'H8' )    -- 8    (1 PAGE)
INSERT INTO Table1 VALUES( 9, 'I9' )    -- 9    (2 PAGE)

확인 하는 방법은 다음과 같다.

 

-- Index 번호 확인

SELECT * FROM sys.indexes

WHERE object_id = OBJECT_ID('Table1')

GO

Index_id : 0 – Heap
Index_id : 1 – 클러스터 인덱스
Index_id : 2 이상 – 넌클러스터 인덱스

-- 페이지 번호 확인

-- DBCC IND('[데이터베이스명]', '[테이블명]', [인덱스번호])

DBCC IND('TestDB', 'Table1', 0)

GO

8번까지는 PagePID 548이 생성되고 9번부터 552가 생성된다.

 

-- 페이지 내용 확인

DBCC TRACEON(3604)

GO

 

-- DBCC PAGE('[데이터베이스명]', [파일번호], [페이지번호], [출력옵션])

DBCC PAGE('TestDB', 1, 548, 3)

Heap 구조는 인덱스가 없는 경우이므로 Fill Factor를 설정 할 수 없다
테이블을 삭제하고 다시 테이블을 만들고 인덱스를 만들자.

CREATE INDEX IX_Table1_B ON Table1(B)
GO

-- Index 번호 확인

SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('Table1')
GO
index_id 2가 추가 된 것을 확인 할 수 있다.


이제 9번까지 INSERT를 하고 PAGE를 보면 3개의 PAGE가 생성된 것을 볼 수 있다.

Indexl Level이 0인 것이 실제 데이터가 들어 있는 Leaf Node가 된다.

그러나 모든 데이터가 인덱스 정렬에 맞게 순차적으로 들어 갈 수는 없다.
만약 'A2'라는 데이터를 넣게 된다면 552 PageID에 데이터를 넣어야 하지만 데이터가 가득 차 있어서 데이터를 넣을 수 없는 경우가 발생된다.
실제 어떤 일이 일어나는지 확인해 보기 위해서

INSERT INTO Table1 VALUES( 2, 'A2' )    -- 1-1
을 넣어 보자.


PageID 557이 추가된 것을 확인 할 수 있다.

실제 페이지 데이터의 변경 내역을 보면


루트 노트에 Leaf 노드의 내용이 추가 되고 552 Page의 데이터가 절반으로 잘려서 557 Page가 추가로 생성이 되었다.
이걸 페이지 분할이라고 한다.

모든 페이지는 기본적으로 더 이상 데이터를 넣을 수 없는 경우 페이지 분할이 발생된다.
여기서 발생되는 문제가 페이지 분할시 디스크에서 신규 페이지 영역을 할당받고 기존 데이터의 반을 다른 페이지에 넣고
하는데 SQL Server에 영향을 미치게 된다.

그래서 나온 것이 Fill Factor이다.(서론이 너무 길었다.)
Fill Factor의 개념은 미리 Index데이터를 정렬한다는 의미이다.
말 보다는 실전으로 데이터를 넣어 보자.

테이블을 만들고 데이터를 넣어 보자.(인덱스는 만들지 않는다.)
그러면 Heap 구조로 데이터가 들어가 있을 것이다. 그리고 IX_Table1_B를 만들어 보자.
이렇게 구성하면 첫 번째 페이지에 데이터가 가득 들어가 있는 형태가 나온다.
이러면 페이지 분할이 발생하므로 Index를 구성할 때 Fill Factor 옵션을 주어서 구성을 해보자

CREATE INDEX IX_Table1_B1 ON Table1(B)
WITH FILLFACTOR = 50                    -- Fill factor : 50%
GO



페이지 번호를 보고 페이지 실제 데이터를 보면 데이터들이 페이지 용량의 50%만 들어 있는 것을 확인 할 수 있다.

 

Fill Factor가 구성된 데이터는 다음 페이지 분할 시 어떻게 될지가 또 궁금할 것이다.

인터넷의 글들을 보게 되면 다음 분할시에도 Fill Factor에 맞추어서 분할 된다고 되어 있으나 실제로는 그렇지 않고 50%:50% 으로 페이지가 분할 된다.

CREATE INDEX IX_Table1_B2 ON Table1(B)
WITH FILLFACTOR = 20                    -- Fill factor : 20%
GO

로 테스트를 해보자

 

INSERT INTO Table1 VALUES( 1, 'A2' )
GO 6
INSERT INTO Table1 VALUES( 1, 'A1' )
GO
실행하면 페이지 분할이 일어난다.

565 PageId가 분리되어서 575가 생성됨.

분할된 페이지 Fill Factor가 20%이지만 50%:50%으로 분리된다.




그렇다면 Fill Factor의 퍼센트로 구성되는 시점은 언제일까?
인덱스를 생성하거나 인덱스를 Rebuild 하는 경우에 Fill Factor 비율에 맞게 인덱스가 정렬된다.
그러므로 Fill Factor가 시스템에 영향을 준다고 판단된다면 자주 인덱스를 Rebuild 해야 한다.

Fill Factor는 기본으로 0% 채우기 비율을 가지고 있다. (0%은 100%의 채우기 비율과 거의 동일한 의미를 가짐.)

조정하는 방법은 다음과 같다.

 

-- Fill Factor 설정
sp_configure 'show advanced options', 1;

GO

sp_configure 'fill factor', 0;

GO

RECONFIGURE;

GO

 

-- Fill Factor 조회
SELECT    DB_NAME()                AS DatabaseName
    ,    SCHEMA_NAME(o.Schema_ID)    AS SchemaName
    ,    OBJECT_NAME(s.[object_id])    AS TableName
    ,    i.name                AS IndexName
    ,    i.fill_factor            AS Fill_Factor
FROM    sys.dm_db_index_usage_stats s
        INNER JOIN sys.indexes i
        ON    s.[object_id] = i.[object_id]
            AND s.index_id = i.index_id
        INNER JOIN sys.objects o
        ON    i.object_id = O.object_id
WHERE    s.database_id = DB_ID()
        AND i.name IS NOT NULL
        AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY    fill_factor DESC