미사용 / 쓰기 전용 인덱스 찾기

명령어

USE [Musteri];
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.name                          AS index_name,
       ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) AS reads,
       ISNULL(us.user_updates, 0)      AS writes,
       ps.used_mb,
       us.last_user_seek, us.last_user_scan,
       N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
         + N'.' + QUOTENAME(OBJECT_NAME(i.object_id)) + N';' AS drop_stmt_draft
FROM   sys.indexes i
  LEFT JOIN sys.dm_db_index_usage_stats us
         ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
  CROSS APPLY (SELECT CAST(SUM(p.used_page_count) * 8 / 1024.0 AS DECIMAL(18,1)) AS used_mb
               FROM sys.dm_db_partition_stats p
               WHERE p.object_id = i.object_id AND p.index_id = i.index_id) ps
WHERE  OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND  i.type_desc = N'NONCLUSTERED'
  AND  i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
  AND  ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) = 0
ORDER BY writes DESC, ps.used_mb DESC;

-- Usage counters since:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

하는 일

재시작 이후 읽히지 않았지만(seek/scan/lookup = 0) 여전히 쓰기 비용이 드는 nonclustered 인덱스를 크기와 함께 나열하고 DROP INDEX 초안 문장을 생성한다.

사용 시점

쓰기 중심 테이블에서 INSERT/UPDATE가 느릴 때, 저장 공간과 유지보수(rebuild, CHECKDB) 시간을 줄일 때, 또는 인덱스 통합 전.

주의

카운터는 재시작, 데이터베이스 오프라인/detach, (일부 오래된 2012/2014 빌드에서는) 인덱스 rebuild 시 초기화된다. 월말/연말 리포트에서만 쓰이는 인덱스는 짧은 구간에서는 "미사용"으로 보인다. 삭제 전에 최소 한 번의 전체 업무 주기 동안 관찰하고, AG에서는 읽기 가능 secondary의 사용량을 따로 확인한다(카운터는 replica별). unique 인덱스는 읽히지 않아도 제약을 강제하므로 제외된다. 읽기 전용 쿼리이며 안전함.