Allocation Ordered Scan 과 READUNCOMMITTED

안녕하세요. 이스트럭(강동운) 입니다.

오래전부터 이 부분을 정리해서 올리려고 했는데.. 오늘 답변을 하다가 간단하게나마 정리해서 올리게 됩니다.

우선 이 내용을 이해하기 위해서는.. Mixed Extent(혼합익스텐트), Uniform Extent(균일 익스텐트), IAM(Index Allocation Map)을 알고 있다는 전제하에 설명을 드립니다.

위의 정보에 대해서 잘 모르신다면.. 아래 글을 우선 읽어주시기 바랍니다~^^

Mixed Extent & Uniform Extent: http://www.sqler.com/347983

IAM(Index Allocation Map): http://www.sqler.com/355204

READUNCOMMITTED를 모르신다면...

트랜잭션 격리수준: http://www.sqler.com/439266   의 2011_09_07_발표자료(강동운).pptx 첨부파일

READUNCOMMITTED에서 정렬 수준이 보장되지 않는 이유는..

Allocation Ordered Scan 때문에 그렇습니다.

Allocation Ordered Scan은...  IAM(Index Allocation Map) 페이지를 기반으로..

Mixed Extent를 먼저 읽고 난 후에.. Uniform Extent를 순서대로 읽게 됩니다.

Index Scan 또는 Clustered Index Scan은.. 인덱스의 더블링크드리스트를 기반으로 Next page 포인터 형태로 읽어가는 것 입니다.

Allocation Ordered Scan에는 조건이 있습니다.

1. 실행계획에 Ordered 연산자가 false (order by 가 없는 경우)

2. READ UNCOMMITTED 이거나 읽기 전용 환경인경우

3. SQL Server 2005 이상에서 테이블의 페이지가 64이상인 경우..

위 3가지 조건을 모두 만족해야.. Allocation Order Scan 으로 돌아가게 됩니다.

(하지만 실행 계획엔 Clustered Index Scan 또는 Index Scan으로 표시 됩니다.)

아래 예제를 잠시 설명드리면..

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/11/09/when-can-allocation-order-scans-be-used.aspx

여기 예제는.. 10~100이란 숫자를 처음 while 문을 통해 넣고.. (한 컬럼당 5000 bytes 므로.. 한페이지에 한개씩)

그 다음... 1~9까지 넣게 됩니다.

10을 넣을때 Mixed Extent 1번

11을 넣을때.. Index 상위 레벨이 생기므로.. 인덱스 상위 레벨에 Mixed Extent 2번

11: Mixed Extent 3번

12: Mixed Extent 4번

13: Mixed Extent 5번

14: Mixed Extent 6번

15: Mixed Extent 7번

16: Mixed Extent 8번

까지 할당되고.. 그 후부턴.. Uniform Extent가 할당됩니다.

추가로 넣은 1~9 ...이 데이터 역시 Uniform Extent에 할당이 됩니다.

결국 이걸 Allocation Order Scan으로 읽게 되면.. 10~100까지 먼저 읽은 다음에.. 1~9 까지 읽게 되는거죠

참고 자료 ppt: http://download.microsoft.com/download/7/a/b/7ab8283e-a3a0-4185-818b-ab7b1fc6300b/DAT427 - Consistency of Reads.ppt

감사합니다 ^^

작성자: eastluck

작성일: 2012.02.03