MS-SQL Server

Sorted Seeks 에 대한 생각…

송혁 - HyokSong 2008. 2. 21. 16:25

아래의 김정선님이 번역한 글을 참조하여, 테스트 및 고민을 하였습니다.

번역본: http://blog.naver.com/visualdb/50028175660
원문 : http://blogs.msdn.com/queryoptteam/archive/2006/04/12/575241.aspx


위에서 설명하는 비슷한 시나리오를 생각해 보면,
어떤 회사의 주문 테이블이 있고, 주문 테이블은 생성일 순으로 일련되게 데이터가 저장되고 있습니다.
생성일에 넌 클러스터 인덱스가 있으며, 데이터는 = 로 비교하며, 선택도는 0.5% 정도 입니다.

이런경우에 그냥 쿼리를 수행하여 처리하면, 각각의 환경마다 틀리겠지만 테이블 SCAN을 하여 쿼리의 결과를 던져 주게 됩니다.
하지만, 위와 같은 테이블이라면, 생성일로 걸려있는 넌클러스터 인덱스를 활용하여 처리 하면 보다 좋은 성능을 가져 올수도 있습니다.
(물론 생성일을 클러스터 인덱스로 건다면, 문제가 끝나지만 그렇지 못한 환경을 이야기 하고 있는 겁니다.)

아래 테스트는 일련되게, 일련되지 않게 데이터를 입력하여 테스트를 하였습니다.
아래 인덱스 힌트를 준 플랜을 잘 살펴보면 인덱스 SEEK한 후 BML을 하기전, RID를 가지고 정렬하는 것을 볼 수 있습니다.
보다 좋은 cachehitRatio를 가지기 위해서 SORT연산자가 추가된 것을 확인 할 수 있습니다.

일련되게 저장된 경우 FULL SCAN보다 BML에서 성능이 좋을 수 있는 이유는 물리적 디스크의 순차적 읽기를 하여, 보다 빠른 물리적 응답으로인한 것이라고 생각되지는 않습니다.
이 경우 미리읽기가 가장 큰 변수일 것 같은데, 아래와 같은 쿼리는 어쩔 수 없이 IAM기반의 미리읽기가 아닌, 인덱스 레벨의 미리읽기가 발생하여 제대로된 순차 읽기를 할 수 없기 때문입니다.

그럼 무엇 때문에 차이가?
일련되게 데이터가 저장되었기 때문에 하나의 페이지에는 같은 키값의 데이터가 밀집되었으므로, 보다 적은 페이지에, 원하는 데이터가 모두 존재하기 때문에 보다 좋은 성능을 가져온게 아닌가 생각이 듭니다.

다른 방법으로도 처리 할 수 있지만,
이러한 특이한 비지니스를 가지고 있다면 충분히 고려를 해볼 수는 있을 것 같습니다.

다른 의견 및 잘못된 부분이 있다면 알려주시길 바랍니다.

송 혁, SQL Server MVP
sqler.pe.kr
nexondbteam.tistory.com


@@버전
Microsoft SQL Server 2005 - 9.00.3175.00 (Intel X86)
    Jun 14 2007 09:20:57
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


USE TEST

GO

DROP TABLE tbl1

DROP TABLE tbl2


--정렬된 샘플 데이터 만들기

SELECT TOP 10000000

    cast(ROW_NUMBER() over(order by A.id) / 50000 as int) col1

    ,ROW_NUMBER() over(order by A.id) col2

    ,cast('HyokSong' as char(50))col3

INTO tbl1

FROM sys.sysindexes A, sys.sysindexes B,sys.sysindexes C,sys.sysindexes D,sys.sysindexes E

ORDER BY ROW_NUMBER() over(order by A.id)


--정렬되지 않은 샘플 데이터 만들기

SELECT *

INTO tbl2

FROM tbl1 with(nolock)

ORDER BY NEWID()


--넌클 인덱스 추가

create index ix_tbl1_col1 on tbl1(col1)

create index ix_tbl1_col1 on tbl2(col1)


/*

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

*/


/*--BP 날리기

checkpoint 1

DBCC DROPCLEANBUFFERS

*/


--=====================================[정렬된]

SELECT * FROM tbl1 where col1 = 98

|--Parallelism(Gather Streams)

|--Table Scan(OBJECT:([TEST].[dbo].[tbl1]), WHERE:([TEST].[dbo].[tbl1].[col1]=(98)))

테이블'tbl1'. 검색수5, 논리적읽기수87721, 물리적읽기수0, 미리읽기수87465

CPU 시간= 5625ms, 경과시간= 8256ms.


SELECT * FROM tbl1 WITH(INDEX(2)) where col1 = 98

|--Parallelism(Gather Streams)

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

|--Sort(ORDER BY:([Expr1004] ASC))

| |--Compute Scalar(DEFINE:([Expr1004]=BmkToPage([Bmk1000])))

| |--Index Seek(OBJECT:([TEST].[dbo].[tbl1].[ix_tbl1_col1]), SEEK:([TEST].[dbo].[tbl1].[col1]=(98)) ORDERED FORWARD)

|--RID Lookup(OBJECT:([TEST].[dbo].[tbl1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

테이블'tbl1'. 검색수5, 논리적읽기수50126, 물리적읽기수386, 미리읽기수169

테이블'Worktable'. 검색수0, 논리적읽기수0, 물리적읽기수0, 미리읽기수0

CPU 시간= 704ms, 경과시간= 1320ms.


--=====================================[정렬되지 않은]

SELECT * FROM tbl2 where col1 = 98

|--Parallelism(Gather Streams)

|--Table Scan(OBJECT:([TEST].[dbo].[tbl2]), WHERE:([TEST].[dbo].[tbl2].[col1]=(98)))

테이블'tbl2'. 검색수5, 논리적읽기수87721, 물리적읽기수0, 미리읽기수87657

CPU 시간= 5735ms, 경과시간= 7029ms.


SELECT * FROM tbl2 WITH(INDEX(2)) where col1 = 98

|--Parallelism(Gather Streams)

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

|--Sort(ORDER BY:([Expr1004] ASC))

| |--Compute Scalar(DEFINE:([Expr1004]=BmkToPage([Bmk1000])))

| |--Index Seek(OBJECT:([TEST].[dbo].[tbl2].[ix_tbl1_col2]), SEEK:([TEST].[dbo].[tbl2].[col1]=(98)) ORDERED FORWARD)

|--RID Lookup(OBJECT:([TEST].[dbo].[tbl2]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

테이블'tbl2'. 검색수5, 논리적읽기수50126, 물리적읽기수4, 미리읽기수38262

테이블'Worktable'. 검색수0, 논리적읽기수0, 물리적읽기수0, 미리읽기수0

CPU 시간= 2578ms, 경과시간= 13153ms.


/*

SET STATISTICS PROFILE OFF

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

*/