SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실

SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실

 

• Version : SQL Server 2000, 2005, 2008, 2008R2, 2012

 

Fill Factor 옵션은 SQL Server에서 인덱스를 생성 할 때 각 페이지를 얼마나 채울지 지정하는 옵션이다. 대부분의 사용자들은 Fill Factor에 대한 정의는 잘 알고 있으나 몇 가지 사실에 대해서는 많이들 오해하는 부분이 있어 간단히 정리해 보고자 한다.

 

Fill Factor 의 기본값은 100 이다?

Fill Factor의 기본값은 0 이다. 사실 0과 100은 동일한 기능으로 작동 한다. Fill Factor의 옵션을 80으로 지정하면 80%의 비율로 채우기를 한다.

 

이미 생성된 인덱스에 Fill Factor 옵션을 변경하면 자동으로 적용 된다?

Fill Factor의 비율에 따른 분할은 인덱스를 처음 생성 할 때, 인덱스를 다시 작성 할 때 적용 된다. 즉 이미 생성된 인덱스에 대해서는 옵션의 설정은 적용되나 실제 적인 분할은 다음과 같은 행위가 있을 때 발생 한다.

 

데이터 추가로 인하여 페이지 분할 시 Fill Factor의 비율 만큼 데이터를 분할 한다?

페이지 분할은 마지막 데이터를 분할해서 가는 것이 아니라 페이지의 인덱스 행 사이를 예약 한다. 즉 현재 페이지에서 반을 분할 하고 이후 데이터가 채워 질 때 80% 까지 채워 진다.

예를 들어 현재 100인 페이지가 있을 때 (현재 Fill Factor 설정은 80) 새로운 행이 추가 되었다고 가정하자. 이때 페이지 분할은 80, 20으로 나누어 지는 것이 아니라 50, 50으로 나누어 지고 분할 된 페이지에 데이터가 추가 될 때 80% 비율로 추가 된다.

 

Fill Factor 비율을 낮게 설정하면 페이지 분할 비율을 줄일 수 있어 더 빠른 성능을 얻을 수 있다?

페이지 분할에 따른 비용은 줄 일 수 있지만 일반 적인 경우에는 낮은 값으로 설정 시 인덱스 페이지에 더 많은 저장 공간이 필요하게 되어 읽기 성능이 저하 된다.

예를 들어 가득 찬 인덱스 페이지에 새 행이 추가되면 데이터베이스 엔진은 행의 절반 정도를 새 페이지로 옮겨 새 행을 위한 공간을 만든다. 이때 페이지 분할이 발생하고 이에 따른 여러 리소스 등이 사용되므로 성능이 느려진다. 그래서 50%의 비율로 인덱스를 생성했다고 가정하자. 같은 데이터를 저장하더라도 50% 밖에 사용하지 못하므로 더 많은 인덱스의 공간이 필요하다. 데이터를 읽을 때 마다 더 많은 인덱스 페이지를 읽어야 한다.

인덱스가 분할 되지 않도록 설계하는 것이 무엇보다 중요하지만 비즈니스에 맞게 비율을 잘 조절하자.

 

Fill Factor 옵션 변경 후 서비스를 재시작 해야한다?

서버 구성 옵션에서 Fill Factor 설정을 변경한 경우에는 RECONFIGURE 명령을 통해서 바로 적용 할 수 있다. 물론 서비스 재시작시에도 변경 사항이 적용 된다.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'fill factor', 100; GO RECONFIGURE; GO

 

 

[SSMS에서 Fill Factor 변경]

 

[스크립트를 이용한 변경]

 

 

인덱스와 Fill Factor의 관계는 매우 밀접하다. 가장 좋은 방법은 인덱스의 특성과 Fill Factor의 특성, 그리고 비즈니스를 잘 고려하여 페이지 분할이 일어나지 않도록 설계하여 사용 하는 것이다. 한가지 예를 들자면 입력되는 데이터에 Identity를 지정하여 항상 페이지의 맨 끝에 데이터가 저장 되도록 유도 한다면 Fill Factor비율이 100이라도 분할에 따른 성능 문제는 발생하지 않는다.

 

[참고 자료]

http://msdn.microsoft.com/ko-kr/library/ms191005(v=sql.90).aspx

http://msdn.microsoft.com/ko-kr/library/ms177459(v=sql.90).aspx

http://msdn.microsoft.com/ko-kr/library/ms189631(v=sql.90).aspx

 

 

2013-03-21 / 강성욱 / http://sqlmvp.kr