Stored Procedure Issues - 3부
프로시저내 임시 테이블을 사용할 시에 재컴파일 된다는 것은 어찌 보면, 당연한 결과이다. 왜냐면, 임시 테이블이라는 것은 TempDB에 생성되는 것으로 영속적인 데이터베이스 개체가 아니라, 세션 동안만 운영되는 것이다. 따라서, 이러한 임시 테이블을 참조하도록 프로시저가 작성되어 있다면, 프로시저의 실행 계획은 영속적이지 않은 것이 된다. 따라서, 프로시저를 실행할 때마다 컴파일이 이루어지게 된다.
임시 테이블을 사용하는 프로시저의 경우에 임시 테이블이 위치가 어디에 있느냐에 따라서 재 컴파일링의 빈도가 달라지게 된다. 임시 테이블은 세션 동안만 개체 아이디를 유지한다. 아래와 같은 프로시저가 있다면, 임시 테이블 #t는 프로시저가 실행되는 동안만 개체 아이디를 유지하는 것이다. 프로시저에서는 #t 임시 테이블이 접근 되지만, 프로시저 밖에서는 #t 테이블에 엑세스 되지 않는다.
create proc temptest01
as
create table #t(id1 char(1))
insert #t values('1')
select * from #t
--------------------
(1개 행 적용됨)
id1
----
1
(1개 행 적용됨)
서버: 메시지 208, 수준 16, 상태 1, 줄 2
개체 이름 '#t'이(가) 잘못되었습니다.
< 리스트 6 > 프로시저에서 임시 테이블 사용
동일한 프로시저에서 생성한 임시 테이블에 접근하는 경우에는 프로시저는 재 컴파일 되지 않는다. 다음 그림은 프로필러(Profiler)에서 SP:Starting, SP:StmtCompleted, SP:Recompile 이벤트 추적을 걸고서 지켜본 화면이다. 재 컴파일은 없었다.
.gif)
<그림 5> 프로시저 내에서 임시테이블 이용 추적
하지만, 동일한 작업을 두 개의 프로시저로 나누어보자. Temptest02 프로시저는 프로시저 내에서 임시 테이블을 생성한다. Temptest03 프로시저는 02 프로시저에서 생성한 임시 테이블을 조회한다. 임시 테이블의 개체 아이디 유지가 Temptest02 프로시저가 종료할 때까지 유효하기 때문에 Temptest03 프로시저도 이러한 임시 테이블을 이용하는 것이 가능하다.
create proc temptest02
as
create table #t(id1 char(1))
insert #t values('1')
exec dbo.temptest03
create proc temptest03
as
select * from #t
exec dbo.temptest02
-------------------------
(1개 행 적용됨)
id1
----
1
(1개 행 적용됨)
< 리스트 7 > 호출되는 프로시저에서 재컴파일 발생
하지만, 호출되는 Temptest02 프로시저 관점에서 생각하면, 임시 테이블의 이름은 #T로 항상 동일하지만, SQL 서버가 실행 계획에서 참조한 실제 개체 아이디 값은 계속해서 변경되게 된다. 따라서, 항상 다른 개체라고 볼 수 있는 것이다. 대상이 되는 개체 참조가 변경되므로, 호출되는 Temptest03 프로시저는 재컴파일 되어야 한다.
.gif)
< 그림 6 > 호출되는 프로시저에서 재컴파일 발생
이와 유사하지만 약간은 틀린 경우가 한가지 더 있다. 배치 세션에서 생성한 임시 테이블을 여러 프로시저들이 참조하는 경우는 어떨까? 이것은 호출되는 프로시저의 경우와 유사하다. 임시 테이블은 배치 세션에서 생성되었으므로 배치 세션이 끝날 때까지 임시 테이블의 개체 아이디가 유지되게 된다. Begin..End까지가 하나의 배치 세션이다. 임시 테이블이 이 세션 동안 존속한다.
create proc temptest04
as
select * from #t
begin
create table #t(id1 char(1))
insert #t values('1')
exec dbo.temptest02
end
--------------------
(1개 행 적용됨)
(1개 행 적용됨)
id1
---- 1 (1개 행 적용됨)
< 리스트 8 > 배치 세션에서 임시테이블 이용으로 재컴파일 발생
저장 프로시저는 당연히 재 컴파일된다. 아래의 프로필러 그림을 보면 알 수 있다.
.gif)
< 그림 7 > 배치 세션에서 임시테이블 이용으로 재컴파일
케이스가 다르다는 것은 임시 테이블이 배치 세션 별로 유지되기 때문에, 해당 임시 테이블을 다시 사용하는 저장 프로시저의 경우에는 해당 세션 동안 개체 아이디가 유지되므로 재 컴파일이 일어나지 않는다는 것이다. 좀더 엄격하게 말하면, 임시 테이블이 각 사용자 세션 별로 다른 이름을 가지게 되므로, 각 사용자 세션 별로 한번 재 컴파일된 이후에는 재 컴파일이 이루어지지 않는다.
아래의 예를 살펴보자. 여기에서는 세션 동안 임시 테이블을 참조하는 동일한 저장 프로시저가 두 번 사용되었다.
Begin
create table #t(id1 char(1))
insert #t values('1')
exec dbo.temptest03 --1st sp
exec dbo.temptest03 --2nd sp
drop table #t
end
(1개 행 적용됨)
id1
----
1
(1개 행 적용됨)
id1
----
1
(1개 행 적용됨)
< 리스트 9 > 배치 세션에서 프로시저 실행
저장 프로시저는 첫 번째는 개체 아이디가 변경되었으므로, 프로시저 재 컴파일이 일어났다. 하지만, 두 번째에서는 캐싱된 계획을 그대로 사용하고 있다. 왜냐면, 임시 테이블의 개체 아이디가 유지되었기 때문이다.
.gif)
< 그림 8 > 배치 세션에서 프로시저 실행
이제는 반대의 경우들을 생각해보자. 저장 프로시저는 캐시된 계획을 이용하도록 하는 것이 좋다. 그래야, 파싱이나 최적화 과정이 필요 없으니깐. 프로시저의 재 컴파일은 되도록 없어야만 한다. 프로시저 재 컴파일은 락 컨텐션을 발생 시켜 서버 부하를 늘리게 할 수 있다. 이것이 공통적인 상식이다. 하지만, 상식의 반대로, 저장 프로시저 재 컴파일을 유도해야 할 필요가 있다.
프로시저가 캐시된 실행 계획을 재사용하게 될 때, 큰 문제점이 하나 있다. 캐시된 계획이라는 것은 고정된 실행 계획을 말하는 것이다. 캐시된 계획은 프로시저를 첫 번째 사용할 때 발생되는 것이다.
다음의 두 개의 쿼리를 보면, Where절이 파라메터만 다를 뿐이지 동일한 쿼리라고 볼 수 있다. 첫 번째 쿼리는 4행을 반환하고 두 번째 쿼리는 830행을 반환한다.
select * from orders where orderdate between '19960725' and '19960730' select * from orders where orderdate between '19910101' and '19990101'
< 리스트 10 > 파라메타가 다른 동일 조건 쿼리
실행 계획을 살펴보면 두 개의 임시 쿼리는 서로 다른 계획을 가지고 있다. 왜냐면 NC 인덱스를 이용하는 경우에 대량의 데이터 페이지를 가져오려면 차라리 테이블 스캔(클러스터드 인덱스 스캔)을 하는 편이 오히려 효율적이기 때문에 SQL 서버 옵티마이저는 두 번째 쿼리에서 테이블 스캔으로 유도한 것이다. 이는 매우 정상적이고 올바른 판단이다.
.gif)
< 그림 9 > 실행 계획이 다른 동일한 쿼리
동일한 작업을 하는 프로시저를 생성하고 똑같이 실행해 보자.
create proc RecomOrders @sdate char(8), @edate char(8) as select * from orders where orderdate between @sdate and @edate go exec dbo.recomOrders '19960725','19960730' exec dbo.recomOrders '19910101','19990101'
< 리스트 11 > RecomOrders 프로시저
프로시저의 실행 계획은 동일하다. 왜냐면 첫 번째 컴파일 된 계획이 아직 캐쉬에 남아있기 때문인 것이다. Where 절의 조건에 다른 선택도 및 대안이 되는 계획을 조사하지 않기 때문에 캐쉬의 계획을 그대로 사용한다. 따라서 두 번째 쿼리는 임시 쿼리보다도 성능이 나쁠 수 있다.
.gif)
< 그림 10 > 동일한 계획을 가제게 되는 프로시저
실제 I/O 비용은 어떨까? 그래서 IO 통계 옵션을 키고 추적해보았다. 논리적 읽기를 보면 임시 쿼리가 21 페이지인데 비해서 저장 프로시저는 1664 페이지이다. 극심한 성능 저하라 할 수 있다.
set statistics io on -- 첫 번째 케이스 select * from orders where orderdate between '19960725' and '19960730' select * from orders where orderdate between '19910101' and '19990101' 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 10, 물리적 읽기 수 0, 미리 읽기 수 0. 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 21, 물리적 읽기 수 0, 미리 읽기 수 0. -- 두 번째 케이스 exec dbo.recomOrders '19960725','19960730' exec dbo.recomOrders '19910101','19990101' 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 10, 물리적 읽기 수 0, 미리 읽기 수 0. 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 1664, 물리적 읽기 수 0, 미리 읽기 수 0.
< 리스트 12 > 재컴파일 추적
이제 캐시를 깨끗하게 비우고 새롭게 시작해 보자. dbcc freeproccache 명령을 사용하면 서버의 캐쉬를 날려준다. 제발~ 테스트 서버에서만 이 명령을 이용하자.
dbcc freeproccache exec dbo.recomOrders '19910101','19990101' exec dbo.recomOrders '19960725','19960730'
< 리스트 12 > 순서를 뒤바꾼 프로시저 실행
실행 계획을 보면 테이블 스캔 계획으로 프로시저가 실행되는 것을 알 수 있다.
.gif)
< 그림 11 > 거꾸로 실행 후 프로시저 계획 비교
이러한 현상을 막는 가장 쉬운 방법은 프로시저를 재컴파일 하는 것이다. WITH RECOMPILE 옵션을 주면 프로시저를 재 컴파일해서 실행한다. 이제 임시 쿼리에서의 성능 정도가 프로시저에서도 나온다.
exec dbo.recomOrders '19960725','19960730' exec dbo.recomOrders '19910101','19990101' with recompile (4개 행 적용됨) 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 10, 물리적 읽기 수 0, 미리 읽기 수 0. (830개 행 적용됨) 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 21, 물리적 읽기 수 0, 미리 읽기 수 0.
< 리스트 13 > With Recompile 옵션으로 프로시저 실행 계획도 마찬가지이다.
.gif)
< 그림 11 > With Recompile로 실행된 프로시저
이러한 현상을 줄이기 위해서는 각 목적에 맞도록 프로시저를 분리해야만 한다. 이를 선택적인 컴파일(Selective compile)이라고 한다. 프로시저들을 가장 작은 목적 별로 작성하고 프로시저에서 다른 프로시저들을 호출하게 하면 재 컴파일이 필요한 프로시저를 전체 프로시저가 아니라 작은 프로시저 하나만으로 처리하도록 하는 것이 가능하다. 즉, 프로시저들을 중첩 시는 것 전략이 매우 효과적이라는 것이다.
또 한가지 방법은 SP_EXECUTESQL 프로시저를 이용하는 것이다. 이 프로시저는 파라메터 바인딩을 가능 하게하고 동일한 쿼리인 경우에는 실행 계획을 캐시 하도록 할 수 있다. 일종의 인 라인 프로시저라고 할 수 있다.
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
< 리스트 14 > SP_EXECUTESQL 프로시저 이용
끝으로 DEEP INSIDE SQL SERVER 2000에 관련되어 질문하고자 하시는 분이 있으면, 필자의 전자 메일 주소로 [SQLMAG]라는 말머리를 붙여서 메일을 보내기 바란다. 메일 주소는 역시 다음과 같다. kind511@dreamwiz.com
최종 수정일 : 2005년 3월 16일