[MSSQL] 저장 프로시저(Stored Procedure)의 문제점

저장 프로시저의 문제점

- 저장 프로시저는 최초 실행될 때 인덱스의 사용 여부가 결정된다. 

(즉, 만약 첫 번째 수행에서 인덱스를 사용하게 최적화되어 컴파일이 이루어졌다면 두 번째 수행할 때 많은 건수의 데이터를 가져오게되는 경우가 발생하여 인덱스를 사용하지 않는 것이 더 효율적이어도 첫번째 에 등룍된 실행 계획대로 인덱스를 사용하게 된다.)

- 저장 프로시저 생성 이후에 인덱스가 추가되거나 변경되어도 기존에 결정된 인덱스 사용여부를 변경할 필요가 있다.

해결 방안

- 저장 프로시저를 다시 컴파일

저장 프로시저를 다시 컴파일 하는 방법

(1) 'WITH RECOMPILE' 사용

- 저장 프로시저를 사용할 때의 'WITH RECOMPILE'

(예시)

EXEC usp_ID 5000 WITH RECOMPILE;

- 결과 : 만약 처음 저장 프로시저를 사용할 때 인덱스를 사용하여 탐색 하는 방법을 사용하였고, 이번 처리는 테이블 탐색 방식이 더 빠르다면 다시 컴파일 한다. 하지만, 새로 다시 컴파일된 결과를 메모리(캐시)에 저장하지는 않는다. 즉, 컴파일 없이 다시 저장 프로시저를 사용한다면 처음 저장 프로시저를 실행했을 때 최적화된 방식으로 실행된다.

- 저장 프로시저를 생성할 때의 'WITH RECOMPILE'

(예시)

CREATE PROC usp_ID
    @id INT
    WITH RECOMPILE
AS
    SELECT * FROM spTbl WHERE CustomerID < @id;
GO

- 결과 : 저장 프로시저를 실행할 때마다 다시 컴파일 하게 되어서 그때그때 필요한 최적화가 수행된다. 다만 이러한 경우 인덱스 사용 여부를 예측하지 못할 경우에 설정하는 것이 바람직하다.

(2)'sp_recompile 테이블이름' 사용

(예시)

-- 시스템 저장 프로시저 사용
EXEC sp_recompile spTbl;
 
-- 사용자 정의 저장 프로시저 
EXEC usp_ID 5000;

- 결과 : 해당 테이블과 관련된 저장 프로시저를 사용할 때 다시 컴파일 하며, 메모리(캐시)에 저장된다.

(3) 'DBCC FREEPROCCACHE' 사용

(예시)

-- 메모리(캐시) 비우기
DBCC FREEPROCCACHE;
 
-- 저장 
EXEC usp_ID 10;

- 결과 : 현재 메모리(프로시저 캐시 영역)의 내용을 모두 비우게 되므로 다음에 저장 프로시저를 실행할 때 다시 컴파일한다.

( ※ 'DBBC FREEPROCCACHE'는 전체 저장 프로시저와 관련된 실행 계획 캐시를 지운다. 때문에 다음에 실행되는 모든 저장 프로시저는 다시 컴파일 하게 되므로 일시적으로 시스템이 느려질 수 있다.)

최종 결론

저장 프로시저를 사용하는 이유는 일반 쿼리문에비해 처리 속도가 빠르다는 장점이 있어서이며, 처리 속도가 빠르다는 점은 최적화 및 컴파일 시간이 일반 쿼리문에 비해 생략되는 경우가 많아서 이다. 하지만 위와 같은 문제점(최적화)으로 매번 다시 최적화를 한다면, 일반 쿼리문와 같은것이 아닌가 라는 생각을 할 수 있다. 하지만 아래와 같은 예시로 저장 프로시저가 빈번한 일반 쿼리문을 사용하는 것보다 빠르다는 것을 보여주며, 이전 게시물(링크)에서 저장 프로시저의 특징을 다시 한 번 살펴봤으면 좋겠다.

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)