[SQL Server]안 쓰이는 인덱스를 찾아보자!

이 인덱스 지워도 될까?

오랫동안 운영 되고있는 SQL SERVER를 열어보면

상당히 많은 인덱스가 있다.

처음 프로젝트를 진행할때 만들었던 인덱스 부터,

중간중간 느린 쿼리로 인해 튜닝시 만들었던 인덱스도 있고,

아직 DB를 잘 모르는 누군가가 만들어놓고 안 지운 인덱스도 있을것이다.

실제로 1년전쯤 보았던 어떤 테이블은 약 40개의 인덱스가 있는것도 보았다.

MS SQL SERVER의 경우 한개의 테이블에 999개까지 인덱스를 만들수 있고,

꼭 필요한 인덱스는 당연히 필요시 생성해야한다.

그러나,

인덱스에 해당하는 컬럼이 UPDATE, INSERT가 일어나면 그 컬럼과 관련된 인덱스를

변경하는 비용이 발생하고, 이로인해 DB에 꽤 많은 부담을 주게 된다.

그러면 사용하지 않는 인덱스는 어떻게 찾아볼수 있을까?

MS SQL SERVER는 sys.dm_db_index_usage_stats DMV에서 해당 내용을 찾아볼수 있다.

(중요 : DMV는 DB를 내릴경우 초기화 되므로 DB를 올리고 바로 조회한다면,

당연히 인덱스를 모두 사용하지 않는다고 나올것이다. 오랜기간 사용중인 DB에서 참고하기 바란다.)

이 DMV에서는 인덱스별 user_seeks, user_scans, user_lookups 컬럼이 있는데

이 컬럼이 모두 사용량이 0이라면 이 인덱스는 사용하지 않는다.

(물론 DB를 올리고 그 후의 사용량이다.)

따라서 아래와 같은 쿼리를 통해 인덱스 사용량을 알 수 있다.

SELECT TOP 10

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

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 OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND i.name IS NOT NULL

ORDER BY user_updates DESC --인덱스 UPDATE 기준 정렬

[결과]

운영시 인덱스가 과도하게 많을때 이를 참조하여 불필요 인덱스를 파악할 수 있다.

(물론 인덱스를 DROP할때는 많은 주의가 필요하다.)