MSSQL - 인덱스 관리 [조각화]

 

▶인덱스 관리 [조각화]

- 내부 조각화 : 인덱스 페이지가 꽉 차있지 않고 비어있는 상황을 말한다. OLTP성 테이블의 인덱스라면 빈공간 확보(내부 조각화)가 필요하다. 하지만 OLAP성 테이블에서는 빈공간이 많으면 조회할 때 읽는 페이지 수가 증가하여 성능이 떨어질 수 있다.

- 외부 조각화 : 인덱스의 논리적인 순서와 물리적인 순서가 맞지 않은 경우로 성능에 도움이 되지 않으므로 제거해야 한다.

  제거 방법 : Rebuild(외부 조각화 30%이상), Reorganize(5-30%) 

- 외부 조각화와 내부 조각화 조회하는 쿼리

select a.index_id, name, avg_fragmentation_in_percent as N'외부조각화', avg_page_space_used_in_percent as N'내부조각화' from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Production.Product'), NULL,NULL,NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id and a.index_id = b.index_id

 

튜닝 사례

[문제] 다른 서버로 부터 가져온 Text file을 테이블에 넣을 때 너무 오래 걸렸다.
[원인] 클라이언트프로그램이 텍스트 파일을 열어서 하나씩 insert문으로 수행하고 있었다.
[해결] Bulk insert 사용해서 쿼리문에서 직접 텍스트파일을 읽어온다. (속도 6배향상)

 

[문제] SELECT *
[원인] 사용하지 않는 데이터 호출로 인한 부하가 생겼다.
[해결] 필요한 컬럼(date type의 byte가 적은 컬럼을 주로)을 사용하는 것이 좋다.

 

[문제] 커서와 임시테이블
[해결] 커서보다는 임시테이블, 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 좋다.
        커서로 처리하는 것은 모두 임시테이블과 테이블 변수로 처리 가능하므로 커서는 쓰지 않는다.

 

[문제] JOIN
[해결] 동일한 효과를 가지는 쿼리의 경우 INNER JOIN이 아닌 LEFT OUTER JOIN을 쓰는 경우가 있다. INNER JOIN을 써야한다.

 

[문제] 배치 작업 안에서 인덱스를 만들고 작업이 끝나면 지운다.
[해결] 불필요한 인덱스를 프로시저 안에서 만들지 않는다.

 

[문제] 디스크 조각이 심하게 나있었다.
[해결] DBCC dbreindex 혹은 DBCC indexdefrag를 주기적으로 실행하여 인덱스 단편화를 제거한다.

 

[문제] where 조건문의 변형된 column
[해결] where name + ‘’ = ‘조건’ 과 같이 왼쪽 조건을 변형하지 말고, where name = ‘’+’조건’과 같이 오른쪽에 조건선언을 해야 한다. 조건 일치를 매 로우마다 확인할 때 왼쪽 조건을 변형하면 그만큼 부하가 크다.

출처: https://elandda.tistory.com/21