SQL Server Index Progress

MS SQL SERVER 의 인덱스 CREATE, ALTER 진행률을 알고 싶다면?

(SQL SERVER 2014 이상, AZURE SQL SERVER) 개발이나 튜닝시 SQL의 성능을 위해 인덱스를 생성, 수정한다. 인덱스를 생성시 시스템에 많은 부하를 주며, Online 옵션을 주지 않는 다면, 해당 테이블은 잠기게 된다.

특히, MS SQL Standard 버전의 경우 Online 옵션을 줄수 없기 때문에 사용자가 없는 밤이나 점심시간등에 협의를 거쳐 인덱스를 생성하곤 한다.

작은 테이블의 인덱스 생성시에는 1초내에 끝나는 경우도 있지만, 성능이슈가 생기는 큰 테이블의 경우 인덱스 생성시 몇 십분, 몇시간이 걸리는 경우도 존재한다. 9시까지 인덱스를 생성하기로 협의가 되어있는데, 8시 50분까지도 인덱스가 생성중이라면… 이걸 계속 진행 해야하는지, 지금이라도 멈춰야 하는지… 단순히 SSMS상에서는 진행률을 볼수 없기에 난감한 상황이 벌어지기도 한다.

이러한 경우 MS SQL SERVER 2014 버전부터 sys.dm_exec_query_profiles 를 이용하면 아래의 단계를 거쳐서 인덱스의 진행률을 알수 있다.

1. 인덱스를 생성할 세션에 SET STATISTICS PROFILE ON 를 활성화한다.
2. 해당 세션에서 CREATE, ALTER INDEX를 통해 인덱스를 생성, 수정 한다.
3. 해당 세션에서는 인덱스가 생성, 수정이 진행 될것이다.
4. 다른 세션에서 아래 SQL문을 실행한다.

    SELECT session_id,
    sp.cmd,
    sp.hostname,
    db.name,
    sp.last_batch,
    node_id,
    physical_operator_name,
    SUM(eqp.row_count) row_count,
    SUM(eqp.estimate_row_count) AS estimate_row_count,
    CAST(SUM(eqp.row_count)*100 AS float)/SUM(eqp.estimate_row_count) as EST_COMPLETE_PERCENT
    FROM sys.dm_exec_query_profiles eqp
    join sys.sysprocesses sp on sp.spid=eqp.session_id
    join sys.databases db on db.database_id=sp.dbid
    WHERE session_id in (select spid from sys.sysprocesses sp where sp.cmd like '%INDEX%')
    GROUP BY session_id, node_id, physical_operator_name, sp.cmd, sp.hostname, db.name, sp.last_batch
    ORDER BY session_id, node_id desc;
   

5. 해당 SQL문 실행 후 아래와 같이 인덱스의 생성률을 파악할 수 있다. (WHERE 절을 보면 알겠지만 세션ID, 다른 CMD를 조회조건에 추가 할 수 있다.)

1. 위의 상황에서 보면 약 101만 행의 CLUSTERED INDEX SCAN -> SORT(101만 행 중에 27만행) -> Index Insert 작업이 진행중인것을 알수 있다.

결 론

DB의 특성상 정확한 남은 시간은 알 수 없지만 진행률을 통해 추정이 가능하므로, 매우 큰 테이블의 인덱스 작업시 많은 도움이 될 수 있다.

참조 - sys.dm_exec_query_profiles