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

 

  • 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 SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 41446
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 22475
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1435
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1324
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1738
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1864
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4797
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1224
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1356
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2456
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2281
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4448
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1540
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1222
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1118
» 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2549
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1164
1799 Hot Add CPU jevida(강성욱) 2016.09.29 916
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1108
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1836
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1520
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1290





XE Login