인덱스,index

개념,용어

page : 8kb, i/o 최소단위

Extent : 8Page(8kb*8=64kb), 할당의 최소단위

Heap : IAM(Index Alloction Map)를 통해서 관리, IAM 자체는 링크드 리스트

page split

nonclustered index  : 지점(Point)에 유리

                      Heap 테이블의 경우 RID로 Rookup.

                      클러스터드 테이블의 경우 clustering kye로 seek.

                      구조상 클러스터인덱스를 한번더 타기 때문에 느릴것 같지만,

                      클러스터드 인덱스에서 리프가 아닌 노드들은 대부분 캐시에 저장되기 때문에

                      넌클로 검색해도 물리적 i/o는 그렇게 많지 않다..

clustered index     : 리프노드에 실제 데이터를 가지고 있는 트리구조.

                      범위(Range)에 유리, 순차적(Sequential)한 정렬된 데이터 가져올 때 유리하다.

                      게다가 Read-Ahead 기능은 순차적 데이터를 매우 빠르게 읽을 수 있도록 도와준다.

                      고로.. Order by, Group by, Range Search에 사용하면 좋다.

                      참고.. 클러스터드 인덱스의 키값은 넌클러스터드 인덱스의 리프노드 포인으로 저장된다.

Covered Index

Index Hot Spot Problem

                    : 순차(Sequential)인 컬럼을 클러스터드 인덱스로 처리한 경우 대량 트랜잭션에서 인덱스의

                      마지막 페이지에 모든 I/0가 몰리게 되고, 페이지 잠금(Page Locking) 문제가 발생된다.

                      이런 경우 넌클러스터로 처리해서 I/O를 다른 페이지로 분산해야한다.

index intersection

 

인덱스 사용 유무는 액세스하는 데이터량에 의해 좌우된다.

  액세스 데이터량이 많다면 인덱스 보다 fullscan이 낫다.

  인덱스 사용시 액세스하는 논리적인 페이지의 양이 fullscan보다 많아지게 된다.

 

결합인덱스에서 컬럼의 분포도는 의미가 없다. 

  분포도는 동일한 값이 조건(=즉 이퀄 조건)인 경우에만 의미가 있다.

  like나 between 연산자의 경우 분포도는 의미가 없다.

그럼 뭐가 중요한가? 점 조건과 선분 조건!!

  해당 컬럼에 사용되는 연산자가 가장 중요하다.

  (점 조건 : in, = 연산자 // 선분 조건 : 그외 모든 연산자)

 

  결합 인덱스는 반드시 아래와 같은 특성을 가지게 된다.

  ● 점 조건+점 조건: 두 컬럼에 의해 처리 범위 감소
  ● 점 조건+선분 조건: 두 컬럼에 의해 처리 범위 감소
  ● 선분 조건+선분 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
  ● 선분 조건+점 조건: 앞의 선분 조건에 의해서만 처리 범위 감소

 

분포도에 의한 결합 인덱스 선정이 아닌 연산자에 의한 결합 인덱스 선정의 중요성을 인식하라!!

 

인덱스 Access 방법 - http://serious-code.net/moin.cgi/SqlServerIndexTuning

선지식 : B-Tree, 더블 링크드 리스트

Unordered Clustered Index Scan/Table Scan : 인덱스 무시하고 IAM을 통한 스캔 검색

Unordered Covering Nonclustered Index Scan : 넌클을 IAM을 통한 스캔 검색

Ordered Clustered Index Scan : 클인덱스의 리프노드를 스캔 검색. 인덱스 조각화가 심한 경우 성능저하.

Ordered Covering Nonclustered Index Scan : 넌클 리프노드를 이용한 스캔 검색.인덱스 조각화의 영향이 있다.

NonClustered Index Seek + Ordered Partial Scan + Lookups : 반환 행수가 적은 쿼리인 경우 발생. 검색 범위가 넒은 경우 성능이 현저하게 떨어진다.

Unordered Nonclustered Index Scan + Lookups : 쿼리 결과를 필터링하는 조건절이 인덱스와 무관한 경우 발생.

검색 조건의 min값을 이용해 리프레벨의 노드를 찾은 다음, max값을 가지는 노드를 만날 때까지, 리프레벨 링크드 리스트를 따라가는 작업. 클러스터드 테이블이기때문에 룩업과정 필요하지 않다.

Clustered Index Seek + Ordered Partial Scan : 검색 조건이 인덱스 첫번째 키컬럼인 경우 발생.

Covering Nonclustered Index Seek + Ordered Partial Scan

                      

인덱스 생성

. 생성 단계 : 기본 테이블 데이터 페이지에서 키 값 검색 -> 내부 정렬버퍼 용량 만큰 키값을 채운다-> 키값 정렬

  -> 정렬 결과 디스크에 저장 -> 다시 기본 데이터 페이지에서 키 값 검색 -> 모두 찾을 때까지 반복..

. SORT_IN_TEMPDB 옵션 - 선지식:인덱스 생성 단계 - http://msdn.microsoft.com/ko-kr/library/ms188281.aspx

  OFF인 경우 정렬 실행은 대상 파일 그룹에 저장. ON인 경우  정렬 버퍼 중간 결과 tempdb에 저장

  tempdb가 대상 파일과 물리적으로 다른 공간에 있을 경우 성능향상을 꾀할 수 있다.

  인덱스 익스텐트의 근접성을 향상시킬 수 있다.

  정렬작업이 필요하지 않거나, 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB옵션은 무시된다.

  클러스터드 인덱스인 경우 정렬결과가 데이터페이지와 같다=용량이 크다!!

. Include (2k5~)

  Include된 컬럼은 리프레벨에만 들어가게 된다.

. 디스크 공간 요구 사항 - http://msdn.microsoft.com/ko-kr/library/ms191163.aspx

  생성, 다시 작성, 삭제할 때 고려할 사항

 

인덱스 관리 포인트

 i/o 병목 / 사용률 / 조각화  진단 -> 조각화 처리 (재구성, 재생성) 

인덱스 조각화 확인

• Logical scan fragmentation or Average fragmentation in percent or External fragmentation
  Out of order 페이지의 비율. 즉 논리적인 순서와 어긋나게 물리적으로 배치된 인덱스 페이지의 비율. Ordered scan 작업의 성능에 큰 영향을 미친다.
  
  
• Average page density or Internal fragmentation
  Logical scan framentation은 무조건 나쁜 것이지만, 페이지 밀도는 두 가지 측면을 가진다. 낮은 밀도를 가지는 인덱스에서 읽기 작업을 할 때에는 높은 밀도를 가지는 인덱스에서 작업하는 것보다 많은 페이지를 읽어들여야 하므로 불리하다. 하지만 쓰기 작업을 할 때에는 낮은 밀도를 가지는 쪽이 유리하다. 높은 밀도의 인덱스에 대해 쓰기 작업을 하는 경우, 새로운 행이 추가되면 페이지 스플릿, 즉 트리의 분할이 이루어질 가능성이 높기 때문이다. 즉 SELECT 작업을 주로 하는 테이블에서는 밀도가 높아야 하고, INSERT 작업을 자주 하는 OLTP 환경에서는 밀도가 낮아야 한다.

 

2K0

:Track('ctl00_MTCS_main_ctl00|ctl00_MTCS_main_ctl18',this);" href="http://msdn.microsoft.com/en-us/library/aa258803(SQL.80).aspx">DBCC SHOWCONTIG 논리적 스캔조각화, 페이지밀도. 2가지 항목을 봐라

2K5

:Track('ctl00_MTCS_main_ctl00|ctl00_MTCS_main_ctl01',this);" href="http://msdn.microsoft.com/ko-kr/library/ms174281.aspx">sys.dm_db_index_operational_stats(Transact-SQL)

:Track('ctl00_MTCS_main_ctl00|ctl00_MTCS_main_ctl02',this);" href="http://msdn.microsoft.com/ko-kr/library/ms188917.aspx">sys.dm_db_index_physical_stats(Transact-SQL)    -- sql 재식작시 초기화된다.

:Track('ctl00_MTCS_main_ctl00|ctl00_MTCS_main_ctl03',this);" href="http://msdn.microsoft.com/ko-kr/library/ms188755.aspx">sys.dm_db_index_usage_stats(Transact-SQL)

 

인덱스 조각 모음

2K0 

CREATE INDEX~ WITH DROP_EXISTING
- 페이지 압축
- 컬럼 변경
- FILLFACTOR, PAD_INDEX 변경
- 비클러스터형 인덱스를 클러스터형 인덱스로  변경

DBCC DBREINDEX
- 페이지 압축
- FILLFACTOR 변경
- 테이블의 모든 인덱스 일괄 적용 가능

- 배타적 잠금이 걸린다 - 온라인 작업 불가!

DBCC INDEXDEFRAG
- 물리적인 리프 페이지의 순서를 논리적인 순서로 재정렬
- 기존에 지정된 FILLFACTOR 적용

 

참조 : http://blog.naver.com/whwlfnsl/70025397241

 

2K5

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE ;

 

Comparison of DBCC DBREINDEX to DBCC INDEXDEFRAG

Functionality	DBCC DBREINDEX	DBCC INDEXDEFRAG
Online/Offline	Offline	Online
Faster when logical fragmentation is:	High	Low
Parallel processing	Yes	No
Compacts pages	Yes	Yes
Can be stopped and restarted without losing work completed to that point	No	Yes
Able to untangle interleaved indexes	May reduce interleaving	No
Additional free space is required in the data file for defragmenting	Yes	No
Faster on larger indexes	Yes	No
Rebuilds statistics	Yes	No
Log space usage	High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space)	Varies based on the amount of work performed
May skip pages on busy systems	No	Yes

 

 

참고 지식

※ unique 라면 unique 조건을 꼭 적어주자!!!

※ 조각화 : i/o 성능과 관련 

    page split 발생 -> 논리적인 순서(Doubly Linked List)와 물리적 순서(Physical Order In Disk)가 일치하지 않게 된다.

   ->  disk head가 순차적으로 i/o 할 수 없다.

 

※ 조각화 범위 : 0 ~ 10 : 낮음, 10~20 : 중간, 20~30 : 높음 // 일반적으로 20%이하는 재구성, 그 이상은 재생성 한다.

※ 조각화율이 30% 이하라면 ALTER INDEX REORGANIZE, 30% 초과라면 ALTER INDEX REBUILD 구문을 사용하는 것이 좋다고 한다.

※ 조각모음 : 인덱스 스캔쿼리의 성능향상이 가장 크다

※ 클러스터드 인덱스 생성할 때 테이블에 배타적 잠금이 걸린다. - 인덱스 생성시 해당 테이블에 대한 모든 작업이 블로킹.

※ 넌클러스터드 인덱스 생성할 때 테이블에 공유잠금이 걸린다. - 인덱스 생성시 읽기 작업만 가능.

※ 인덱스 재생성 시 주의 사항

     - 로그가 꽤 많이 쌓이기 때문에 recovery mode=simple

     - 인덱스 생성시 필요한 충분한 공간 확보! - 클러스터드 인덱스 생성시 테이블 크기의 약 120% 공간을 필요로 한다!

     - SORT_IN_TEMPDB 옵션 사용시 I/O 경합을 감소 시킬 수 있다

     - MAXDOP 쿼리 힌트. (2K0: 지원하지 않는다.) (2K5 : 지원한다)

     - 2K5부터 인덱스 생성시 온라인 작업을 지원한다.

 

 

 

 

 

* 검색 시 i/o를 줄이기 위해 사용

* 조각화 관리 필요

 

---

 

 

DBCC (2k~)

• dbcc showcontig
• dbcc indexdefrag
• dbcc dbreindex

 

DMV (2k5~)

• sys.dm_db_index_usage_stats 
      사용 유무/사용률/유지관리비용
      해당 인덱스가 처음 사용되는 순간 행 추가
      서비스 중지, AUTO_CLOSE ON, DETACH 될 때 모든 행 제거
      user_updates (=i,d,u의 stmt 개수, 처리된 rows cnt가 아님!)
• sys.dm_db_missing_index_details
      optimizer가 사용하려고 시도 했던 index
• sys.dm_db_missing_index_group_stats
      missing_index의 사용률
      sys.dm_db_index_usage_stats  와 비슷
• sys.dm_db_missing_index_groups

DMF (2k5)

• sys.dm_db_index_operational_stats
      비용/대기정보 추적
      i/o 문제가 발생하는 테이블 or 인덱스 식별 
• sys.dm_db_missing_index_columns
• sys.dm_db_index_physical_stats      
      dbcc showcontig 대체
      인덱스 크기 / 조각화

 

Table

• sys.index_columns   (2k5~)
• sys.indexes         (2k5~)
• sys.columns         (2k5~)
• sys.objects         (2k5~)
• sysindexes          (~2k)
• syscolumns          (~2k)
• sysobjects          (~2k) 

SP

• sp_MSindexspace
• sp_helpindex

Function

• SELECT INDEXPROPERTY
  (object_id('titles'),
  'UPKCL_titleidind',
  'IndexDepth')

 

Create Index (2k~)

• create index ... with (options)
• create index ... wtih drop_existing              (drop & create를 한번에 처리)

Alter Index (2k5~)

• alter index  index_name on table_name reorganize (= dbcc indexdefrag)
• alter index  index_name on table_name rebuild    (= dbcc reindex)

Drop Index (2k~)

• drop index index_name on table_name (2k5)
• drop index table_name.index_name    (2K)

DBCC

• DBCC PGLINKAGE
• DBCC IND
• DBCC PAGE
• DBCC TRACEON(TRACEON (3604))