[MSSQL] 인덱스 생성 진행률 확인하기

1. 개요

인덱스 생성 시 데이터 건수에 따라 소요시간이 매우 길어질 수 있다.

벌크 데이터 적재나 갱신같은 경우 단위처리방식으로 끊어서 진행할 수 있어서 진행률을 확인 가능하지만
인덱스의 경우에는 단위처리 방식이 불가능하여 한번 실행 시 쿼리가 멈추지 않고 계속 실행되어야 한다.

오래 수행되는 인덱스일수록 생성이 잘 진행되고 있는지, 루프를 돌며 어딘가에 에러가 발생해 멈춰있는 것이 아닌지 확인하기가 어렵다.

이런 경우 인덱스 생성 진행률을 확인하여 완료시간 또한 추정이 가능하다. (SQL Server 2014 이상에서만 작동)


2. 선행 작업

profile on 혹은 '실제 실행 계획'을 활성화 상태로 변경한다.

how to 1) 

 

how to 2) 

set statistics profile on

 

3. 쿼리 실행

dm_exec_query_profiles 는 쿼리가 실행되는 동안 실시간 쿼리 진행 상황을 모니터링 하는 동적뷰
dm_exec_requests 뷰와 조인하여 CREATE INDEX 하는 세션ID 의 쿼리 진행상황을 출력한다.

SELECT 
  eqp.node_id AS 'node_id',
  eqp.physical_operator_name AS 'physical_operator_name',
  OBJECT_NAME(eqp.object_id, eqp.database_id) AS 'object_table_name',
  SUM(eqp.estimate_row_count) AS 'Total Rows',
  SUM(eqp.row_count) AS 'Processed Rows',
  SUM(eqp.estimate_row_count) - SUM(eqp.row_count) AS 'Left Rows',
  CONVERT(DECIMAL(10,2), (MAX(eqp.last_active_time) - MIN(eqp.first_active_time))*1.0/1000) AS 'Elapsed Seconds',
 
  CASE CONVERT(DECIMAL(5,2), (SUM(eqp.row_count)*1.0)/SUM(eqp.estimate_row_count)*100)
    WHEN 0 THEN 0
    ELSE CONVERT(DECIMAL(10,2), 
                 CONVERT(DECIMAL(10,2), 
                         (MAX(eqp.last_active_time) - MIN(eqp.first_active_time))*1.0/1000) * (100 - CONVERT(DECIMAL(5,2), (SUM(eqp.row_count)*1.0)/SUM(eqp.estimate_row_count)*100) ) / CONVERT(DECIMAL(5,2), (SUM(eqp.row_count)*1.0)/SUM(eqp.estimate_row_count)*100))
  END AS 'Estimate Left Seconds',
  --(100*1.0)/((SUM(row_count)*1.0)/SUM(estimate_row_count))*1.0 AS 'Test',
  CONVERT(DECIMAL(5,2), (SUM(eqp.row_count)*1.0)/SUM(eqp.estimate_row_count)*100) AS 'Percent Complete'
FROM
  sys.dm_exec_query_profiles AS eqp WITH(NOLOCK)
  INNER JOIN sys.dm_exec_requests AS der WITH(NOLOCK)
    ON eqp.session_id = der.session_id
WHERE eqp.physical_operator_name IN ('Table Scan', 'Clustered Index Scan', 'Index Scan', 'Sort')
  AND der.command IN ('CREATE INDEX')
GROUP BY eqp.node_id, eqp.physical_operator_name, eqp.object_id, eqp.database_id
ORDER BY eqp.node_id DESC

 

4. 쿼리 결과

 

 - 각 컬럼의 의미

컬럼명	설명
Node_Id	고유 Node identity
Physical_Operator_Name	물리적 연산 명칭     테이블 스캔 ( = 클러스터 인덱스 스캔)     정렬
object_table_name	테이블 객체 명 Sort 단계는 temp에서 진행되어 Null 값
Total Rows	인덱스 생성을 위해 읽어야 하는 건수 ( = 테이블 건수)
Processed Rows	인덱스 생성을 위해 읽은 건수
Left Rows	인덱스 생성을 위해 남은 건수
Elapsed Seconds	인덱스 생성하는데 소요된 시간
Estimate Left Seconds	인덱스 생성하는데 남은 예측시간
Percenct Complete	Node별 단계 진행률

 

출처 : https://mozi.tistory.com/475