실행계획을 보면 예상행수라는 항목이 있습니다. 이 수치는 플랜을 생성시 통계 정보를
참조하여 구하게 되며 실행계획 생성시 많은 영향을 주게 됩니다.
만약 플랜 생성시 예상행수가 100만개라면 랜덤IO가 발생하는 작업 보다 순차IO가 발생하는
작업이 보다 효율적이라 판단하여 테이블을 SCAN하도록 풀릴 수 있습니다. 만약 1건이라면
랜덤IO로 풀릴 수 있습니다.
이렇듯이 예상행수는 적절한 플랜을 생성하기 위해 중요한 데이터 중 하나입니다.
그럼 이 데이터를 사용자가 조작할 수 있는지 그리고 지정할 필요가 있는지에 대해서
살펴보도록 하겠습니다.
1. 내 맘대로 예상행수를 지정하자.
BOL을 보면 예상행수를 지정할 수 있는 힌트는 제공되지 않습니다.
하지만 힌트중 FAST라는 힌트가 있습니다.
BOL에는 아래와 같이 설명하고 있습니다.
FAST number_rows
첫 번째 음수가 아닌 정수 number_rows를 빨리 검색하기 위해 쿼리를 최적화하도록 지정합니다.
첫 번째 number_rows를 반환한 후에 쿼리는 계속 실행하여 전체 결과 집합을 만듭니다.
BOL에서 설명하고 있듯이, FAST힌트를 사용하면 특정 N개의 행이 가장 빠르게 리턴 될 수
있도록 플랜을 생성하게 됩니다. 예를 들면 리턴된 결과가 10000개의 행인데,
힌트를 안 줄 경우는 Hash Join으로 풀리지만 FAST 10을 줄 경우는 LOOP JOIN으로 처리
될 수 있습니다. LOOP JOIN은 인덱스만 적절하게 있다면 일부 데이터에 대해서는 바로
리 턴할 수 있기 때문입니다. 간단하게 FAST힌트는 N개의 값이 가장 빠르게 리턴 될 수 있는
플랜을 생성하라는 힌트입니다. 그렇다고 해서 FAST 힌트를 준다고 해서 빠른것은 아닙니다.
N개의 행은 빠르게 나오겠지만, 모든 결과가 완료되는 것은 더 느릴 수 있습니다.
이러한 FAST힌트의 N값은 플랜의 예상행수로 처리하여 플랜을 생성하게 됩니다.
이점을 이용하여 예상행수를 변경해서 플랜을 생성해야 할 때 사용 할 수 있습니다.
아래 테스트는 FAST힌트에 따른 예상행수가 변경되는지 테스트 했습니다.
처음은 FAST힌트가 없는 경우는 실제 행수인 249와 근접한 234의 예상행수를 볼 수 있으며
FAST 힌트에 1을 추가하게 되면 예상행수가 1로 변경되는 것을 알 수 있습니다.
만약 실제행수보다 FAST힌트에 많은 값을 입력하게 입력된 값을 무시하고
통계 정보를 기반으로 예상 행수를 산정하게 됩니다.
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
option(fast 1)
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
option(fast 100)
2. 왜 통계가 있는데 예상행수를 지정할 필요가 있을까?
그럼 왜 통계정보를 관리하고 있는데~ 굳이 예상행수를 내가 입력해야 하는 경우가 있을까요?
전에 설명했듯이 예상행수는 통계데이터를 참조하여 구하게 됩니다.
"예상행수" 말에서도 알 수 있듯이 실제 행수가 아닌 예상수치이며 이 데이터는 통계를 기반으로
하였기에 부정확할 수 있습니다. 그럼 왜 통계는 정확할 수 없을까요?
크기가 크지 않은 테이블이라면 통계 히스토그램에서 어느 정도 일치하는 행수를 알 수
있으나 큰 테이블이라면 통계 정보가 부정확해질 수 있습니다.
왜냐하면 DB에서 자동으로 발생하는 통계 업데이트는 샘플링으로 처리되며
또한 히스토그램의 스텝은 200개 밖에 안되기 때문입니다.
“그럼 통계 히스토그램의 스텝을 많이 늘리면 이런 문제를 개선할 수 있지 않을까?”
라는 의문이 들 수 있습니다.
맞습니다. 히스토그램의 스텝이 많아 질수록 통계데이터는 더 정확하게 사용될 수 있습니다.
하지만 스텝이 늘어남에 따라 관리 비용 또한 증가할 것이며, 얼마만큼 늘려야 할지도 의문입니다.
예전 SQL Server에서는 스텝이 300개였는데~ 버전업이 되면서 200개로 줄어들었다는 건~
무언가의 성능적인 이유 또는 100개정도의 차이가 통계데이터 정확성에 큰 영향을 주지
않았을까 하는 개인적인 생각이 듭니다.
만약 SQL Server 2008을 사용하신다면, 필터된 통계를 생성할 수 있기에 200개로 제한된
통계히스토그램을 원하는 만큼 늘릴 수는 있습니다.
이번에 이야기 하고자 한 내용이 통계는 아니기에~ 통계에 대한 이야기는 여기서 마치겠습니다.
다음 기회에 통계에 대해서 조금 더 깊이 살펴보도록 하겠습니다.
3. FAST힌트와 인덱스 힌트
그럼 다시 돌아와서~ FAST힌트로 인한 실행계획이야기를 다시 해보겠습니다.
위에서 보았듯이 통계는 한계를 가지고 있어 적절하지 못한 실행계획이 생성되어 서비스에 영향을
줄 수 있습니다. 이럴 때 인덱스 힌트 또는 쿼리 힌트를 사용하여 실행계획을 고정하고 있습니다.
예를 들어보면
~ 하나의 테이블은 1000만 건이 있습니다.
~ 800만 건은 모두 고유하지만
~ 나머지 200만 건은 모두 NULL입니다.
~ 또한 통계데이터가 부정확합니다.
이러한 경우 사용자는 데이터의 분포도를 알고 있기에 NULL인 경우는 클러스터 인덱스를
SCAN, 아닌 경우에는 넌 클러스터 인덱스SEEK라고 인덱스 힌트를 통해서 처리 할 수 있습니다.
하지만 인덱스 힌트는 관리적인 이슈가 있습니다. 인덱스 힌트는 인덱스 이름 또는 인덱스 ID값을
입력해서 사용게되는데 이때 사용한 인덱스 이름 또는 ID값이 변경되게 되면 SP는 오류가
발생하게 됩니다. SP오류로 인해서 서비스에 영향을 줄 수 있기에 인덱스 힌트를 사용한 SP의 경우는
따로 관리해야할 이슈가 생깁니다. 하지만 인덱스 힌트가 아닌 FAST힌트를 통해서도 처리할 수
있습니다. NULL인 경우는 FAST 200만, NULL이 아닌 경우는 FAST 1로 지정한다면 원하는
플랜으로 처리 할 수 있습니다.
위와 같이 FAST힌트를 사용하면 예상행수를 변경할 수 있고 이러한 동작으로 일부 인덱스
힌트를 대체 할 수 있다는 것을 살펴보았습니다. 하지만 모든 경우에서 FAST힌트가 인덱스
힌트를 대신할 수 없기에 상황에 맞춰 적절하게 사용해야 합니다.
sqler.com // sqlleader.com
hyoksong.tistory.com
