[MSSQL] 저장 프로시저(Stored Procedure)와 파라미터 스니핑

1. 저장 프로시저(Stored Procedure)란?

복잡한 SQL 쿼리문들을 하나의 함수처럼 묶어서 데이터베이스에 미리 컴파일하여 저장해 둔 객체를 말한다. 필요할 때마다 긴 쿼리를 다시 짜는 게 아니라, 이름만 불러서 실행(EXEC 프로시저명)할 수 있다.

 

💡 함수(FUNCTION)와 다른 점

• FUNCTION(함수): 반드시 하나의 값을 반환해야 하며, SELECT나 WHERE 절 내부에서 호출된다. 오직 데이터를 읽고 계산하는 용도로만 쓰이며, 내부에서 INSERT/UPDATE 같은 데이터 변경이 엄격히 금지된다.
• PROCEDURE(프로시저): 독립적으로 EXEC 명령어로 실행되며, 반환값이 없어도 된다. 내부에서 데이터를 자유롭게 수정/삭제할 수 있고 복잡한 트랜잭션 제어가 가능하다.

---

2. 현대 웹 개발에서는 많이 보이지 않는 이유

1. SQL Mapper과 ORM의 사용: 과거에는 자바 코드에 긴 SQL을 안 섞으려고 프로시저를 주로 사용했다. SQL Mapper는 SQL과 동적 쿼리 로직을 깔끔하게 분리할 수 있기에 굳이 DB에서 로직을 처리할 이유가 사라졌다.
2. 비즈니스 로직은 서버에서 처리: 현대 아키텍처 철학에서는 DB는 저장/조회에 충실하고, 비즈니스 로직에 대한 처리는 서버에서 하도록 하여 역할을 확실히 분리하고자 한다.
3. 형상 관리와 배포의 용이성: SQL Mapper에서 작성된 소스는 형상 관리 Tool을 통해 커밋 이력이 완벽히 관리되지만, DB 프로시저는 버전 관리가 까다로워 유지보수가 쉽지 않은 단점이 있다.

💡 프로시저는 그럼 사용되지 않는가?

대형 금융권, 대기업 물류처럼 수십 년간 축적된 복잡한 로직이 DB에 묶여있는 곳이나, 수천만 건의 데이터를 네트워크 이동 없이 한 번에 처리해야 하는 대량의 배치(Batch) 작업에서는 여전히 프로시저가 핵심 동력원인 경우가 있다.

---

3. 파라미터 스니핑 (Parameter Sniffing)

MSSQL은 프로시저가 최초로 실행될 때 들어온 파라미터의 값을 읽고, 그 값에 딱 맞춘 최적의 실행 계획을 짜서 메모리(캐시)에 저장한다. 이후 호출부터는 이 계획을 무조건 재사용하도록 되어 있다.

 

여기까지만 보면 실행할 때마다 새로운 계획을 짜지 않아도 돼서 빠르겠다 싶을 수 있다. 문제는 첫 번째 파라미터에는 최고였던 쿼리 실행 계획이 두 번째 파라미터에는 최악의 계획이 될 수 있다.

 

파라미터 스니핑 예시

주문 내역을 조회하는 프로시저가 있다고 가정하고, 여기에는 두 가지 유형의 고객사가 있다.

1. 신생 소형 고객사 (ID: 999): 이번 달 주문 데이터 단 5건
2. 초대형 대기업 고객사 (ID: 111): 이번 달 주문 데이터 30만 건

CREATE PROCEDURE GetOrderDashboard
    @CustomerID INT,
    @StartDate DATETIME
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, TotalAmount, OrderStatus
    FROM Orders
    WHERE CustomerID = @CustomerID AND OrderDate >= @StartDate;
END;

• 새벽 4:00: 배치 프로그램이 소형 고객사(ID=999)의 데이터를 조회한다. MSSQL은 적은 건수의 데이터 분포를 확인하고, Index Seek 방식으로 계획을 짠 후 메모리(캐시)에 실행계획을 올린다. 데이터 조회는 빠르게 진행된다.
• 오전 9:05: 출근한 대기업 담당자가 로그인하여 대기업 고객사(ID=111)의 대시보드를 켜고, 주문 내역을 조회한다. 30만 건을 가져와야 하는데, MSSQL은 새벽에 만들어둔 Index Seek 계획을 무지성으로 재사용한다.
• 결과: 30만 건을 찾으려고 인덱스 루프를 돌며 디스크 I/O를 무자비하게 낭비하여 성능이 떨어진다. 결국 30초 타임아웃과 함께 DB CPU 점유율이 100%를 찍으며 웹 서버 전체가 먹통이 될 수 있다.

*Index Seek: 색인을 보고 원하는 데이터를 찾아가는 방법, 소량의 데이터 조회에 최적
*Table Scan: 데이터 밀도가 높아서 테이블의 모든 내용을 조회하여 찾는 방법, 대량의 데이터 조회에 최적
*Index Scan: 테이블 데이터는 보지 않고, 색인 자체를 처음부터 끝까지 훑는 방식

---

4. 파라미터 스니핑 예방법

(1) OPTIMIZE FOR UNKNOWN

MSSQL이 최초 파라미터 값에 치중된 계획을 짜도록 하지 않고 통계 정보를 바탕으로 가장 평균적이고 무난한 실행 계획을 세우도록 강제하는 힌트를 주는 방법이다.

ALTER PROCEDURE GetOrderDashboard
    @CustomerID INT,
    @StartDate DATETIME
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, TotalAmount, OrderStatus
    FROM Orders
    WHERE CustomerID = @CustomerID AND OrderDate >= @StartDate
    OPTION (OPTIMIZE FOR UNKNOWN); -- 쿼리 맨 밑에 힌트를 추가한다
END;

 

(2) 로컬 변수(Local Variable) 우회

파라미터를 내부 변수에 한 번 복사해서 사용하는 방식이다. MSSQL은 컴파일 시점에 로컬 변수에 무슨 값이 담길지 예측할 수 없기 때문에, 자동으로 무난하고 안전한 평균치 계획을 수립합니다.

ALTER PROCEDURE GetOrderDashboard
    @CustomerID INT,
    @StartDate DATETIME
AS
BEGIN
    -- 로컬 변수에 대입 (컴파일 시점에 스니핑 차단)
    DECLARE @LocalID INT = @CustomerID;
    DECLARE @LocalKey DATETIME = @StartDate;

    SELECT OrderID, CustomerID, OrderDate, TotalAmount, OrderStatus
    FROM Orders
    WHERE CustomerID = @LocalID AND OrderDate >= @LocalKey;
END;

 

(3) WITH RECOMPILE 옵션 사용

매번 실행 계획을 새로 짤 수 있도록 강제하는 WITH RECOMPILE 옵션을 주어 해결할 수도 있다. 하지만 호출할 때마다 컴파일이 되기 때문에 프로시저를 사용하는 장점을 얻을 수 없고, 동시 접속자가 몰릴 때 DB CPU를 폭발시키는 주범이 될 수 있어 많이 사용되지 않는다.

CREATE PROCEDURE GetOrderDashboard
    @CustomerID INT,
    @StartDate DATETIME
WITH RECOMPILE -- 옵션 선언
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, TotalAmount, OrderStatus
    FROM Orders
    WHERE CustomerID = @CustomerID AND OrderDate >= @StartDate;
END;

---

5. 파라미터 스니핑 발생 시 처치 방법

(1) 범인 프로시저 색출

어떤 프로시저가 파라미터 스니핑에 걸려 DB CPU를 잡아먹고 있는지 찾아야 한다. SSMS(SQL Server Management Studio)에서 아래 스크립트를 실행하여 현재 가장 느린 프로시저를 색출한다.

-- 현재 캐시에 있는 프로시저 중 평균 CPU 사용량이 가장 높은 상위 5개 조회
SELECT TOP 5
    Result.text AS [ProcedureName],
    Stats.total_worker_time / Stats.execution_count AS [Avg_CPU_Time],
    Stats.execution_count AS [Execution_Count],
    Stats.total_elapsed_time / Stats.execution_count AS [Avg_Total_Time]
FROM sys.dm_exec_procedure_stats AS Stats
CROSS APPLY sys.dm_exec_sql_text(Stats.sql_handle) AS Result
ORDER BY [Avg_CPU_Time] DESC;

 

(2) 범인 프로시저의 실행 계획 제거

당장 전체 DB 서버를 껐다 켤 수는 없으므로 메모리에 올라가 있는 해당 프로시저의 잘못된 실행 계획만 강제로 지운다.

-- 특정 프로시저의 ID를 찾아 캐시에서 제거
DECLARE @PlanHandle VARBINARY(64);

SELECT @PlanHandle = plan_handle 
FROM sys.dm_exec_procedure_stats 
WHERE object_id = OBJECT_ID('GetOrderDashboard');

IF @PlanHandle IS NOT NULL
    FREEPROCCACHE (@PlanHandle); -- 해당 계획만 제거

 

이렇게 병목 되어 있는 프로시저의 실행 계획을 제거하여 다음 해당 프로시저의 요청이 오면 새로운 계획을 짜서 메모리에 저장한다. 하지만 다른 요청이 이어서 들어오면 결국 같은 상황이 발생되기 때문에 반드시 해당 프로시저에 OPTIMIZE FOR UNKNOWN을 넣어 예방할 수 있도록 조치를 해야 한다.