인덱스 정말 까다롭고도 많은 양입니다.

인덱스의 이해에 대해서는 다양하고 속 깊은 논리를 많이 알아야 한다는 거죠.... (전 깊게 알지 못합니다. -_- 솔직히 어렵더라구요)

IAM (인덱스 올로케이션 맵)은 인덱스에 대한 익스텐트를 탐지하고,

익스텐트를 오브젝트로 매핑 합니다.

한개의 IAM 은 512000개의 페이지를 커버합니다.

root level - non leaf level ( 혹은 중간레벨 ) - leaf level

pad index 는 non leaf level 의 공간 확보를 위한 옵션 입니다.

fill factor 는 leaf level 의 초기 체우기 비율을 말합니다.

인덱스는 발렌스드 트리 구조를 가집니다.

여기서 꽉 차 있는 fill factor 100 or 0 은 당연히 페이지당 데이터 밀도가 높기 때문에 거쳐야 할 단계도 적습니다. 그러니 서칭이 빠르죠

하지만 인서트시 꽉 차 있다면 100% 라면 (사실은 100프로 다 채워 이럼 mssql 이 이 옵션을 무시하고 최소 2개의 row 를 삽입할 수 있는 공간을 둡니다.) 데이터를 넣기 위한 page 가 없게 됩니다. 그럼 page 는 둘로 갈라져야 하고 2개로 가른후 데이터를 삽입하게 됩니다. 이때 윗 레벨에도 공간이 없다면 또 페이지 스플릿이 일어 나겠죠?

그러니 테이블을 만들때 혹은 인덱스를 만들때 그 공간의 채우기 비율을 조정해주면, (인덱스 속도와 인서트 델리트 속도의 트레이드 오프가 관계) 인서트시 패이지 스플릿 없이 데이터를 넣을수 있다 라는 재미있는 결과가 나옵니다.

그러면 서칭도 테이블 스캔보다 빠르면서 인서트도 빠르게 할 수 있다는 것이죠 그리고 dbcc showcontig 로 페이지 밀도 및 인덱스의 논리 조각화 정도를 볼 수 있고 이것을 서비스를 하면서도 인덱스 조각화를 해결하는 dbcc indexdefrag 를 해 조각화와 인덱스에 적어준 패드인덱스와 필 펙터에 근사하게(?) 최적화 하고 나서 나머지 페이지는 버려버립니다.

이렇게 버린 공간을 다시 반환하고 디비를 가볍게 하기 등등 의 dbcc 명령어가 있게 되는거죠

단순히 이것을 선배들이나 잘하는? 사람들에게 물어보기 보다는 자신이 책을 읽고 심도 있게 정리하는 것이 맞다고 생각합니다.

mssql 의 index 의 카디널리티 밀도 선택성 등등의 개발자가 알아야 할 많은 성능 이슈도 같이 공부하셔야 할 것 같습니다.

= = = = = = = = = =

이렇게 봤을때 저는 새로운 데이터가 들어가기 위한 예약 공간이다 라고 이해하고 있습니다.

계속 불어나게 만들지는 않은것 같습니다.그런데 iam 이 저렇게 많은 데이터를 커버하는데 그까이꺼 늘어나두 뭐 얼마나 되겠는가 라는 생각도 드는군요

먼저 답변 감사합니다.

질문 사항은 fillfactor 기타 저장영역에 대한 부분이 아니였습니다.

showcontig 에 나타나는 값에 대한 분석및 rebuild 에 의한 단편화 해소등은 말씀해 주신 내용들은 말씀하신데로 책에 잘 나와 있죠...

하지만...  인덱스 영역에 대한 삭제후에 대한 재 사용 여부는 잘 나와 있는 책들이 많지 않더군요....

오라클의 경우는 pctused 라는 값이 0 입니다. 인덱스 영역에 해당 되는 부분만 그렇죠... 즉 삭제후에 인덱스 영역에는 재 사용이 불가하다는 것을 의미합니다. 하지만 내부적으로 block split 을 살펴 보면.. 재사용이 되고 있어 기존 오라클 정식교재나 기타 맹신적으로 믿어 오던 index rebuild에 의한 성능 향상에 대해서 많은 반론과 아울러 실효성이 없어 진 상태입니다.

 그래서 mssql 에서는 어떤가 궁금했기에 글을 올려 본것입니다. 님도 그 부분에 대해서는 확실한 답변을 주지 못하신거 같네요.. ^^;;

그래서 제가 직접 테스트를 수행해봤는데요... mssql 도 역시 빈공간을 재 사용은 하는거 같습니다. 즉 delete 후에 insert 하면서 page 번호를 추적하여 확인해 본 결과 clustered 가 됬건 non-clustered index 가 됬껀 공간을 재 사용하는 것을 나왔네요.. ^^;;

즉 mssql 도 delete 후에 insert 로 인해서는 page split 이 심각히 발생하지 않음을 알아 냈습니다.

 하지만.. update 후에는 조금 틀린 현상이 발생하더군요..뿐만아니라..모든 경우에 delete 후 insert 시 삭제된 인덱스 영역을 사용하는 것이 아니더군요..즉 random 하게 발생하는 delete 후의 insert 에서도 역시 마찬가지로 할당 해제 영역이 아닌 새로운 page에 split 이 발생하면서... insert 가 되어 단편화가 발생하더군요.... ^^;;

 이슈에 대한 내용은 이정도가 될거 같구요..

님이 조금 잘못 이해 하고 계시는 fillfactor 나 pad index 옵션에 대한 내용은 수정하셔야 할 거 같아요....

즉 제가 말씀드린 재 사용에 대한 부분과 fillfatcor 와는 별개 입니다.

fillfactor나 pad index 는 leaf level과 intermaidate(중간) 영역에 대한

공간을 예약하여 page split 에 의한 b-tree 의 depth 가 더 깊어 지지 않게 하려고 공간을 일정 양 예약 한 것을 의미 합니다. 이는 인덱스 영역의 삭제되고 다시 재 사용된다는 의미와는 완전히 별개 입니다. ^^;;

도움 되셨길.. ^^ 수고하세요

중간레벨 이나 non-leaf level 은 같은 말 입니다.

설명 잘 들었습니다.

네 sqler 님이 말씀하신부분과는 처음부터 좀 떨어진 이야기라는 걸 알았습니다. ^-^  그냥 책만 보고 공부해서 아직 잘 모릅니다.

많은걸 배웠구요 추적 화면 스크랩 한거 있음 한번 보고 싶고 어떻게 추적했는지도 한번 가르쳐 주시면 감사하겠습니다.

화면 스크랩이 있음 더 좋겠는데요......

결론은 grow grow 현상은 일어나지 않는것이군요 ?

그리고 왜 리빌드를 할때 인덱스 영역을 재 사용하면 성능향상이

되지 않는거라고 생각하시는건지 그 이유가 궁금합니다.

인덱스에서의 단편화 때문에 그런것인지요? 의견을 듣고 싶습니다.

많은 질책 부탁 드립니다.

많은 사람의 궁금증을 풀어줄 것 같군요...

그럼 수고하세요 ^^;

use tempdb

drop table test

--인덱스 페이지에 조금씩 들어가게 하려고 복합인덱스로 구성합니다.
create table test(a int , b char(800))


--대충 행을 입력하고..
declare @a int
set @a  =1
while 1=1
begin
insert into test values(@a,'')
set @a=@a+1
end

select count(*) from test
--7424행 반환.

--넌클러스터 인덱스 생성
create unique nonclustered index test_n on test(a,b)

--object_id 를 확인하고 first page를 확인..
select * from sysindexes where id = object_id('test')
--obj_id = 831458882
-- root = 0xD50400000100
-- first = 0x880200000100



dbcc traceon (3604)

--first_page 를 다시 확인하고.
dbcc ind(2,831458882,2)
first_page = 648

--대충 감으로 페이지를 선택합니다.
--64~72 번까지 656 페이지에 존재하네요//
dbcc page(2,1,656,3)

1 656 0 0 64
1 656 1 0 65
1 656 2 0 66
1 656 3 0 67
1 656 4 0 68
1 656 5 0 69
1 656 6 0 70
1 656 7 0 71
1 656 8 0 72
                                                                                                                                                            
                                                                                                  

--그럼 중간에 있는 68번행을 삭제합니다.
delete test where a = 68

--해당 인덱스의 리프레벨에서 68의 존재 여부를 확인.
dbcc page(2,1,656,3)
1 656 0 0 64
1 656 1 0 65
1 656 2 0 66
1 656 3 0 67
1 656 4 0 69
1 656 5 0 70
1 656 6 0 71
1 656 7 0 72


--다시 68을 입력
insert into test values(68,'')

--해당 페이지에 68의 데이터가 있는지, 아니면 다른 페이지로 할당되었는지 확인
dbcc page(2,1,656,3)
1 656 0 0 64
1 656 1 0 65
1 656 2 0 66
1 656 3 0 67
1 656 4 0 68/*보이네요.*/
1 656 5 0 69
1 656 6 0 70
1 656 7 0 71
1 656 8 0 72

--656페이지에 공간이 남아있으므로 656페이지에 저장이 되었다.

dbcc traceoff(3604)

이렇게 테스트됬구요.... 이는 clustered index 에도 같은 상황이 적용됩니다.

즉 삭제된 인덱스 영역을 재 사용하는 것이 가능하겠죠....

그렇기 때문에 logical,phyical 단편화는 크게 많이 발생하지 않는거

같습니다. 이 부분이 mssql 7 보다 월등히 향상된 기능이라고 생각됩니다. 즉 7 버전에서 와 같이 2000 에서 index rebuild 로 얻어 지는 성능의 향상 폭은 적다는 것을 의미합니다.

하지만 모든 상황이 다 재 사용되는 것은 아닙니다. 순차적이지 않은 random 하게 발생하는 update 나 delete 의 경우 b-tree 에 의해 순차적인 페이지를 찾아 가야 하기 때문에... page split 이 발생할수 있습니다. 이는 오라클도 마찬가지입니다.

하지만 오라클의 경우 8i 버전 이상부터 new feature 로 적용된 LMT 라는 테이블 스페이스 방식으로 인해 단편화의 발생여부가 거의 없지만..

즉 모든 EXTENTS 할당을 초기 사이즈의 2배로 할당합니다.

MSSQL 의 경우 UNIFORM EXTENTS 와 MIXED EXTENTS 를 혼용해 사용하기 때문에 .. 더군다나 기본적인 인덱스의 경우 테이블보다 사이즈가 작기 때문에 MIXED(혼합) EXTENTS로 영역을 할당하기 때문에 단편화 현상은 발생할수 있습니다. 제가 테스트 했던 부분은 DELETE 후의 INSERT 였씁니다. UPDATE 의 경우는 발생할수 있씁니다. 그렇기 때문에 FILLFACTOR 나 기타 INDEX PAD 와 같은 옵션으로 PAGE SPLIT 을 막아 주려고 노력하는 것이 겠죠...

 인덱스 REBUILD 에 의한 성능 향상은 당연히 있습니다. 잘 아시겠지만.... INDEX SCAN 이나 SEEK 시 발생하는 IO 를 줄여 주기 때문이죠....

 하지만... 모든 상황이 다 REBUILD 후에 성능이 향상되는 것은 아니란 것을 알려 드리고 싶었습니다. ^^;;

실제로 인덱스 REBUILD 후에도 논리적 EXTENTS 단편화나 기타 밀집도에서 별 차이가 없는 OBJECT 들을 보실수 있는데요.. 이도 위와 같은 맥락에서 설명이 가능할 듯 싶습니다.

 그리고 LEAF LEVEL 과 중간 레벨은 차이가 있습니다. ^^;;

그리고 GROW AND GRWO 는 항상 발생하는 것은 아니라고 판단되네요.... 말씀드린 데로 인덱스 영역을 재 사용하는 것이 가능하고 FILLFACTOR 로 적절히 PAGE SPLIT 을 막아 주면 생각하시는 것 처럼 커다란 GROW AND GROW 는 항상 발생하는 상황은 아니겠죠...

물론 인덱스의 DEPTH 는 커 질수 있습니다. 이는 트랜잭션의 특성에 따라서 발생할수 있겟죠...

신봉적으로 책에 나와 있던 내용들을 모두 믿을 필요는 없을거 같아요...

 저도 오라클 DBA 를 하고 있지만... 교재나 기타 밴더의 가이드는 트랜드에 상당히 벗어난 내용들이 많이 존재합니다. 오라클의 경우 자료들및 전문가들의 가이드가 상당히 보편적으로 잘 나와 있고 기술이 오픈됬지만..  MSSQL 은 그런 부분이 상당히 취약하기 때문에... 사용도 솔직히 망설여 지네요... 원하는 만큼의 수준을 채워가기가 어렵기 때문이죠.... MSSQL 공부한지 이제 2달 되가지만.... 오라클에 비하면... 쉽긴 하지만.... 가이드가 형편없어서... 얼마만큼 LEVEL UP을 할수 있을지 의문이 들어요.. ^^;; 이제 MSSQL로 전환해야 하는데 고민이군요... ^^;;

 설명이 어느정도 됬으면 좋겠어요.. 수고하세요...

공유해 주셔서 정말 감사합니다.

열심히 배우도록 하겠습니다.

^-------^

혹시 정모 있음 저두 함 나가볼까 하네요 ^-^

혹시 정모도 가시나요?

도움 되셨다니.. 다행이네요...

정책상 mssql 로 전환해야 하는데.. 걱정입니다. ^^;

저는 정모에는 나가지 않가지 않습니다. 가고 싶긴한데..

현업 엔지니어 이신가봐요?  좋은 인연 됬으면 좋겠네요..

 수고하세요...