데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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

 

 

 

[참고자료]

 

 


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

No. Subject Author Date Views
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1581
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2222
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2512
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1717
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1543
» 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1872
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4579
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1273
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1191
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1331
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1638





XE Login