SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션

SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션

 

• Version : SQL Server 2014

 

SQL Server에서 인덱스를 다시 작성하면 인덱스가 삭제된 다음 다시 생성된다. 인덱스가 생성되는 과정에서 조각화가 제거되고 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고 인덱스 행을 연속된 페이지로 다시 정렬할 수 있다. 익스텐트가 128개 이상인 인덱스를 다시 작성하면 데이터베이스 엔진에서 실제 페이지 할당 취소와 해당 관련 잠금이 트랜잭션 커밋후까지 지연된다. 인덱스의 크기가 작은 경우에는 혼합 익스텐트에 저장되기 때문에 리빌드 후에도 조각화가 줄어들지 않는 경우가 있다.

 

• ALTER INDEX : https://msdn.microsoft.com/ko-kr/library/ms188388(v=sql.120).aspx

 

SQL Server 2014에서 온라인 인덱스 리빌드시 처리 과정에서 SCH-M 잠금과 SCH-S 잠금을 처리해야 하기 때문에 블록이 발생한다. SCH-M 잠금은 다른 잠금 때문에 차단 시스템 둔화의 원인이 되는데 테이블에 잠금이 부여되는 다른 모든 프로세스와 호환되지 않는다. SQL Server 2014 Enterprise에서 온라인 인덱스 리빌드시 잠금 우선순위 매커니즘을 소개한다.

 

실습을 위해 테스트 테이블을 생성한다. 실습에서는 SQL Server Enterprise 또는 SQL Server Developer 에디션을 사용해야 한다.

-- Create a table with primary key create table randomdata ( id int identity, randomNumeric numeric (18,12), randomDatetime datetime2, randomVarchar varchar(100) ); create clustered index cl_idx_id on randomdata(id)   --fill the table with data--on my machine this took just under 2 minutes. set nocount on declare @loopcounter int = 1 declare @randomNumeric numeric(18,12) declare @randomDatetime datetime2 declare @randomVarchar varchar(1000)     declare @randomString VARCHAR(1000) set @randomString = 'zi g upi vsm trsf yjod o jsbr vpmvrtmd eoyj jpe ;pmh upi br nrrm eptlomh pm upit vp,[iyrt/ [rtsj[d oyd yo,r pgt s bsvsyopm/ mpy vtoyovodomh/ kidy pbrtdtbomh js[[u gsvr'      while @loopcounter < 1000000     begin         set @randomNumeric = round(rand() * 100,10)         set @randomDatetime = dateadd(minute,@randomnumeric,getdate())         set @randomVarchar = SUBSTRING(@randomString,convert(int,@randomNumeric),100 )         insert randomdata             ( randomNumeric, randomDatetime, randomVarchar)             values             (@randomNumeric, @randomDatetime, @randomVarchar)         set @loopcounter = @loopcounter + 1 end

 

온라인 인덱스 리빌드 동작을 시뮬레이션 하기위해 SSMS에서 3개의 다른 창을 생성하고 각각의 스크립트를 추가한다.

--Window 1 begin transaction     select top 1000 * from randomdata with (holdlock)   --commit --Commented out on purpose so the transaction holds a lock on the table

--Window 1 begin transaction     select top 1000 * from randomdata with (holdlock)   --commit --Commented out on purpose so the transaction holds a lock on the table

--Window 3 select top 10 * from randomdata

 

쿼리창1에서 스크립트를 실행하여 트랜잭션을 시작한다. 커밋 구문은 잠금 동작을 입증하기 위해서 주석처리한다. 그리고 쿼리창3을 실행하면 쿼리창1에서 트랜잭션이 실행중임에도 불구하고 쿼리3에서 데이터가 조회되는 것을 확인할 수 있다. 쿼리창1의 SPID로 sp_lock을 조회해보면 IS 및 S 잠금을 확인할 수 있다.

 

SSMS에서 쿼리창2를 실행하여 온라인 인덱스 리빌드를 실행한다. (현재 쿼리창1에서는 트랜잭션이 그대로 유지되고 있다.) 다른 창을 실행하여 현재 실행중인 쿼리2의 SPID에 대한 잠금 정보를 조회한다. 스키마 잠금을 보유하고 있지만 다음 잠금을 위한 변화를 기다리는 동안 인덱스 작업은 계속 된다.

 

기존의 인덱스가 교체될 작업이 끝나면 SCH-M 잠금을 획득해야 다음 인덱스를 다시 작성하는 과정을 진행하고 SQL Server는 SCH-S 잠금을 유지한다. 잠금 호환성에 대한 표는 마이크로소프트 웹사이트를 참고한다.

• Lock Compatibility : https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx

 

 

현재 쿼리창1에서 홀드 잠금이 진행중인 상태에서 쿼리창2의 온라인 인덱스 리빌드 작업이 진행 중이다. 온라인 인덱스 리빌드 작업이 SCH-M 잠금을 획득한 상황에서 쿼리창3을 실행하면 이전과 달리 데이터가 조회되지 않는다. sp_who2를 사용하여 쿼리창3의 SPID를 조회하면 쿼리창2의 SPID에 의해 차단된 것을 확인할 수 있다.

 

쿼리창1에서 현재 트랜잭션이 실행되고 있는 프로세스 커밋이 완료되면 쿼리창2의 온라인 인덱스 리빌드 작업이 다음 잠금을 획득하여 프로세스를 완료할 수 있다. 그리고 잠금으로 인해 대기하고 있던 쿼리창3도 데이터조회가 완료 된다.

 

SQL Server 2014 엔터프라이즈 버전에서는 온라인 인덱스 리빌드시 잠금에 관한 우선순위를 적용할 수 있다.

WAIT_AT_LOW_PRIORITY	온라인 인덱스 리빌드는 다른 잠금요청보다 낮은 우선순위로 대기
MAX_DURATION	분단위로 기다리는 시간
ABORT_AFTER_WAIT	MAX_DURATION이 충족된 후에 조취를 결정 None : 이전 동작을 따라 잠금 큐를 입력 Self : 온라인 인덱스 리빌드 작업 중지 Blockers : 블록킹 SPID를 강제 종료하고 온라인 인덱싱을 진행

 

온라인 인덱스 리빌드시 잠금 우선순위를 사용하기 위해 쿼리창2의 스크립트를 아래 스크립트로 변경한다. 변경된 스크립트는 최대 1분을 기다린 후 다음 작업을 수행한다.

--Window 2 alter index cl_idx_id on randomdata rebuild with (ONLINE = ON(WAIT_AT_LOW_PRIORITY         (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

 

이전과 같이 쿼리창1, 쿼리창2, 쿼리창3을 실행하면 이전과 다른 동작을 확인할 수 있다. 쿼리창2에 적용된 새로운 잠금 우선순위로 낮은 잠금 우선위로 실행중인 쿼리창3이 실행되고 쿼리창2의 작업은 일정 시간이 지난뒤 중지된다.

 

쿼리창2의 스크립트 옵션을 블록킹 세션을 강제로 종료 후 실행하도록 수정한 다음 실행해보자.

--Window 2 alter index cl_idx_id on randomdata rebuild with (ONLINE = ON(WAIT_AT_LOW_PRIORITY             (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS )), fillfactor = 80)

 

 

우선순위 옵션에서 MAX_Duration = 0을 사용했을 경우 아래와 같은 오류가 나타났다.

--Window 2 alter index cl_idx_id on randomdata rebuild with (ONLINE = ON(WAIT_AT_LOW_PRIORITY             (MAX_DURATION = 0 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

 

 

SQL Server에서 트랜잭션의 잠금 관리는 매우 중요하다. 잠금 옵션의 사용으로 유지보수시 효율적인 작업을 진행 할 수 있도록 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4085/using-sql-server-managed-lock-priority-for-online-index-rebuilds/

 

2015-11-26 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, SQL 2014, 온라인 인덱스 리빌드, Online Index Rebuild, 인덱스 재구성, 인덱스 조각화