SQL Server에서 데이터베이스 최적화를 위해서는 각 테이블의 인덱스를 잘 관리하는 것이 중요합니다. 인덱스는 데이터 검색 성능을 향상시키는 핵심 요소로, 쿼리 성능을 높이기 위해선 전체 테이블의 인덱스를 주기적으로 확인하는 것이 필요합니다. 이번 글에서는 MSSQL에서 전체 테이블 인덱스를 확인하는 쿼리와 그 결과를 해석하는 방법을 소개합니다.
전체 테이블 인덱스 확인 쿼리
아래는 MSSQL에서 전체 테이블의 인덱스를 확인할 수 있는 쿼리입니다. 이 쿼리는 테이블 정보와 인덱스 정보를 함께 제공하여, 각 테이블의 데이터 크기, 인덱스 유형, 인덱스 크기, 마지막 사용 일자 등을 확인할 수 있도록 합니다.
/*
b_name부터 update_ymd까지가 테이블에 대한 정보이며
idx_type부터 idx_col_name까지가 해당 테이블의 개별 인덱스들에 대한 정보
테이블 정보만 조회하고자 할 경우 4번째 줄부터 시작하는 서브쿼리만 실행.
total_size_mb : 데이터 및 인덱스 포함 모든 크기
2nd_idx_cnt : 테이블데이터가 포함되어 있는 clustered index를 제외한 2차 인덱스들의 개수
2nd_idx_size_mb : 테이블데이터가 포함되어 있는 clustered index를 제외한 2차 인덱스들의 크기
update_ymd : 테이블 데이터 마지막 변경일(insert,update,delete)
idx_type : 1(clustered), 2(non clustered)
idx_col_name : 복합 인덱스의 경우 컬럼 순서대로 콤마로 구분해서 대상 컬럼명 표시
last_user_use : insert나 update, delete가 아닌 조회에(scan, seek, lookup)에 사용된 마지막 일자
*/
select a.db_name, a.schema_name, a.tbl_name, a.tbl_type, a.row_cnt, a.total_size_mb, a.[2nd_idx_cnt], a.[2nd_idx_size_mb], a.create_ymd, a.update_ymd
, b.idx_type, b.is_primary_key, b.is_unique, b.idx_size_mb, b.idx_name, b.last_user_use_ymd, b.idx_col_cnt, b.idx_col_name
from (
select db_name(db_id()) as db_name
, (select name from sys.schemas with(nolock) where schema_id = a.schema_id) as schema_name
, a.name as tbl_name
, case when min(b.index_id) = 0 then 'heap' else 'clustered' end as tbl_type
, sum(case when (b.index_id < 2) then b.row_count else 0 end) as row_cnt
, cast(sum(b.used_page_count) * 8.0 / 1024.0 as numeric(36, 2)) as total_size_mb
, sum(case when b.index_id > 1 then 1 else 0 end) as [2nd_idx_cnt]
, cast(sum(case when b.index_id < 2 then 0 else b.used_page_count end) * 8.0 / 1024.0 as numeric(36, 2)) as [2nd_idx_size_mb]
, convert(varchar(10), a.create_date, 120) as create_ymd
, convert(varchar(10), (select max(last_user_update) from sys.dm_db_index_usage_stats with(nolock) where object_id = a.object_id and database_id = db_id()), 120) as update_ymd
, db_id() as db_id
, a.schema_id
, a.object_id
from sys.tables a with(nolock)
join sys.dm_db_partition_stats b with(nolock) on a.object_id = b.object_id
group by a.schema_id, a.object_id, a.name, a.create_date
) a
left join (
select a.name as tbl_name
, b.type as idx_type
, b.is_primary_key
, b.is_unique
, b.index_id
, (
select sum(used_page_count)
from sys.dm_db_partition_stats with(nolock)
where object_id = b.object_id
and index_id = b.index_id
) idx_size_mb
, b.name as idx_name
, convert(varchar(10), case when isnull(c.last_user_seek, '1900-01-01') > isnull(c.last_user_scan, '1900-01-01')
and isnull(c.last_user_seek, '1900-01-01') > isnull(c.last_user_lookup, '1900-01-01') then isnull(c.last_user_seek, '1900-01-01')
when isnull(c.last_user_scan, '1900-01-01') > isnull(c.last_user_lookup, '1900-01-01') then isnull(c.last_user_scan, '1900-01-01')
else isnull(c.last_user_scan, '1900-01-01')
end
, 120
) as last_user_use_ymd
, (select count(*) from sys.index_columns with(nolock) where object_id = b.object_id and index_id = b.index_id) as idx_col_cnt
, stuff((
select ',' + bb.name
from sys.index_columns aa with(nolock)
join sys.columns bb with(nolock) on aa.object_id = bb.object_id
and aa.column_id = bb.column_id
where aa.object_id = b.object_id
and aa.index_id = b.index_id
order by aa.key_ordinal
for xml path('')
), 1, 1, ''
) as idx_col_name
, a.object_id
from sys.tables a with(nolock)
join sys.indexes b with(nolock) on a.object_id = b.object_id
and b.type in ('1', '2')
left join sys.dm_db_index_usage_stats c with(nolock) on b.object_id = c.object_id
and b.index_id = c.index_id
and c.database_id = db_id()
) b on a.object_id = b.object_id
order by a.total_size_mb desc, b.idx_col_name;
쿼리 설명
이 쿼리는 데이터베이스의 전체 테이블 정보를 조회하며, 각 테이블에 대한 인덱스와 인덱스별 정보를 함께 제공합니다.
- 테이블 정보:
- total_size_mb: 데이터 및 인덱스를 포함한 테이블 크기 (MB)
- 2nd_idx_cnt: 2차 인덱스 개수
- 2nd_idx_size_mb: 2차 인덱스 크기 (MB)
- update_ymd: 마지막 데이터 변경일 (Insert, Update, Delete)
- 인덱스 정보:
- idx_type: 인덱스 유형 (1: Clustered, 2: Non-Clustered)
- idx_col_name: 인덱스 컬럼명 (복합 인덱스의 경우 콤마로 구분)
이 쿼리를 실행하면 데이터베이스 전체 테이블의 인덱스 상태를 파악할 수 있습니다. 성능 최적화를 위해 주기적으로 이 쿼리를 활용하여 테이블 인덱스 상태를 점검해 보세요.
SQL Server 인덱스 최적화의 중요성
인덱스는 대규모 데이터베이스에서 데이터를 빠르게 검색하기 위한 핵심 요소입니다. 주기적으로 인덱스 상태를 점검하고, 비효율적인 인덱스를 재구성하면 쿼리 성능이 크게 향상됩니다. 특히 데이터가 많이 쌓이는 테이블의 경우 인덱스 최적화가 필수적입니다.
