미사용 인덱스 확인 쿼리

[미사용 인덱스 확인 쿼리]

 

select
    object_name( idx.object_id) as 'table',
    idx.name as 'index',
    (select rows from sysindexes where indid < 2 and id = idx.object_id ) as [rows],
    idx.index_id as 'id',
    idx.type_desc as 'type',
    icol.column_id,
    usg.user_seeks,
    usg.last_user_seek,
    usg.user_scans, 
    usg.last_user_scan,
    usg.user_lookups,
    usg.last_user_lookup,
    usg.last_user_update -- 최근데이터수정(입력) 날짜...

 

from

sys.indexes idx inner join sys.dm_db_index_usage_stats usg
on usg.object_id = idx.object_id and idx.index_id = usg.index_id and usg.database_id = db_id()
left join sys.index_columns icol on icol.index_id = usg.index_id and icol.object_id = idx.object_id
 and usg.index_id IS null
 and idx.type_desc not in ('heap','clustered')

 

where 1=1
 and idx.object_id IN (select id from sysindexes where indid < 2 --rows,
 )
 and type_desc ='NONCLUSTERED'
 and object_name( idx.object_id)='MOBILE_JOB_TB'

ORDER BY 7,9