컴퓨토피아

저자: Conor, Query Optimizer Team

원문: http://blogs.msdn.com/queryoptteam/archive/2006/04/12/575241.aspx

김정선의 의견

이번 글은 아주 흥미로운 내용입니다! 특히 쿼리 튜닝에 있어서요.

(RID 혹은 Key) Bookmark Lookup이나 Loop Join 등에서 발생하는 Random I/O에 대해 이해할 수 없는 부분들이 많이 있으시죠? 그 중에 하나가 아마도 논리적 읽기와 물리적 읽기에 대한 차별화되지 않은 처리일 것입니다. 우연히 보게 된 이 글에서 Query Optimizer팀이 어떤 고민을 하고 있는지, 어떻게 변화될 것인지? 그래서 어떻게 사용하는 것이 좋을지에 대한 추천할만한 이야기를 하고 있습니다. 솔직히 저는 약간 다른 의견입니다만, 어찌됐던 아주 좋은 글이라 번역을 해서 공유를 합니다.

좋은 글들이라 쉽지 않은 시간 내서 번역하고 공유하는데요, 읽으시는 분들이 별로 호응도 업고 의견도 없고, 댓 글로 없어서 의욕을 점점 상실하고 있습니다.  

(사실 대충 번역만 하고 글도 다듬지 않고 올립니다, 그러니 얼마나 불편하시겠어요! 저도 압니다, 네!)

그래도, 계속 열심히 할 수 있게 용기 좀 주세요 ^^

내용이 길지 않으니 편하게, 잠깐 읽어보시고 의견주세요.

그럼, 즐.독.하세요~~~

본문 시작

Optimizer 모델은 실행 계획 선택 시 여러 가지를 가정한다. 때론 그 가정이 특정 쿼리와 데이터 집합에 대해 잘못된 선택의 문제 원인이 되기도 하는데, 그 중 하나가 바로 Optimizer 팀에서 “The Sorted Seek Problem”라고 부르는 문제이다. 이는 Clustered Index와 Heap 그리고 Bookmark Lookup 쿼리 계획 선택에 영향을 미치는 내용이다.

Heap에 대한 Bookmark Lookup 연산이나 Index의 Index Seek 연산에 대한 비용 산정 시 이러한 작업을 일련의 랜덤 I/O로 취급한다. 랜덤 I/O는 디스크 암의 위치 이동과 특정 섹터 검색을 위한 회전 동작을 요구하므로 순차 I/O에 비해 훨씬 더 고 비용의 작업이다. 해당 페이지를 찾으면 다음 재사용을 위해 버퍼 풀(메모리)에 로드하고 캐싱 처리를 한다. 또한 그 계획의 비용을 산정하는 코드는 평균적으로 봤을 때 이후 연산에서 캐시 상의 페이지가 얼마자 자주 액세스되는지 이해하고 있으며, 더불어 그러한 메모리 기반 액세스가 이전의 디스크 액세스보다 훨씬 더 저렴한 비용으로 산정하고 있다.

쿼리에서 이러한 액세스 방법을 이용해 데이터를 조회하는 경우, 비용 산정 코드는 해당 데이터가 테이블 혹은 인덱스 페이지에 균등하게 분포되었다고 가정한다. 이는 각 행이 디스크로부터 로드될 것이라는 가정을 유발해서 결국 행 별 검색 작업이 매우 고 비용이라고 판단하게 만든다. (경우에 따라, 필요한 모든 페이지가 이미 버퍼 풀에 존재하므로 고 비용의 랜덤 I/O가 불 필요할 수 있음에도 불구하고)

실제로, 대개의 경우 데이터는 랜덤하게 분포되어 있지 않다. 사실 대부분의 많은 사용자 데이터가 물리적으로 군집된 정렬 상태로 입력된다. 예를 들어 특정 주문에 대한 주문 상세를 입력한다면 이는 주문이 생성된 시점에 순차적으로 이루어질 것이다. 즉 특정 페이지 혹은 소수의 페이지에 데이터를 군집된 형태로 저장되는 것이다. 그 결과, 비용 산정 모델이 예측하는 것 보다 훨씬 더 빠른 시간 안에 Bookmark Lookup 이나 Index Lookup을 수행할 수 있다. 결국 Query Optimizer는 실제보다 더 느린 계획을 선택할지도 모르는 것이다. 잘못된 예측으로 인해서.

데이터가 균등하게 분포된 시나리오도 또한 존재하므로, 위 가정을 변경하는 것 또한 옳지 않다. 향후에 이러한 문제들을 해결할 수 있는 기술이 제공될 것이다. 현재로선 이런 시나리오에 적합한 해결 방안은 인덱스를 사용하도록 쿼리 힌트 등으로 강제하는 것이다.

역주: 헉! 그럴 수는 없다. 다른 방법들을 먼저 고려해야 한다.

고객의 실제 시나리오를 보자. 테이블은 80개의 칼럼과 5.5백만 행을 가진다. 쿼리에서 검색 행수는 대략 21,000 행이다.

-- 1) Optimizer가 산출한 실행 계획: 병렬 Table Scan

SELECT * FROM CustTable  WHERE col='someconstvalue'

  |--Parallelism(Gather Streams)

       |--Table Scan(OBJECT:(CustTable), WHERE:(col=’someconstvalue’))

Table ‘CustTable’. Scan count 3, logical reads 416967, physical reads 22, read-ahead reads 413582, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Completed in 3 minutes 9 seconds

-- 2) index 힌트로 강제한 경우

SELECT * FROM CustTable with (index=idxoncol) WHERE col='someconstvalue'

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1005]) WITH UNORDERED PREFETCH)

       |--Index Seek(OBJECT:(idxoncol), SEEK:(col=’someconstvalue’) ORDERED FORWARD)

       |--RID Lookup(OBJECT:(CustTable), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Table 'CustTable'. Scan count 1, logical reads 21167, physical reads 387, read-ahead reads 1354, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Completed in 11 seconds

첫 번째 쿼리가 원본 쿼리이며 그 실행 계획은 해당 테이블을 병렬 스캔 하는 것으로 결정되었다. 조건은 비 검색인수(Non-SARG) 조건자로 WHERE절에서 처리된다. 처리 시간은 대략 3분. 두 번째 실행 계획은 Bookmark Lookup을 수행하도록 인덱스 힌트로 강제한 결과이다. 대략 11초 걸렸다.

만일 한 번의 디스크 랜덤 액세스에 대략 10ms가 걸린다고 가정하면, 1초에 100번 이상은 수행할 수 없다. 단일 디스크를 대상으로 이러한 랜덤 I/O가 21,000 번 일어난다면 아마도 3분 이상이 걸릴 것이다. 그런데 실제로 21,000 랜덤 I/O보다는 작게 발생할 것이다. 이는 위 결과에서 statistics I/O 출력 값을 보면 387 + 1354가 실제 물리적인 페이지라는 것을 알 수 있게 해준다.

위의 고객 데이터는 실제 물리적으로 군집을 이룬 상태였다. 따라서, 한 번 Bookmark Lookup을 통해 메모리(버퍼 풀)로 읽은 들인 페이지 내의 행들은 랜덤 I/O가 필요 없으며, 이를 통해 쿼리 성능에 큰 도움을 준다.

쿼리가 명확히 이런 사례에 해당한다면, 쿼리 힌트 사용을 권장한다.

 역주: 글쎄 안 된데두~ ^^