[MSSQL] 인덱스 생성 진행률 확인하는 방법

개요

---

데이터 건수가 많아질수록 인덱스 생성시간은 오래 소요됩니다.

 

벌크 데이터 적재나 갱신같은 경우 단위처리방식으로 끊어서 진행할 수 있어서 진행률을 확인 가능하지만

인덱스의 경우에는 단위처리 방식이 불가능합니다.

 

즉, 한번 실행하면 완료될때까지 쿼리가 계속 실행되어야 합니다.

오래수행되는 인덱스는 생성이 잘 진행이 되고 있는지, 어디에서 멈춰있는 것은 아닌지 알기가 힘듭니다.

 

이런 경우 인덱스 생성 진행률을 확인하여 완료시간또한 추정할 수 있습니다. (SQL Server 2014 이상)

단, 이 진행률을 알기 위해서는 선행조건이 필요합니다.

 

 

선행조건

---

profile on 혹은 '실제 실행 계획' 활성화가 필요합니다.

아래 방법 둘 중 하나를 적용해 줍니다.

 

SSMS 창에서 빨간 네모박스를 활성화 합니다.

profile 옵션을 활성화 합니다.

set statistics profile on

 

 

쿼리 결과

---

아래의 쿼리를 수행하면 다음 결과를 확인할 수 있습니다.

컬럼의 의미는 다음과 같습니다.

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

 

 

쿼리

---

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
 

 

 

 

 

 

SQL Server 인덱스 생성 진행률

SQL Server 인덱스 생성 진행 퍼센트

MSSQL 인덱스 생성 진행률

MSSQL 인덱스 생성 진행 퍼센트