로컬변수 사용에 따른 이상한 실행계획

목적 : 로컬 변수 사용시 실행계획상 예상행수가 달라지는 현상의 이유를 파헤처 보자

 

현상 :  Duration 및 IO가 너무 심한 새벽 시간대 JOB을 확인 중 로컬변수 사용과 파라매터 매개 변수 사용에 따라 부하비용 큰 차이 발생

 

더미 테이블 내역

Select * From Test

Seq         dt             dumy1     dumy2           dumy3                                      dumy4

1        20100206         54          A4AD4         5B747A05-4                   452687CD-15E1-48B4-ABB4-B353273F6B57

 

 

일단 프로시저 및 데이터 캐쉬를 비운 후

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

1번 : NCI SEEK  실제 행수 5000건  예상행수 5000건

 Select * from Test

Where dt >= '20110928'

 

   

 

 

2번 : 인덱스 CIScan 실제 행수 5000건  예상행수 90000건

declare @dt char(8)

set @dt ='20110928'

Select * from Test

Where dt >= @dt

 

 

이누무 옵티마이저 또 시작이네 -_-;;

IO 차이가 무려 10배 이상이다.

차이점은 WHERE 절에 로컬변수 사용를 사용했는냐 아니냐의 차이다

 

가끔 SP 안에 코드값을 로컬변수로 사용하는 경우를 볼 수있다.

이런 경우 옵티마이저는 실행하기 전까지 분포도를 계산 할 수가 없다고 한다.

그러므로 하기와 같이 정해진 분포도를 사용 한다고 한다.

예를 들어 로컬변수를 사용할 경우 1000건인 데이터가 존재시 Where 절에  > 조건 사용시 1000건의 30%  인 3000건이 예상행수로 계산된다는 의미다.

 

   조건                       분포

>=, > , <, <=               30

Between                     25

 

해결책은 간단하게 하기와 같이  ‘매개변수화’ 시키면 된다. SP만들거나 EXEC()써서 값을 직접 넣는 것처럼 만들거나 또는 sp_executesql을 쓰면

된다.

다음달 부터 몇몇 부하 JOB에 대하여 변경 좀 해야겠다.

 

EX) 

create proc testsp

@DT char(8)

As

Set nocount on

Set transaction isolation level read uncommitted 

Select * from Test

Where dt >= @dt

 

exec testsp '20110928'