안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 인덱스 생성과 관리 - 인덱스 재구성/재생성입니다.

TL;DR

ALTER INDEX 구문으로, 인덱스 재구성과 재생성 방법을 설명합니다. 파편화율을 기준으로 재구성과 재생성을 결정하며, 실행 방법과 예제 쿼리 및 쿼리 최적화 통계 데이터 확인 방법을 소개합니다.

인덱스 재구성(Reorganize) / 재구축(Rebuild)

인덱스를 재구성/재구축하는 방법은 ALTER INDEX 구문에서 재구성 - REORGANIZE 명령과 재구축 - REBUILD 명령을 사용할 수 있습니다. (DBCC DBREINDEX, DBCC INDEXDEFRAG 명령은 차기 SQL Server에서 지원되지 않을 예정이니 사용하지 마시고 ALTER INDEX를 사용하세요.)

재구성 - REORGANIZE  vs 재구축 - REBUILD 

인덱스 재구성 방법 두 가지를 비교하기 전에, 먼저 평균 파편화를 구해야 합니다. 

-- Microsoft 공식 가이드
--https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-2017#a-return-information-about-a-specified-table
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'SQLERTestDB');  --DB명
SET @object_id = OBJECT_ID(N'SQLERTestDB.dbo.IndexDummy');  --테이블명

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT DB_NAME(database_id), OBJECT_NAME(object_id), avg_fragmentation_in_percent 
    FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')
END;
GO

-- 또는 아래 방법 수행 - https://stackoverflow.com/a/45439047
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc
GO

이렇게 수행해 인덱스의 파편화율을 구합니다. 파편화율에 따라 재구성과 재구축을 결정합니다.

• 파편화율이 10% 이하면 아무 작업 하지 않음
• 10~30% 사이면 REORGANIZE 수행
• 30% 이상이면 REBUILD 수행 

그럼, 재구성과 재구축 SQL 쿼리 구문을 살펴보겠습니다. 

REORGANIZE 실행

REORGANIZE는 인덱스 리프 레벨을 재구성합니다. 온라인으로 수행되고 REBUILD에 비해 가볍습니다. 인덱스를 재구성하는 REORGANIZE은 아래 방식으로 실행합니다.

-- 한 테이블의 특정 인덱스를 REORGANIZE
ALTER INDEX idx_id ON IndexDummy REORGANIZE;

-- 한 테이블의 전체 인덱스를 REORGANIZE
ALTER INDEX ALL ON IndexDummy REORGANIZE;

-- 모든 테이블의 모든 인덱스를 REORGANIZE
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REORGANIZE';

REBUILD 수행

인덱스를 재생성합니다. 시스템 리소스를 많이 사용할 수 있으며 다른 프로세스의 쿼리 속도를 저하시킬 수 있으니 신중하게 사용하세요. 사용자가 적은 시간대를 택해 유지관리 작업으로 수행하실 것을 권장합니다.
인덱스를 재생성하는 REBUILD 구문은 아래처럼 실행합니다.
 

-- 한 테이블의 특정 인덱스를 REBUILD
ALTER INDEX idx_id ON IndexDummy REBUILD;

-- 한 테이블의 모든 인덱스를 채우기 비율과 함께 REBUILD 
ALTER INDEX ALL ON IndexDummy REBUILD WITH (FILLFACTOR = 80);

-- 모든 테이블의 모든 인덱스를 REBUILD
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD';

데이터베이스의 모든 테이블 인덱스를 파편화율로 자동 재구성/재생성 수행

아래 쿼리를 이용해 모든 테이블과 모든 인덱스에 자동 처리 가능합니다. SQL Server의 유지관리 작업으로 설정해 일정 주기마다 실행되도록 구성하면 유용합니다.
파편화율 10% 이상인 테이블을 #work_to_do 테이블에 저장하고, 커서를 사용해 #work_to_do의 개별 테이블의 파편화율을 조사해 10~30%면 재구성, 그 이상이면 재생성을 수행합니다.

-- Microsoft  가이드 문서 참조 - https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-2017#a-return-information-about-a-specified-table
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

쿼리 최적화 통계 데이터(Query optimization statistics data)

다음으로 쿼리 최적화 통계 데이터입니다. 먼저 다음 질의를 봐 보도록 하지요.

-- 통계 데이터 보기
DBCC SHOW_STATISTICS(IndexDummy,idx_id)

--통계 데이터 UPDATE (자동 업데이트. 불필요 작업)
UPDATE STATISTICS IndexDummy(idx_id)

이전 강좌에서 몇 번 말씀드린 대로, “통계에 따라서 SQL Server 쿼리 최적화기(Query optimizer)가 인덱스를 타게 할지 여부를 결정한다”라고 말씀드렸습니다. 이때 작업 근거가 되는 정보가 바로 테이블 통계 데이터입니다.

SQL Server는 테이블 통계 데이터를 인덱스와 함께 생성하고 자동으로 유지합니다.  수동으로 통계 업데이트 설정도 가능하지만, 데이터베이스 튜닝 요소로 볼 때 수동 진행은 권장하지 않습니다. 만약, 수동으로 설정할 경우 많은 모니터링이 필요합니다.

인덱스에 대해 좀 더 깊이 있는 공부나 SQL Server 쿼리 최적화기의 세밀한 제어, 여러 시스템 카탈로그 정보 분석을 원하시면 이런 통계 데이터 관련 정보를 참고해 보시길 바랍니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022