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

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

 

• 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

 

 

 

[참고자료]

• http://blogs.msdn.com/b/psssql/archive/2011/07/15/stored-procedure-recompile-caused-by-alter-table-on-temp-table.aspx
• http://support.microsoft.com/kb/243586

 

 

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