[MSSQL] 공간인덱스 중복, 조각화 자동화

MSSQL 공간인덱스 중복으로 인해 INSERT 불가 현상이 발생했습니다.

고유 인덱스 'IDX_XXX'을(를) 포함하는 개체 'sys.extended_index_1895026578_384001'에 중복 키 행을 삽입할 수 없습니다. 중복 키 값은 (0x08141f0504, 35216993)입니다.

HOW TO FIX MSSQL GEOMETRY INDEX DUPLICATION ERROR OCCUR 

I thought It is an bug but It's not. Through out my experiments, I'm sure it's about index maintenance problem.

you can fix this by below query automation.(Run defragmentation, Run rebuild index)

 

query(Q열에 생성 쿼리 조회 됩니다.)

SELECT 
DB_NAME() AS 'DatabaseName'
, OBJECT_NAME(S.OBJECT_ID) AS 'TBL'
, I.NAME AS 'IDX'
, ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Frag'
,'ALTER INDEX '+ I.NAME +' ON '+OBJECT_NAME(S.OBJECT_ID)+' REBUILD WITH(ONLINE=OFF)' AS Q
 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S CROSS APPLY (	SELECT * 
																						FROM SYS.INDEXES I 
																						WHERE S.OBJECT_ID = I.OBJECT_ID 
																						AND S.INDEX_ID = I.INDEX_ID
																						AND I.NAME IS NOT NULL
																						) I
 ORDER BY [Frag] DESC


1. LOCK를 유발하는 REBUILD 
--DBCC DBREINDEX(TABLE_NM,채우기비율)
--ALTER INDEX ALL ON TABLE_NM REBUILD WITH(여러 옵션들)
--ALTER INDEX INDEX_NM ON TABLE_NM REBUILD WITH(여러 옵션들)

2. LOCK을 발생시키지 않는 REBUILD
--DBCC INDEXDEFRAG(DB_NM, TABLE_A, INDEX_NM)
--ALTER INDEX ALL ON TABLE_NM REBUILD WITH(ONLINE=ON)
--ALTER INDEX INDEX_NM ON TABLE_NM REBUILD WITH(ONLINE=ON)

mssql 에서 공간인덱스를 포함한 테이블에 업데이트를 수행하는 중에 아래과 같이 index 중복 에러가 발생했다.

 "고유 인덱스 'IDX_XXX'을(를) 포함하는 개체 'sys.extended_index_1895026578_384001'에 중복 키 행을 삽입할 수 없습니다. 중복 키 값은 (0x08141f0504, 35216993)입니다."

공간인덱스라서 중복으로 되어도 상관없고 다른 db에서 하나의 데이터만 업데이트했을때는

동일한 데이터라도 중복 오류가 발생하지 않는다.

 

여러 자료를 찾아 봤으나 mssql 과거버전 2008이하에서 발생하는 문제라는 의견은 있었지만,

공식적으로 버그로 리포트 되진 않은 것으로 보인다.

 (dba.stackexchange.com/questions/114938/sql-server-2012-duplicate-key-error-with-spatial-index-that-shouldnt-be-unique)

테이블 구조를 확인 하던 중에 해당 테이블에 인덱스 조각화 비율이 크게 높은 점이 발견되어

수정 처리를 아래와 같이 진행하였고, 그 결과 업데이트가 잘되었다.

ALTER INDEX IDX_XXX ON TABLE REBUILD WITH(ONLINE=OFF)

예상 원인 : 조각화가 심화됨으로 인해 인덱스 생성시 페이지 중복이 발생함

단기 조치 : 인덱스 리빌드

장기 조치 : 단편화 90% 이상인 인덱스에 대해 주기적으로 리빌드 수행 스케쥴 작성