[DB] MSSQL 인덱스 페이지 비율설정

 

이번시간에는 인덱스 비율에 대하여 설명드리겠습니다. 일반적으로 셋팅을 하지 않는경우도 많으나 데이터읽고 쓰기 양이 많은 테이블의 경우 인덱스 비율 페이지 비율에 따라 성능이 크게 좌우될수 있습니다. 

그럼 먼저 인덱스를 생성해보도록 하겠습니다. SQL Management 툴을 이용해서 생성하도록 하겠습니다. 
테이블 -> Index -> New Index -> No-Clustered Index를 클릭합니다. 
Index Name은 IX_Name으로 하고 Add... 버튼을 클릭하여 Index Key columns은 Name항목을 선택후 OK버튼을 클릭하면 인덱스가 생성됩니다. 

GUI를 사용하지않고 스크립트문으로 생성하려면 아래 쿼리문으로 생성합니다. 

USE [TEST]
GO
 
SET ANSI_PADDING ON
GO
 
/****** Object:  Index [NonClusteredIndex-20200909-000618]    Script Date: 2020-09-09 오전 12:06:58 ******/
CREATE NONCLUSTERED INDEX [IX_NAME] ON [dbo].[TodoItem]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

채우기 비율 값은 0%에서 100% 설정가능하며 기본값은 0입니다.  0%와 100%는 동일하게 작동한다고 하며 80%으로 설정하면 80%비율값으로 채우기를 합니다. 대부분의 데이터의 경우는 등록,수정되는것보다 조회작업이 많이 이루어 지며 삽입,등록이 많은경우테이블의 경우에도 조회가 보통 10대 5정도로 많다고 합니다. 

채우기 비율 값을 적절히 선택하여 기본 테이블에 데이터가 추가될 때 인덱스 확장을 위한 충분한 공간을 제공함으로써 페이지 분할 가능성을 줄일 수 있습니다. 일반적으로 인덱스 페이지 비율을 설정하지 않을경우 SELECT문에 대한 성능은 빨라지나 데이터 양이 증가할수록  INSERT,UPDATE,DELETE관련 부분의 성능이 저하됩니다. 데이터간 변경될경우 인덱스페이 일반 적인 경우에는 낮은 값으로 설정 시 인덱스 페이지에 더 많은저장 공간이 필요하게 되어 읽기 성능이 저하 됩니다. 
저 같은경우 특정 현장에서 Insert성능이 문제가되어 특정테이블에 Lock이 걸려서 시스템 장애가 발생한적이 있었는데 채우기 비율을 80%비율로 설정후 관련부분이 해결된 적이 있었습니다.

인덱스 채우기 비율 변경방법
1.  특정인덱스 

ALTER INDEX [IX_Name] ON TodoItem
REBUILD WITH (FILLFACTOR = 80); 

2. 특정테이블 변경 
특정테이블 항목을 변경하기 위해서는 커서를 이용해서 변경하면됩니다.  테이블의 인덱스목록을 가지고 와서 커서를 돌리면서 1번항목을 입력해주면됩니다. ChatGPT에 물어보니 아래와 같이 작성하는 쿼리문을 만들어주네요.... 

DECLARE @TableName NVARCHAR(128)
SET @TableName = '테이블명'
 
DECLARE @IndexName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
 
DECLARE IndexCursor CURSOR FOR
SELECT name
FROM sys.indexes
WHERE object_id = OBJECT_ID(@TableName)
 
OPEN IndexCursor
 
FETCH NEXT FROM IndexCursor INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@TableName) +
               ' REBUILD WITH (FILLFACTOR = 80)'
    EXEC (@SQL)
PRINT @SQL
    FETCH NEXT FROM IndexCursor INTO @IndexName
END
 
CLOSE IndexCursor
DEALLOCATE IndexCursor
 

3. 서버의 모든 구성옵션을 변경방법
서버의 기본 구성값을 변경할 경우에는 sp_configure 프로시저를 이용해서 변경하면됩니다.
모든구성을 변경하는부분이기때문에 신중하게 하시기 바랍니다.

-- 비율설정방법 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
-- 80%비율로 채우기
sp_configure 'fill factor', 80;
GO
RECONFIGURE;
GO

[참고] MSDN에 상세하게 설명되어있으니 추가사항관련은 MSDN을 참고하시기 바랍니다.
docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2005/ms189631(v=sql.90)?redirectedfrom=MSDN
docs.microsoft.com/ko-kr/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-ver15 www.sqler.com/bColumn/863922