[DBA][MsSQL] Index 사용량 조회 / 미사용 Index 확인

자주 쓰는 쿼리 - Index 사용량 조회

테이블 조회 효율을 위해 인덱스를 생성하여 사용하는 것은 당연하다.

하지만 시간이 지나고 업무 계획의 방향성이 바뀌다보면, 이렇게 생성하여 사용하던 인덱스들 중 사용하지 않게 되는 인덱스가 생길 수 있다.

이렇게 사용하지 않는 인덱스를 한번씩 정리를 해주게 된다면, 테이블의 DML작업에 대해 효율이 조금이나마 올라갈 수 있게 되는데, 이럴 때 도움이 될 만한 인덱스 사용량 조회 쿼리에 대해서 한번 소개해보도록 한다.

 

Query

인덱스 사용량을 조회하는 쿼리는 아래와 같다.

use [DATABASE NAME]

select db_name(s.database_id) as database_name,
       object_name(s.object_id) as table_name,
       i.name as index_name,
       s.*
  from SYS.DM_DB_INDEX_USAGE_STATS s
 inner join SYS.INDEXES i
    on s.object_id = i.object_id
   and s.index_id = i.index_id
 where s.database_id = db_id()
   and object_name(s.object_id) = '[TABLE NAME]'
 order by table_name, index_name

특정 데이터베이스의 특정 테이블에 대한 인덱스 사용량을 조회하는 쿼리이다.

해당 데이터베이스의 모든 테이블에 대해 인덱스를 조회하고 싶다면, [TABLE NAME]이 들어가는 조건을 제외하고 조회하면 된다.

 

위 쿼리에서 알 수 있는 정보들 중 몇가지 중요 정보에 대해서 간단히 소개해보도록 하겠다.

 

index_id - 인덱스의 종류 구분

index_id는 크게 3가지로 나눌 수 있다.

• 0 : Heap Table
• 1 : Clustered Index
• 2~255 : Index

먼저 0 : Heap Table이란, 클러스터 인덱스를 생성하지 않아 데이터의 저장이 Heap으로 구성되어있는 테이블을 말한다. 인덱스를 생성 한 것이 아니다보니 Index Name도 따로 없다.

 

그 다음으로는 1 : Clustered Index 이다.

클러스터 인덱스는 데이터를 지정키에 맞게 물리적으로 순서대로 저장한다는 것은 다들 알 것이다.

하나의 테이블에 한개밖에 가질 수 없는 인덱스이다. 꼭 PK가 아니여도 클러스터 인덱스 일 수 있다.

반응형

위에서 알 수 있듯, 0과 1의 ID값은 공존 할 수 없다.

 

마지막으로는 2~255 : 일반적인 Index 이다.

우리가 제일 많이 생성하고 인덱스 사용량이 궁금한 것은 대체로 이 범위에 들어가는 인덱스 일 것이다.

PK가 클러스터 인덱스가 아닐 경우 해당 값에 포함 될 수 있다.

 

index 사용량 조회

사용자의 인덱스 사용은 크게 4가지로 나누어진다. USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES 이렇게 4가지이다.

아래의 내용은 Index사용량 조회 쿼리를 해당 데이터베이스의 모든 테이블에 대해서 조회해 본 내용이다.

 

하나의 테이블에 여러가지 인덱스가 생성되어 있는 모습을 확인 할 수 있다.

이 중 USER_SEEKS / USER_SCANS 의 경우는 해당 인덱스가 사용되어 조회 된 건수를 뜻한다. 일반적인 우리의 인덱스 사용이다.

 

USER_LOOKUPS는 무엇일까? 자세히보면 Index_ID = 1 즉 클러스터 인덱스에만 건수가 올라가 있는 모습을 볼 수 있다.

lookup의 경우는 key값을 가지고 해당 데이터를 찾아가는 과정인데, MsSQL의 인덱스 구조는 Clustered Index의 값을 Index의 키 값으로 가지고 있다.

그래서 인덱스를 사용하여 데이터를 찾아가게 되면 Key Lookup 과정이 발생 하여, 클러스터 인덱스를 lookup하여 데이터를 조회하게 된다. 

즉, USER_LOOKUPS는 다른 인덱스를 통해 클러스터 인덱스의 key값으로 한번더 조회 된 값이라고 생각하면 편하다.

만약 클러스터 인덱스가 없을경우 Heap Table로 구성되어 RID값을 키 값으로 가지게되어 RID Lookup 과정이 발생하게 된다.

 

 

USER_UPDATES의 경우 쿼리 별 인덱스 업데이트 수 이다.

delete, update, insert 모두 포함이 되는데, 중요한 것은 쿼리 별 이라는 것이다. 한 동작의 dml 쿼리에 대해 1000건이 입력되어도 USER_UPDATES는 1건이 증가된다.

 

index 미사용

인덱스 미사용이란 무엇일까?

너무나 당연하겠지만 위에서 소개한 USER_SEEKS, USER_SCANS, USER_LOOKUPS의 3개의 컬럼 값이 0일 경우를 뜻한다.

 

해당 컬럼들의 값이 0일 경우 해당 인덱스에 삭제를 고려해 봐도 좋을거라 생각한다.

 

Related Posts

동적 관리 뷰 - SYS.DM_DB_INDEX_USAGE_STATS