[미사용 인덱스 확인 쿼리]
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
'Database > MS-SQL' 카테고리의 다른 글
| sql 툴사용 (2) | 2016.08.10 |
|---|---|
| 간단하게 페이징 처리하기 (MS-SQL 2012버전부터 사용가능) (0) | 2015.09.10 |
| 테이블 리네임처리(sp_rename 사용) (0) | 2014.06.24 |
| 데이터베이스 복원하기 스크립트 (0) | 2014.01.28 |
| 달력만들기 쿼리 (0) | 2013.05.15 |
