'SET STATISTICS PROFILE ON' 옵션을 사용하여 인덱스 생성 모니터링

MS SQL Server에서 인덱스 생성 진행 상태 모니터링하기 : SET STATISTICS PROFILE ON 활용

특히 대용량 테이블에 인덱스를 생성할 때 시간이 오래 걸리다 보니 "지금 어느 정도 진행됐는지" 궁금할 때가 많습니다. 이런 상황에서 `SET STATISTICS PROFILE ON` 옵션을 활용하면 실행 계획과 함께 진행 상태를 어느 정도 파악할 수 있습니다. 

 1. `SET STATISTICS PROFILE ON`이란?

`SET STATISTICS PROFILE ON`은 SQL 쿼리의 실행 계획(Execution Plan)을 텍스트 형태로 출력해주는 옵션입니다. 일반적으로 쿼리 최적화나 성능 분석에 사용되지만, 인덱스 생성 같은 작업에서도 내부적으로 어떤 단계가 진행 중인지 힌트를 얻을 수 있습니다. 이 설정을 켜면 쿼리 실행 후 각 작업 단계별로 통계 정보(예: 행 수, 실행 횟수 등)를 확인할 수 있어요.

 

2. 인덱스 생성 모니터링 예제

대용량 테이블에 인덱스를 생성한다고 가정해 봅시다. 예를 들어, `Sales`라는 테이블에 `OrderDate` 열을 기준으로 비클러스터드 인덱스를 만들고 진행 상태를 확인하려면 아래와 같은 스크립트를 사용하면 됩니다.

-- 통계 프로필 활성화
SET STATISTICS PROFILE ON;

-- 인덱스 생성
CREATE NONCLUSTERED INDEX IDX_Sales_OrderDate 
ON dbo.Sales (OrderDate);

-- 통계 프로필 비활성화
SET STATISTICS PROFILE OFF;

이 쿼리를 SQL Server Management Studio(SSMS)에서 실행하면 결과 창에 두 개의 탭이 나타납니다:
- Messages: 인덱스 생성 완료 메시지
- Results: 실행 계획과 통계 정보

 

3. 실행 계획에서 무엇을 볼 수 있나?

`SET STATISTICS PROFILE ON`을 사용하면 결과로 나온 실행 계획에서 다음과 같은 정보를 확인할 수 있습니다:
- Rows: 처리된 행 수
- Executes: 작업이 실행된 횟수
- StmtText: 실행된 내부 작업 단계 (예: 데이터 정렬, 인덱스 빌드 등)

예를 들어, 인덱스 생성 중 데이터 정렬(Sort) 단계가 진행 중이라면 `StmtText` 열에 `Sort` 작업이 표시되고, `Rows` 열에서 현재까지 처리된 행 수를 볼 수 있습니다. 대용량 데이터의 경우 이 정보로 진행률을 대략적으로 가늠할 수 있죠.

 

4. 더 정확한 진행률을 알고 싶다면?

사실 `SET STATISTICS PROFILE ON`은 진행률을 퍼센트 단위로 보여주지는 않습니다. 단지 실행 계획을 통해 내부 작업 단계를 추적할 뿐이죠. 만약 더 구체적인 진행률(예: 50% 완료)을 확인하고 싶다면, 다음과 같은 방법을 병행할 수 있습니다:
- DMV 사용: `sys.dm_exec_requests`와 `sys.dm_exec_sql_text`를 조합해 현재 실행 중인 쿼리의 진행 상황을 모니터링하세요. 아래 쿼리를 주기적으로 실행해 보세요.

SELECT 
    r.percent_complete, 
    r.estimated_completion_time, 
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%CREATE NONCLUSTERED INDEX%';

- `percent_complete` 열을 통해 진행률을 퍼센트로 확인할 수 있습니다.

 

5. 주의사항

- 성능 영향: `SET STATISTICS PROFILE ON`은 추가적인 오버헤드를 발생시키므로 프로덕션 환경에서는 신중히 사용하세요.
- 제한점: 이 옵션은 인덱스 생성의 세부 진행률을 실시간으로 보여주기보다는 작업 단계를 확인하는 데 더 유용합니다.

 

6. 결론

MS SQL Server에서 인덱스 생성은 테이블 크기에 따라 시간이 꽤 걸릴 수 있는 작업입니다. `SET STATISTICS PROFILE ON`을 활용하면 실행 계획을 통해 작업의 내부 단계를 파악할 수 있고, DMV를 추가로 사용하면 진행률까지 확인할 수 있어요. 두 방법을 조합하면 대용량 인덱스 생성 작업을 훨씬 더 효과적으로 관리할 수 있습니다.