Reorganizing and Rebuilding Indexes http://Café.naver.com/sqlmvp http://judydba.tistory.com/ chusouk@gmail.com 010-7398-1136 추숙(주디아줌마)

1. 조각화 2. Index Rebuild Tip 3. Index Rebuild 전략 4. QA 목차 1.1 인덱스 조각화란? 1.1 인덱스 조각화란? 1.2 인덱스 조각화의 발생 원인 1.3 인덱스 조각화의 유형 1.4 인덱스 조각화로 인한 영향 1.5 인덱스 조각화 확인 방법 1.5 인덱스 조각화 해결 방법 2. Index Rebuild Tip 2.1 Index Rebuild FillFactor 비율 2.2 Reorganizing and Rebuilding 선택 2.3 Clustered Index Rebuild시 DROP EXISTSING 선택 2.4 복구 모델 선택 3. Index Rebuild 전략 3.1 Rebuilding 전략 요소 3.2 Rebuilding Case 3.3 Rebuilding Offline Case 4. QA

1. 조각화

1.1 인덱스 조각화란? - OLTP 환경에서 “불가피한” 것이 특징 사전적인 의미 체크 할 것.

1.2 인덱스 조각화의 발생 원인 가. Insert and Update operations causing Page Split 1.2 인덱스 조각화의 발생 원인 가. Insert and Update operations causing Page Split 나. Delete operations 다. Initial allocation of pages from mixed extents 라. Large row size

1.3 인덱스 조각화 유형 가. Internal Fragmentation - Random deletes resulting in empty space on data pages - Page-splits due to inserts or updates - Shrinking the row such as when updating a large value to a smaller value - Using a fill factor of less than 100 - Using large row sizes

1.3 인덱스 조각화 유형 나. Logical Fragmentation 1.3 인덱스 조각화 유형 나. Logical Fragmentation - Page-splits due to inserts or updates - Heavy deletes that can cause pages be removed from the page chain, resulting in dis-contiguous page chain

1.3 인덱스 조각화 유형 다. Extent Fragmentation 1.3 인덱스 조각화 유형 다. Extent Fragmentation Extent fragmentation occurs when the extents of a table or index are not contiguous with the database leaving extents from one or more indexes intermingled in the file.

1.3 인덱스 조각화 유형 라. 조각화 구분 가. 조각화가 없는 인덱스 페이지 1.3 인덱스 조각화 유형 라. 조각화 구분 가. 조각화가 없는 인덱스 페이지 나. 무작위 삽입/업데이트/삭제 후 발생할 수 있는 조각화

1.4 조각화로 인한 영향은? Logical fragmentation and Extent fragmentation will cause the read performance to slow down

1.5 인덱스 조각화 확인 방법 DECLARE @id int, @indid int 1.5 인덱스 조각화 확인 방법 DECLARE @id int, @indid int SET @id = OBJECT_ID('dbo.TblX') SELECT @indid = index_id FROM sys.indexes WHERE object_id = @id AND name = 'nc_tblx_randSeq' dbcc showcontig('TblX', @indid) go SELECT table_schema ,OBJECT_NAME(F.OBJECT_ID) obj ,i.name ind ,f.INDEX_TYPE_DESC AS IndexType, f.avg_fragmentation_in_percent, f.Avg_page_space_used_in_percent, f.page_count FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F JOIN SYS.INDEXES I ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id JOIN INFORMATION_SCHEMA.TABLES S ON (s.table_name=OBJECT_NAME(F.OBJECT_ID)) AND f.database_id=DB_ID() AND OBJECTPROPERTY(I.OBJECT_ID,'ISSYSTEMTABLE')=0 WHERE F.OBJECT_ID = OBJECT_ID('TblX') --WHERE F.index_id > 0 AND F.index_id <= 1000 GO

1.6 인덱스 조각화 해결 방법 가. Index Reorganizing(인덱스 다시 구성) 최소한의 시스템 리소스가 사용 1.6 인덱스 조각화 해결 방법 가. Index Reorganizing(인덱스 다시 구성) ALTER INDEX { index_name | ALL }     ON <object>     {    | REORGANIZE         [ PARTITION = partition_number ]         [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]   | SET ( <set_index_option> [ ,...n ] )     } DBCC INDEXDEFRAG 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행 최소한의 시스템 리소스가 사용 장기간 차단 테이블 잠금이 유지되지 않는다

1.6 인덱스 조각화 해결 방법 나. Index Rebuilding(인덱스 다시 작성) 인덱스가 삭제된 다음 다시 생성 1.6 인덱스 조각화 해결 방법 나. Index Rebuilding(인덱스 다시 작성) 인덱스가 삭제된 다음 다시 생성 ALTER INDEX { index_name | ALL }     ON <object>     {    REBUILD         [ [PARTITION = ALL]           [ WITH ( <rebuild_index_option> [ ,...n ] ) ]           | [ PARTITION = partition_number                 [ WITH ( <single_partition_rebuild_index_option>                         [ ,...n ] )                 ]             ]         ]   | SET ( <set_index_option> [ ,...n ] )     } DROP INDEX 인덱스명 ON 테이블명 CREATE INDEX 인덱스명 ON 테이블명 ~ DROP_EXISTING CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name      ON <object> ( column [ ASC | DESC ] [ ,...n ] )      [ INCLUDE ( column_name [ ,...n ] ) ] [ WHERE <filter_predicate> ]      [ WITH ( <relational_index_option> [ ,...n ] ) ]      [ ON { partition_scheme_name ( column_name )          | filegroup_name          | default          }     ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ ; ] | DROP_EXISTING = { ON | OFF } DBCC DBREINDEX (     table_name     [ , index_name [ , fillfactor ] ] )     [ WITH NO_INFOMSGS ]

1.6 인덱스 조각화 해결 방법 다. Reogranizing and rebuilding의 특징 # Characteristic 1.6 인덱스 조각화 해결 방법 다. Reogranizing and rebuilding의 특징 # Characteristic Alter Index REORGANIZE Alter Index REBUILD 1 Online or Offline Online Offline (unless using the Online keyword) 2 Address Internal Fragmentation Yes (can only raise page density) Yes 3 Address Logical Fragmentation 4 Transaction Atomicity Small Discrete Transactions Single Atomic Transaction 5 Rebuild Statistics Automatically No 6 Parallel Execution in multi-processor machines 7 Untangle Indexes that have become interleaved within a data file 8 Transaction log space used Less More 9 Additional free space required in the data file

2. Index Rebuild Tip

2.1 Index Rebuild FillFactor 비율 가. Low Update Tables (100-1 read to write ratio): 100% fill factor 나. High Update Tables (where writes exceed reads): 50%-70% fill factor 다. Everything In-Between: 80%-90% fill factor.

2.2 Reorganizing and Rebuilding 선택 가. Fragmentation >=30 AND PAGES>1000 일때 rebuild 나. Fragmentation between 15 to 29 AND PAGES>1000 일때 reorganize&updatestatistics 다. 가와 나의 조건에 들어가지 있는 다면, update the statistics   

2.3 Clustered Index Rebuild시 DROP EXISTSING 선택 DROP_EXISTING 절은 SQL 서버가 클러스터된 인덱스를 삭제하고 다시 만들 때 기존에 존재하고 있던 넌클러스터 인덱스에 포함되어 있는 클러스터된 인덱스의 키 부분을 삭제하지 않고 새로 만들어진 클러스터된 인덱스의 키로 변경시키도록 하는 역할 - 넌클러스터 인덱스를 삭제하고 다시 만드는 전체 과정에 소요되는 시간 및 자원을 절감   

2.4 복구 모델 선택 인덱스 작업 기간 동안 데이터베이스의 복구 모델을 대량 로그 복구모델 또는 단순 복구 모델로 설정하여 이러한 인덱스 작업을 최소화 로그할 수 있음    인덱스 작업 전체 대량 로그 단순 ALTER INDEX REORGANIZE 전체 로그 ALTER INDEX REBUILD 최소 로그 CREATE INDEX DBCC INDEXDEFRAG DBCC DBREINDEX DROP INDEX 인덱스 페이지 할당 취소가 전체 로그됩니다. 해당 사항이 있는 경우 다시 작성된 새 힙은 전체 로그됩니다. 인덱스 페이지 할당 취소가 전체 로그됩니다. 해당 사항이 있는 경우 다시 작성된 새 힙은 최소 로그됩니다.

3. Index Rebuild 전략

3.1 Rebuilding 전략 요소 가. 추가 디스크 공간 Source + Sort Table + B-Tree를 위한 대략 2.2*Index Size 필요 나. 인덱스 빌드 동작 저장을 위한 공간 선택 a) User’s Database(default) b) tempdb(SORT_IN_TEMPDB 옵션 지정) 재사용 가능 다. Query Executor Process 가동을 위한 메모리 적어도 40Pages(3200KB)의 메모리 필요  

3.2 Rebuilding Case Online Index Build Offline Index Build Create clustered index idx_t on t(c1, c2) WITH (ONLINE = ON) Serial Index Build Parallel Index Build Create index idx_t on t(c1, c2) WITH (MAXDOP = 2) Storing in User’s database Storing in tempdb Create clustered Index idx_t on t(c1) WITH (SORT_IN_TEMPDB = ON) Partitioned index build Non Partitioned build

3.3 Rebuilding Offline Case # Case Add Case Desc 1 Serial DISK : 2.2*Index Size Memory : At least 40 Pages(3200KB) 2 Parallel Use Stats Plan (Historygram) Serial Build보다 더 많은 메모리 소모 #DOP만큼 sort table 생성 Non Stats Plan (No historygram) Indexed view(“No Stats Plan”) Parallel data source read 4 Parallel Partitionning (use sort_in_tmpdb) Aligned partitioned Non-Aligned partitioned • Aligned (when base object and in-build index use the same partition schema) • Not- Aligned (when heap and index use different partition schemas (including the case when base object is not partitioned at all and in-build index use partitions))

참고 인덱스를 리빌드 및 통계 업데이트를 한꺼번에^^ (주디아줌마 블로그) http://judydba.tistory.com/135 http://www.alicerock.com/1051 http://blogs.msdn.com/b/pamitt/archive/2010/12/23/notes-sql-server-index-fragmentation-types-and-solutions.aspx http://blogs.msdn.com/b/sqlqueryprocessing/archive/tags/indexing/

Thank you~~