프로시저와 임시테이블, 그리고 리컴파일

 

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

 

대부분의 자료에서 보면 보안, 성능 측면에서 stored procedure 사용을 권장한다. 보안 적인 측면에서는 ad-hoc 쿼리의 실행 권한을 방지 함으로써 허가 받지 않는 데이터 변경을 방지 하고 성능 적인 측면에서는 실행 계획 재사용 및 컴파일 비용의 감소를 통해 성능상 이점을 지향 한다.

그렇다면 성능 적인 측면에서 프로시저를 사용하면 무조건 compile(recompile)이 발생하지 않을까? 다음 시나리오를 통해 알아 보자.

 

우리는 프로시저의 리컴파일에 대해서 알아 보기 위해 프로시저 내에서 임시 테이블을 생성하는 쿼리를 작성 하였다. 임시 테이블이라는 것은 TempDB에 생성되는 것으로 영속적인 데이터베이스의 개체가 아니라 세션 동안만 유지 되는 개체이다.

 

결론부터 정의하면 프로시저 내에서 임시 테이블을 참조하도록 코드가 생성되어 있으면 프로시저의 실행 계획은 영속적이지 않고 프로시저 실행 시 마다 컴파일이 이루어 진다. 다만 동일한 프로시저에서 생성한 임시 테이블에 접근하는 경우는 프로시저가 리컴파일 되지 않는다.

 

[동일한 SP에서 임시 테이블 사용]

동일한 SP에서 임시 테이블을 사용하였을 때에는 리컴파일이 발생하지 않는다.

create procedure Test_1

 

as

 

create table #temp (name nvarchar(50))

 

insert #temp values ('KANGSUNGWOOK')

 

select * from #temp

 

 

 

 

[SP에서 임시테이블을 사용하는 다른 SP 호출]

SP Test_2를 호출하면 Test_2에서는 임시테이블을 생성하고 그 안에서 다른 프로시저 Test_3을 호출 한다. Test_3은 Test_2에서 생성한 임시테이블을 조회한다. 그리고 반복적인 작업을 위해 다시 Test_2를 호출하도록 설계 되었다.

Test_2에서 Test_3을 호출 하는 것은 동일한 SP에서 실행되는 것처럼 보이지만 Test_2에서 생성한 개체 ID 값은 계속 변하므로 호출 되는 Test_3프로시저는 계 해서 리컴파일 된다.

create procedure Test_2

 

as

 

create table #temp (name nvarchar(50))

 

insert #temp values ('KANGSUNGWOOK')

 

exec dbo.test_3

create procedure Test_3

 

as

 

select * from #temp

 

exec dbo.test_2

exec Test_2

 

 

 

[배치세션과 임시테이블 호출]

배치 작업 시 임시테이블이 세션 별로 유지 되기 때문에 해당 테이블을 저장 프로시저의 경우 해당 개체ID 가 유지되어 리컴파일이 발생하지 않는다.

아래의 시나리오에서는 동일한 프로시저 내에서 임시테이블을 참조하는 프로시저를 여러번 호출하게 하였다. 처음 한 번은 리컴파일이 발생하고 나머지 실행에서는 발생하지 않았다.

create procedure Test_4

 

as

 

begin

    create table #temp (name nvarchar(50))

 

    insert #temp values ('KANGSUNGWOOK')

 

    exec dbo.test_5

    exec dbo.test_5

    exec dbo.test_5

    exec dbo.test_5

    exec dbo.test_5

    exec dbo.test_5

    

end

create procedure Test_5

 

as

 

select * from #temp

 

 

 

프로시저 사용 시 프로시저 안에서 사용하는 임시테이블의 성격을 잘 고려하여 리컴파일의 비용을 줄 일 수 있도록 생각해 보자.

 

 

[참고자료]

http://technet.microsoft.com/ko-kr/library/cc700710.aspx

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 1247
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2258
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4122
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1512
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1166
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1096
» 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2513
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1093
1799 Hot Add CPU jevida(강성욱) 2016.09.29 887
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1059
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1778
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1481
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1260
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1586
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1463
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 954
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1089
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1288
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1859
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2602
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1389





XE Login