Transcript 2. Index Rebuild PPT(배포용) - 주디아줌마

Reorganizing and Rebuilding
Indexes
http://Café.naver.com/sqlmvp
http://judydba.tistory.com/
[email protected]
010-7398-1136
추숙(주디아줌마)
목차
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 환경에서 “불가피한” 것이 특징
4 / 주디아줌마
1.2 인덱스 조각화의 발생 원인
가. Insert and Update operations causing Page Split
나. Delete operations
다. Initial allocation of pages from mixed extents
라. Large row size
5 / 주디아줌마
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
6 / 주디아줌마
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
7 / 주디아줌마
1.3 인덱스 조각화 유형
다. Extent Fragmentation
8 / 주디아줌마
1.3 인덱스 조각화 유형
라. 조각화 구분
가. 조각화가 없는 인덱스 페이지
나. 무작위 삽입/업데이트/삭제 후 발생할 수 있는 조각화
9 / 주디아줌마
1.4 조각화로 인한 영향은?
Logical fragmentation and Extent fragmentation will cause the read
performance to slow down
10 / 주디아줌마
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 11 / 주디아줌마
WHERE F.OBJECT_ID = OBJECT_ID('TblX')
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
왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준
페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터
형 인덱스의 리프 수준에 대한 조각 모음을 수행
-최소한의 시스템 리소스가 사용
-장기간 차단 테이블 잠금이 유지되지 않는다
12 / 주디아줌마
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
{ filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ ; ]
| DROP_EXISTING = { ON | OFF }
DBCC DBREINDEX (
table_name
[ , index_name [ , fillfactor ] ] )
| default
}
] [ FILESTREAM_ON
[ WITH NO_INFOMSGS ]
13 / 주디아줌마
1.6 인덱스 조각화 해결 방법
다. Reogranizing and rebuilding의 특징
# Characteristic
Alter Index REORGANIZ Alter Index REBUILD
E
1 Online or Offline
Online
Offline (unless using the Online k
eyword)
2 Address Internal Fragmentation
Yes (can only raise page density)
Yes
3 Address Logical Fragmentation
Yes
Yes
4 Transaction Atomicity
Small Discrete Transactions
Single Atomic Transaction
5 Rebuild Statistics Automatically
No
Yes
6 Parallel Execution in multi-processor machines
No
Yes
7 Untangle Indexes that have become interleaved
within a data file
No
Yes
8 Transaction log space used
Less
More
9 Additional free space required in the data file
No
Yes
14 / 주디아줌마
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.
16 / 주디아줌마
2.2 Reorganizing and Rebuilding 선택
가. Fragmentation >=30 AND PAGES>1000 일때 rebuild
나. Fragmentation between 15 to 29 AND PAGES>1000 일때 reorganize&up
datestatistics
다. 가와 나의 조건에 들어가지 있는 다면, update the statistics
17 / 주디아줌마
2.3 Clustered Index Rebuild시 DROP EXISTSING 선택
DROP_EXISTING 절은 SQL 서버가 클러스터된 인덱스를 삭제하고 다시 만들
때 기존에 존재하고 있던 넌클러스터 인덱스에 포함되어 있는 클러스터된 인
덱스의 키 부분을 삭제하지 않고 새로 만들어진 클러스터된 인덱스의 키로 변
경시키도록 하는 역할
- 넌클러스터 인덱스를 삭제하고 다시 만드는 전체 과정에 소요되는 시간 및
자원을 절감
18 / 주디아줌마
2.4 복구 모델 선택
인덱스 작업 기간 동안 데이터베이스의 복구 모델을 대량 로그 복구모델 또는
단순 복구 모델로 설정하여 이러한 인덱스 작업을 최소화 로그할 수 있음
인덱스 작업
ALTER INDEX
REORGANIZE
ALTER INDEX REBUILD
CREATE INDEX
DBCC INDEXDEFRAG
DBCC DBREINDEX
DROP INDEX
전체
전체 로그
대량 로그
전체 로그
단순
전체 로그
전체 로그
최소 로그
최소 로그
전체 로그
최소 로그
최소 로그
전체 로그
전체 로그
전체 로그
전체 로그
최소 로그
최소 로그
인덱스 페이지 할당
인덱스 페이지 할당
인덱스 페이지 할당
취소가 전체 로그됩니다. 취소가 전체 로그됩니다. 취소가 전체 로그됩니다.
해당 사항이 있는 경우
해당 사항이 있는 경우
해당 사항이 있는 경우
다시 작성된 새 힙은 전체 다시 작성된 새 힙은 최소 다시 작성된 새 힙은 최소
로그됩니다.
로그됩니다.
로그됩니다.
19 / 주디아줌마
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)의 메모리 필요
21 / 주디아줌마
3.2 Rebuilding Case
Online Index Build
Offline Index Build
Create clustered
index idx_t on t(c1, c2)
WITH (ONLINE = ON)
Create clustered index idx_t on t(c1, c
2)
Serial Index Build
Parallel Index Build
Create index idx_t on t(c1, c2)
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)
Create clustered Index idx_t on
t(c1)
WITH (SORT_IN_TEMPDB = ON)
Partitioned index build
Non Partitioned build
22 / 주디아줌마
3.3 Rebuilding Offline Case
# Case
Add Case
1 Serial
2 Parallel
4 Parallel Partitionning
(use sort_in_tmpdb)
Desc
DISK : 2.2*Index Size
Memory : At least 40 Pages(3200KB)
Use Stats Plan
(Historygram)
Serial Build보다 더 많은 메모리 소모
#DOP만큼 sort table 생성
Non Stats Plan
(No historygram)
Indexed view(“No Stats Plan”)
Parallel data source read
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))
23 / 주디아줌마
참고
1.
인덱스를 리빌드 및 통계 업데이트를 한꺼번에^^ (주디아줌마 블로그)
http://judydba.tistory.com/135
2.
http://www.alicerock.com/1051
3.
http://blogs.msdn.com/b/pamitt/archive/2010/12/23/notes-sql-server-indexfragmentation-types-and-solutions.aspx
4.
http://blogs.msdn.com/b/sqlqueryprocessing/archive/tags/indexing/
24 / 주디아줌마
Thank you~~