강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트 B*TREE 인덱스 구조 강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트

목차 인덱스 개요 B*TREE 인덱스 구조 B*TREE 인덱스 생성 과정 B*TREE 인덱스 행의 구조 SQL Server 인덱스 인덱스 페이지 조각화 인덱스 검색 프로세스 이해

인덱스 개요 장점 단점 - 성능 개선 : 데이터 검색 시 IO 및 CPU 사용량 감소로 검색 속도 개선 - 유일성 보장 : 데이터의 유일성을 보장하는 유일한 수단 (PK, UNIQUE 제약 조건도 내부적으로는 고유 (unique) 인덱스생성) 단점 - 저장 공간 소요 : sysindexes, sp_spaceused 구문으로 사용 공간 확인 - DML 작업 시 추가 부하 발생

인덱스 키 + 포인터 (ROWID / Clustering Key) B*TREE 인덱스 구조 Tree 구조로 구성됨 ( 나무를 뒤집어 놓은 구조 ) - 상위 : Root Page - 중간 : Intermediate Page - 하위 : Leaf Page 인덱스 시작점 리프 레벨 포인터 인덱스 키 + 포인터 (ROWID / Clustering Key) -------------------------- Root Page --- Leaf Page --------------- Intermediate Page

… B*TREE 인덱스 생성 과정 Leaf Level을 먼저 구성하고 Leaf Page가 2개 이상으로 분할 시 새로운 Root Level 추가 Root Page가 2개 이상으로 분할 시 새로운 Root Page 추가 기존의 Root는 Intermediate Page가 된다. 인덱스 페이지는 이중 링크로 구성되어 페이지간 연결 정보 유지 Root (single page) Root B - 인덱스 Root  Intermediate Root … Leaf Root  Leaf

B*TREE 인덱스 행의 구조 인덱스 페이지 행 (리프 페이지가 아닌 경우) 인덱스 페이지 행 (힙 테이블 상의 리프 페이지) 인덱스 페이지 행 (리프 페이지가 아닌 경우) Header Index Key Column Down Page ID Down Page File ID No. of Columns NULL bitmap 1byte 4bytes 2bytes 1bit/Cols 인덱스 페이지 행 (힙 테이블 상의 리프 페이지) Header Index Key Column Book Mark Page ID File ID Slot ID NULL bitmap 1byte 4bytes 2bytes 1bit/Cols 인덱스 페이지 행 (클러스터 형 인덱스 상의 리프 페이지) Header Index Key Column No. of Columns NULL bitmap No. of Variable Variable Column Header Clustered 1byte 2bytes 1bit/Cols 2bytes/Cols

인덱스 키 + 포인터 (ROWID / Clustering Key) DATA ( CLUSTERED INDEX ) SQL Server 인덱스 NON-CLUSTERED INDEX - 리프 레벨 CLUSTERED INDEX 인덱스 키 + 포인터 (ROWID / Clustering Key) 데이터 페이지 --- --------- ---- Root Page ---- ----- ---- -- --- Leaf Page --- ----- Intermediate Page ---- B - 인덱스 DATA ( CLUSTERED INDEX ) DATA ( HEAP )

insert emp (name,title…) values (‘이소진’, ‘대리’ …) 인덱스 페이지 조각화 insert emp (name,title…) values (‘이소진’, ‘대리’ …) Non-Leaf Level 가기순 3:298 김태훈 3:302 이소민 3:306 임세령 3:310 < =이소진 이소진 3:320:1 Leaf Level (key Value) Page 298 Page 302 Page 305 Page 310 인덱스에서 Page Splits 3:137:1 이소진 3:137:3 이수희 3:137:2 이소정 3:145:1 이소영 이소민 가기순 3:133:3 공국진 3:111:2 공대열 3:129:4 … 김태훈 3:102:3 남수중 3:133:0 남현주 3:102:0 … 3:137:1 이소진 3:145:1 이소영 이소민 임세령 3:200:3 임용찬 3:188:0 임희찬 3:172:1 … 3:137:2 이수희 3:320:1 이소진 3:137:1 이소정

인덱스 키 + 포인터 (Clustering Key) 비클러스터형 인덱스의 구조 변화 클러스터형 인덱스가 있는 상태에서의 비클러스터형 인덱스 구조 인덱스 키 + 포인터 (Clustering Key) DATA ( CLUSTERED INDEX ) 클러스터드 인덱스의 레벨수 –1 만큼의 I/O가 추가 새로운 데이터 삽입시 NON-Clustered INDEX의 추가 부하 발생이 없다 단점 장점 B - 인덱스

Demo : 인덱스 포인터 정보 확인 포인터 정보 (ROWID or 클러스터형 인덱스 키) 확인

select * from emp where name=‘이소정’ 비클러스터형 인덱스를 사용한 데이터 검색 select * from emp where name=‘이소정’ sysindexes Indid=2 Root=400 Root Page 400 가기순 3:301 김태훈 3:302 이소민 3:305 임세령 3:310 < =’이소정’ Page 301 Page 305 Leaf Level (key Value) Page 302 Page 310 가기순 3:133:3 공국진 3:111:2 공대열 3:129:4 … 김태훈 3:102:3 남수중 3:133:0 남현주 3:102:0 … 이소민 3:137:1 이소영 3:145:1 이소정 3:137:2 … 임세령 3:200:3 임용찬 3:188:0 임희찬 3:172:1 … Clustered 되지 않은 Index가 있는 Heap에서 Data 검색 HEAP BOOK MARK NAVIGATION Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

select * from emp where empno = 10006 클러스터형 인덱스를 사용한 데이터 검색 select * from emp where empno = 10006 sysindexes Indid=1 Root=100 Root Page 100 10000 3:101 10005 3:102 10009 3:120 … 10024 3:137 10068 3:205 < =10006 Leaf Clustered Index가 있는 테이블에서 Data 검색 Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

select * from emp where name=‘이소정’ BOOK MARK NAVIGATION (10026) 클러스터형 인덱스가 있는 테이블에서 비클러스터형 인덱스를 사용한 데이터 검색 select * from emp where name=‘이소정’ sysindexes Indid=2 Root=400 Root Page 400 가기순 3:301 김태훈 3:302 이소민 3:305 임세령 3:310 < =’이소정’ Page 301 Page 305 Leaf Level (key Value) Page 302 Page 310 가기순 10044 공국진 10043 공대열 10013 … 김태훈 10006 남수중 10066 남현주 10004 … 이소민 10025 이소영 10063 이소정 10026 … 임세령 10051 임용찬 10039 임희찬 10057 … Clustered 되지 않은 Index가 있는 Heap에서 Data 검색 BOOK MARK NAVIGATION (10026) sysindexes Indid=1 Root = 100 Clustered Index ROOT

BOOK MARK NAVIGATION (10026) 클러스터형 인덱스가 있는 테이블에서 비클러스터형 인덱스를 사용한 데이터 검색 (2) BOOK MARK NAVIGATION (10026) Root Page 100 10000 3:101 10005 3:102 10009 3:120 … 10024 3:137 10068 3:205 < =10026 Leaf Level (DATA) Clustered Index가 있는 테이블에서 Data 검색 Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

Demo : 인덱스 사용 시 IO 분석 인덱스 사용 시 IO 분석

요약 B*TREE 인덱스의 구조 - Bottom-Up 방식으로 생성 되며 루트 페이지 하나를 유지함 - 루트-중간-리프 페이지로 구성 - 리프 페이지는 인덱스 키 컬럼 +포인터로 구성됨 비클러스터형 인덱스 - 단일 행 또는 적은 범위의 검색에 효율적인 구조 - 클러스터형 인덱스의 존재 여부에 따라서 포인터 정보가 행 구별자(ROWID) 또는 클러스터형 인덱스 키러 구성됨 클러스터형 인덱스 - 일반 B*TREE 인덱스와 구조는 동일하나 리프 페이지가 데이터인 구조 - 범위 검색 / 집계 연산 등에 효율적

강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트 B*TREE 인덱스 정보 확인하기 강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트

목차 B*TREE 인덱스 기본 정보 확인 B*TREE 인덱스 통계 정보 확인 B*TREE 인덱스 조각화 확인

B*TREE 인덱스 기본 정보 확인 sysindexes 시스템 테이블 id indid first root first IAM dpages statblob rows rowmodctr Id : 기반 오브젝트 ID ( table / view) Indid : index ID First : Leaf-level 의 시작 페이지 주소 Root : 인덱스의 시작점 주소 First IAM : 첫 IAM 페이지 주소 Dpages : leaf-level 페이지의 개수 Statblob : 통계페이지 (image 로 저장) Rows : 인덱스의 행 수 Rowmodcrt : 통계 업데이트 기준 / 최근 통계 업데이트 후 변경된 행의 개수 0: HEAP 1: 클러스터형 인덱스 2~250: 비클러스터형 인덱스 255 : BLOB page Heap 에서 Data 검색

Demo : 인덱스 기본 정보 확인 sysindexes 시스템 테이블 정보 확인

B*TREE 인덱스 통계 정보 확인 / 갱신 update statistics employees pk_employees SP_HELPSTATS ‘테이블 이름’, ‘ALL’ : 테이블의 모든 통계의 이름 반환 DBCC SHOW_STATISTICS (‘테이블_이름’, ‘인덱스_이름’) : 해당 통계 페이지 정보 반환 update statistics 테이블_이름 인덱스_이름 update statistics employees pk_employees 3. ① 통계정보 ②tempdb ③blocking ④data file growth ⑤log file growth ⑥disk used percentage ⑦ parallel bug ⑧virus… 4. ① 통계정보 -> update statistics ②tempdb-> 충분한 사이즈로 변경, 개수 증가 ③blocking-> blocking process 문제 해결 (sp_broker_pas80 procedure, profiler,sp_lock,sp_who2…) ④data file growth 충분한 사이즈로 변경 / 향후 filegrowth 적정값으로 변경 ⑤log file growth  작은 트랜잭션이 빈번하게 발생하는 경우 (log backup 실행 여부) 큰 트랜잭션이 발생하는 경우 ( 충분한 사이즈로 변경 / 향후 filegrowth 적정값으로 변경 / 트랜잭션 분할 여부) ⑥disk used percentage  추가 적인 파일/파일 그룹 할당 / 물리적 디스크 할당 ( if controller permmit) ⑦ parallel bug maxdop 변경 / max worker thread 조정 ⑧virus  virus 치료

통계 페이지 정보 Updated Rows Rows Sampled Steps : Max 200 Density All Density Range_Hi_Key Range_Rows EQ_Rows Distinct_Range_Rows Avg_Range_Rows

Demo : 인덱스 통계정보 확인 인덱스 통계 정보 확인

B*TREE 인덱스 조각화 정보 확인 인덱스 조각화의 영향 1) DML (insert/update/delete) : 페이지 할당 / 조각화 부하 감소  OLTP 성 효율적 2) select : range-query, group by , order by 등 seek time 증가  DW, DSS, Report 용 비효율적 조각화 여부 확인 : DBCC SHOWCONTIG

DBCC SHOWCONTIG (조각화의 경우) dbcc showcontig ('tb_m_mmodel_cost_act_past','PK_tb_m_mmodel_cost_act_past') DBCC SHOWCONTIG이(가) 'tb_m_mmodel_cost_act_Past' 테이블을 스캔하는 중... 테이블: 'tb_m_mmodel_cost_act_Past' (165575628); 인덱스 ID: 1, 데이터베이스 ID: 10 TABLE 수준 스캔이 수행되었습니다. - 스캔한 페이지................................: 1888100 - 스캔한 익스텐트..............................: 244450 - 전환된 익스텐트..............................: 671143 - 익스텐트 당 평균 페이지 수........................: 7.7 - 스캔 밀도[최적:실제].......: 35.17% [236013:671144] - 논리 스캔 조각화 상태 ..................: 2.35% - 익스텐트 스캔 조각화 상태 ...................: 35.57% - 페이지 당 사용 가능한 평균 바이트 수.....................: 76.7 - 평균 페이지 밀도(전체).....................: 99.05% DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. select count(*) from tb_m_mmodel_cost_act_past where work_yymm='200410' CPU 시간 = 7189ms, 경과 시간 = 30181ms.

DBCC SHOWCONTIG (재구성 후의 경우) dbcc showcontig ('tb_m_mmodel_cost_act_past','PK_tb_m_mmodel_cost_act_past') DBCC SHOWCONTIG이(가) 'tb_m_mmodel_cost_act_Past' 테이블을 스캔하는 중... 테이블: 'tb_m_mmodel_cost_act_Past' (165575628); 인덱스 ID: 1, 데이터베이스 ID: 10 TABLE 수준 스캔이 수행되었습니다. - 스캔한 페이지................................: 1888097 - 스캔한 익스텐트..............................: 240140 - 전환된 익스텐트..............................: 240139 - 익스텐트 당 평균 페이지 수........................: 7.9 - 스캔 밀도[최적:실제].......: 98.28% [236013:240140] - 논리 스캔 조각화 상태 ..................: 0.42% - 익스텐트 스캔 조각화 상태 ...................: 0.11% - 페이지 당 사용 가능한 평균 바이트 수.....................: 76.7 - 평균 페이지 밀도(전체).....................: 99.05% DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. select count(*) from tb_m_mmodel_cost_act_past where work_yymm='200410' CPU 시간 = 6267ms, 경과 시간 = 14510ms.

요약 B*TREE 인덱스 기본 정보 확인 - sysindexes 시스템 테이블을 통해서 다음 내용 확인 ( 인덱스 종류/ 행 숫자 / 통계 정보 갱신 임계값 / 사용중인 페이지 숫자 등) B*TREE 인덱스 통계 정보 확인 (DBCC SHOW_STATISTICS ) - 정기 적인 통계 정보 확인을 통한 갱신 주기 결정 - 올바른 실행 계획 생성 여부 확인 후 샘플링 비율 결정 B*TREE 인덱스 조각화 정보 확인 (DBCC SHOWCONTIG) - 정기 적인 조각화 정보 확인을 통한 인덱스 재구성 주기 결정 - 적절한 채우기 비율 (fillfactor) 결정