저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server의 저장프로시저(Stored Procedure)는 보안이나 성능적인 측면에서 많은 이점을 제공한다. 성능적인 측면에서 가장 큰 장점은 프로시저가 처음 한번 실행 될 때 컴파일 되고 그 이후로는 컴파일 과정없이 재사용되는 것이다. 실제 저장프로시저의 컴파일(재컴파일) 비용은 매우 크므로 이 부분을 최대한 발생하지 않도록 해야 한다.

 

다음의 케이스는 어떤 저장프로시저가 실행 될 때 항상 재컴파일로 인하여 성능 문제가 발생했던 사례이다.

 

저장프로시저 안에서 사용된 임시 테이블을 ALTER 명령어를 통하여 수정하였을 때 재컴파일이 발생 한다. 다음 예제 스크립트를 통해 확인해 보자.

 

테스트에 사용할 저장 프로시저를 생성 한다.

use tempdb

go

create proc proc_test

as

set nocount on

create table #t1 (c1 int)

create table #t2 (c1 int)

insert into #t1 values (1)

insert into #t2 values (1) --this will always recompile even the alter statement is on a different temp table

alter table #t1 add c2 as c1

 

프로파일러를 통하여 재컴파일 이벤트를 캡처 한다.

 

위에서 생성한 프로시저를 실행 한다.

exec proc_test

 

 

프로시저가 실행 될 때마다 프로파일러를 통하여 재컴파일 이벤트가 발생한 것을 확인 할 수 있다. 이 과정을 지연된 컴파일이라 부른다.

 

사용자가 저장 프로시저를 실행 할 때 캐시에서 사용할 수 있는 상태가 아니면 SQL Server가 프로시저를 로드하고 계획을 컴파일 한다. 컴파일된 계획은 재컴파일이 발생 할 때까지 캐시에 저장되며 다음 호출시 재사용 된다. 재컴파일이 발생하는 경우는 다음과 같다.

  • CREATE PROCEDURE 또는 EXECUTE 문에서 WITH RECOMPILE 절을 사용할 경우
  • 제약 조건, 기본값, 규칙의 추가/제거를 포함하여 참조된 개체의 스키마가 변경 될 경우
  • 프로시저가 참조한 테이블에 대해 sp_recompile를 실행할 경우
  • 프로시저나 프로시저가 참조하는 데이터베이스가 복원될 경우
  • 서버 작업이 많아서 오래된 계획이 캐시에서 제거될 경우

 

프로시저를 실행 할 때 소유자를 한정하게 되면 현재 사용자가 기존 실행계획을 구분하고 다시 사용하기가 쉬워진다. 예를 들면 dbo가 소유한 프로시저를 dbo가 아닌 사용자가 실행 할 때에는 소유자로 한정된 저장 프로시저를 사용 한다.

exec dbo.sPName -> exec sPName

 

 

 

[참고자료]

 

 

2013-07-24 / 강성욱 / http://sqlmvp.kr

 

 

  1. 김정열 2016.08.26 16:23

    항상 좋은 자료 보고 있습니다.~~ 감사합니다.

    보다가 보니 오류가 있는 부분이 있는듯 합니다.

    중간부분에 '이과정을 지연된 컴파일이라 부른다' 라고 되어 있는데

    '지연된 컴파일(Deferred compile)' 가 아니라 '스키마변경(Schema changed)

    으로 인해 발생한 재컴파일로 보입니다.

    원문에도 지연된 컴파일이라고 되어있으나 아무리 봐도 아닌것 같네요

+ Recent posts