Stored Procedure

들어가며

회사에서 .NET과 MSSQL로 개발하다 보면 Stored Procedure(이하 SP)를 매일 마주한다. 명목상 백엔드라고는 하지만 데이터베이스작성 및 관리부터 프로젝트 배포 및 까지 사실상 전 과정을 수행한다.

SELECT 한 줄인 단순한 SP부터, 트랜잭션과 에러 핸들링이 얽힌 복잡한 비즈니스 로직까지. 그런데 막상 SP가 정확히 무엇인지 설명하라라고 물어보면 명쾌하게 설명하기 어렵다.

이 글에서는 SP의 기원부터 내부 동작 원리, 패턴, 그리고 .NET 연동까지 간단히 정리하려한다. 한 번에 모두 이해하긴 어려우니 두고두고 읽으려 한다.

SP란 무엇인가

Stored Procedure는 데이터베이스에 저장된 SQL 문장들의 집합 이다. 하나의 함수처럼 이름을 붙여 저장해두고, 필요할 때 호출해서 실행한다.

-- 가장 단순한 SP
CREATE PROCEDURE usp_GetAllUsers
AS
BEGIN
    SELECT UserId, UserName, Email
    FROM Users
    WHERE IsActive = 1
END

일반적인 SQL 쿼리와 뭐가 다를까? 핵심 차이는 사전 컴파일 이다.

일반 쿼리는 실행할 때마다 파싱 → 최적화 → 컴파일 → 실행 단계를 거친다.반면 SP는 최초 실행 시 한 번만 이 과정을 거치고, 이후에는 캐시된 실행 계획을 재사용한다.

Sybase에서 SQL Server까지

SP의 기원을 알면 왜 이렇게 설계되었는지 이해할 수 있다.

Sybase SQL Server (1980년대)

SP는 Sybase 에서 처음 등장했다. 1980년대 Sybase가 T-SQL(Transact-SQL)이라는 SQL 확장 언어를 만들면서 SP 개념을 도입했다. 당시 네트워크 대역폭이 좁았기 때문에, 여러 SQL 문을 서버 쪽에 저장해두고 한 번의 호출로 실행하는 방식이 성능상 큰 이점이 있었다.

Microsoft SQL Server의 탄생

1989년, Microsoft는 Sybase, Ashton-Tate와 함께 Sybase SQL Server를 OS/2로 포팅하는 프로젝트를 시작한다. 이것이 Microsoft SQL Server의 시작이다. 1995년 SQL Server 6.0에서 Sybase와의 협업이 종료되고, 이후 Microsoft는 독자적으로 SQL Server를 발전시킨다. 2005년 SQL Server 2005에서 기존 Sybase 코드를 완전히 Microsoft 코드로 전환 완료한다.

T-SQL과 SP는 이 Sybase 시절부터 내려온 유산이다. 지금 MSSQL에서 쓰는 SP 문법이 Oracle의 PL/SQL이나 PostgreSQL의 PL/pgSQL과 다른 이유가 여기에 있다.

T-SQL은 Sybase에서 시작된 SQL 확장 언어다. Microsoft SQL Server와 Sybase ASE가 같은 뿌리를 공유하는 이유이기도 하다.

SP의 내부 동작 원리

SP가 실행되면 내부에서 어떤 일이 벌어지는지 이해해야 성능 문제를 진단할 수 있다.

최초 실행 : 컴파일과 실행 계획 생성

SP를 처음 호출하면 SQL Server는 다음 단계를 거친다.

1. 파싱(Parsing)
   └─ 문법 검사, 구문 트리 생성

2. 바인딩(Binding)
   └─ 테이블, 컬럼 등 객체 존재 여부 확인

3. 최적화(Optimization)
   └─ 통계 정보를 기반으로 최적의 실행 계획 결정
   └─ 인덱스 스캔? 시크? 해시 조인? 네스티드 루프?

4. 컴파일(Compilation)
   └─ 실행 계획을 실행 가능한 형태로 컴파일

5. 캐싱(Caching)
   └─ Plan Cache에 실행 계획 저장

6. 실행(Execution)
   └─ 실행 계획에 따라 데이터 처리

이후 실행 : Plan Cache 재사용

두 번째 호출부터는 1~4단계를 건너뛴다. SQL Server가 Plan Cache에서 기존 실행 계획을 찾아 바로 실행한다. 이것이 SP의 핵심 성능 이점이다.

-- Plan Cache에서 특정 SP의 실행 계획 확인
SELECT 
    ps.object_id,
    OBJECT_NAME(ps.object_id) AS ProcedureName,
    ps.execution_count,
    ps.total_elapsed_time / 1000 AS TotalElapsedMs,
    ps.cached_time,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats ps
WHERE OBJECT_NAME(ps.object_id) = 'usp_GetAllUsers'

SQL Server 7.0 이전에는 SP만 Plan Cache에 저장되었다. 7.0부터는 Ad-hoc 쿼리도 캐시되지만, SP는 여전히 재사용률이 높다.

SP 기본 문법 정리

CREATE, ALTER, DROP

-- 생성
CREATE PROCEDURE dbo.usp_GetUserById
    @UserId INT
AS
BEGIN
    SELECT UserId, UserName, Email
    FROM Users
    WHERE UserId = @UserId
END
GO

-- 수정
ALTER PROCEDURE dbo.usp_GetUserById
    @UserId INT
AS
BEGIN
    SELECT UserId, UserName, Email, CreatedDate
    FROM Users
    WHERE UserId = @UserId
END
GO

-- 삭제
DROP PROCEDURE IF EXISTS dbo.usp_GetUserById
GO

ALTER를 사용하면 SP에 부여된 권한이 유지된다. DROP 후 CREATE하면 권한을 다시 부여해야 한다.

입력 매개변수와 출력 매개변수

CREATE PROCEDURE dbo.usp_CreateUser
    @UserName    NVARCHAR(50),
    @Email       NVARCHAR(100),
    @NewUserId   INT OUTPUT,         -- 출력 매개변수
    @ResultCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY
        INSERT INTO Users (UserName, Email, CreatedDate)
        VALUES (@UserName, @Email, GETDATE())

        SET @NewUserId = SCOPE_IDENTITY()
        SET @ResultCode = 0    -- 성공
    END TRY
    BEGIN CATCH
        SET @NewUserId = 0
        SET @ResultCode = -1   -- 실패
    END CATCH
END
GO

-- 호출
DECLARE @NewId INT, @Result INT

EXEC dbo.usp_CreateUser
    @UserName = N'홍길동',
    @Email = N'hong@example.com',
    @NewUserId = @NewId OUTPUT,
    @ResultCode = @Result OUTPUT

SELECT @NewId AS NewUserId, @Result AS ResultCode

기본값이 있는 매개변수

CREATE PROCEDURE dbo.usp_SearchUsers
    @UserName   NVARCHAR(50) = NULL,    -- 기본값 NULL
    @PageSize   INT = 20,               -- 기본값 20
    @PageNumber INT = 1
AS
BEGIN
    SET NOCOUNT ON

    SELECT UserId, UserName, Email
    FROM Users
    WHERE (@UserName IS NULL OR UserName LIKE '%' + @UserName + '%')
    ORDER BY UserId
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
END
GO

실행 계획과 Parameter Sniffing

SP의 가장 유명한 함정이 Parameter Sniffing 이다.

Parameter Sniffing이란?

SP를 최초 실행할 때 전달된 매개변수 값을 기반으로 실행 계획이 만들어진다. 이후 다른 값으로 호출해도 같은 실행 계획 을 재사용한다. 데이터 분포가 고르면 문제없지만, 편향된 데이터에서는 성능이 크게 달라질 수 있다.

-- 주문 조회 SP
CREATE PROCEDURE dbo.usp_GetOrdersByStatus
    @Status INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE Status = @Status
END
GO

예를 들어 Status = 1 (처리중)인 주문이 100건, Status = 2 (완료)인 주문이 500만 건이라고 하자.

최초 호출: EXEC usp_GetOrdersByStatus @Status = 1
→ 100건이니까 Index Seek 실행 계획 생성 (빠름)

두 번째 호출: EXEC usp_GetOrdersByStatus @Status = 2
→ 500만 건인데 Index Seek 계획을 재사용 (느림!)
→ Table Scan이 더 효율적이지만, 캐시된 계획을 쓰니까 비효율

반대 순서로 호출하면 또 다른 문제가 생긴다.

최초 호출: EXEC usp_GetOrdersByStatus @Status = 2
→ 500만 건이니까 Table Scan 실행 계획 생성

두 번째 호출: EXEC usp_GetOrdersByStatus @Status = 1
→ 100건인데 Table Scan 계획을 재사용 (불필요하게 느림)

Parameter Sniffing 자체는 나쁜 것이 아니다. SQL Server의 정상적인 최적화 메커니즘이다. 문제가 되는 것은 데이터 분포가 극단적으로 편향된 경우뿐이다.

Parameter Sniffing 해결 방법

1. OPTION (RECOMPILE)

매 실행마다 새로운 실행 계획을 만든다. 가장 단순하지만, 자주 호출되는 SP에서는 컴파일 비용이 부담된다.

ALTER PROCEDURE dbo.usp_GetOrdersByStatus
    @Status INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (RECOMPILE)   -- 매번 최적의 계획 생성
END
GO

2. OPTIMIZE FOR

특정 값에 최적화된 실행 계획을 고정한다. 대부분의 호출이 특정 패턴을 따를 때 유용하다.

ALTER PROCEDURE dbo.usp_GetOrdersByStatus
    @Status INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR (@Status = 1))  -- Status=1 기준으로 계획 생성
END
GO

3.: OPTIMIZE FOR UNKNOWN

통계 정보의 평균 분포를 기반으로 실행 계획을 만든다. 최적은 아니지만 안정적이다.

ALTER PROCEDURE dbo.usp_GetOrdersByStatus
    @Status INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR UNKNOWN)
END
GO

4. 지역 변수 사용

매개변수 값을 지역 변수에 복사하면 SQL Server가 값을 “모르는” 상태가 되어 평균 통계 기반 계획을 생성한다. OPTIMIZE FOR UNKNOWN과 사실상 동일한 효과다.

ALTER PROCEDURE dbo.usp_GetOrdersByStatus
    @Status INT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @LocalStatus INT = @Status    -- 지역 변수에 복사

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE Status = @LocalStatus
END
GO

정리

방법	장점	단점	적합한 상황
RECOMPILE	항상 최적 계획	컴파일 비용 발생	호출 빈도 낮은 SP
OPTIMIZE FOR 값	특정 패턴에 최적	데이터 분포 변경 시 재검토 필요	호출 패턴이 예측 가능할 때
OPTIMIZE FOR UNKNOWN	안정적	최적은 아닐 수 있음	범용적으로 무난
지역 변수	코드 변경 간단	UNKNOWN과 동일한 한계	빠른 대응이 필요할 때

트랜잭션과 에러 핸들링

실무 SP에서 가장 중요한 부분이다. 데이터 정합성을 보장하려면 트랜잭션과 에러 핸들링을 반드시 함께 사용해야 한다.

기본 패턴 : TRY-CATCH + 명시적 트랜잭션

CREATE PROCEDURE dbo.usp_TransferPoints
    @FromUserId  INT,
    @ToUserId    INT,
    @Points      INT,
    @ResultCode  INT OUTPUT,
    @ResultMsg   NVARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON    -- 에러 발생 시 자동 롤백

    SET @ResultCode = 0
    SET @ResultMsg = N''

    -- 유효성 검사 (트랜잭션 밖에서)
    IF @Points <= 0
    BEGIN
        SET @ResultCode = -1
        SET @ResultMsg = N'포인트는 0보다 커야 합니다'
        RETURN
    END

    BEGIN TRY
        BEGIN TRAN

        -- 차감
        UPDATE Users
        SET Points = Points - @Points
        WHERE UserId = @FromUserId
          AND Points >= @Points    -- 잔액 확인

        IF @@ROWCOUNT = 0
        BEGIN
            SET @ResultCode = -2
            SET @ResultMsg = N'잔액이 부족합니다'
            ROLLBACK TRAN
            RETURN
        END

        -- 적립
        UPDATE Users
        SET Points = Points + @Points
        WHERE UserId = @ToUserId

        IF @@ROWCOUNT = 0
        BEGIN
            SET @ResultCode = -3
            SET @ResultMsg = N'받는 사용자가 존재하지 않습니다'
            ROLLBACK TRAN
            RETURN
        END

        COMMIT TRAN
        SET @ResultMsg = N'전송 완료'

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN

        SET @ResultCode = -99
        SET @ResultMsg = ERROR_MESSAGE()
    END CATCH
END
GO

SET 옵션 정리

SP 상단에 자주 등장하는 SET 옵션들의 의미를 정리한다.

SET NOCOUNT ON
-- INSERT, UPDATE, DELETE 후 "(N개 행이 영향을 받음)" 메시지를 억제한다
- 기능: 쿼리 실행 후 "n개 행이 영향을 받음"이라는 메시지가 클라이언트로 전송되는 것을 차단
- 사용 이유: 
  * 네트워크 부하 감소: 수천 번 반복되는 루프나 복잡한 로직에서 매번 "1 row affected" 메시지를 보내는 건 자원 낭비
  * 애플리케이션 오류 방지: 일부 오래된 ADO.NET이나 라이브러리는 이 메시지를 실제 데이터 결과셋으로 오해하여 에러를 일으키기도 함


SET XACT_ABORT ON
"에러 나면 뒤도 돌아보지 말고 전부 취소해."
- 기능: 런타임 에러가 발생하면 즉시 현재 트랜잭션을 롤백(Rollback)하고 실행을 중단합니다.
- 사용 이유:
  * 데이터 무결성: 여러 테이블을 건드리는 작업 중 중간에 에러가 났는데, 앞부분만 저장되고 뒷부분은 안 되는 '반쪽짜리 저장'을 완벽히 방지.
  * 분산 트랜잭션 필수: 연결된 서버(Linked Server)를 사용할 때는 이 옵션이 없으면 에러가 발생할 수 있다.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
"데이터가 정확하지 않아도 좋으니, 일단 멈추지 말고 읽어라." (NOLOCK)
- 기능: 다른 트랜잭션이 수정 중이지만 아직 커밋(Commit)되지 않은 데이터도 읽을 수 있게 함 (Dirty Read 허용)
- 사용 이유:
  * 차단(Blocking) 방지: 조회(Select) 쿼리가 수정(Update/Insert) 작업과 충돌해서 대기하는 상황을 막음. 시스템 전체의 처리량(Throughput)이 비약적으로 상승
- 주의사항: 금융권 잔액 조회처럼 1원의 오차도 허용 안 되는 곳에는 위험할 수 있다.
            하지만 대규모 조회 시스템에서는 서비스 가용성을 위해 필수.


SET LOCK_TIMEOUT 3000
"기다림에도 한계를 둡니다 (3초만 기다림)."
- 기능: 락(Lock)이 걸린 자원을 기다리는 시간을 밀리초(ms) 단위로 설정. 3000은 3초를 의미.
- 사용 이유:
  * 무한 대기 방지: 기본값은 -1(무한 대기). 특정 쿼리가 자원을 꽉 잡고 있으면 뒤에 오는 모든 사용자가 무한정 모래시계만 보게 됨.
  * 시스템 가용성: 3초 뒤에 에러를 뱉고 종료하게 함으로써, 시스템 전체가 마비되는 '연쇄 장애'를 끊어주는 안전장치 역할.

SET XACT_ABORT ON 없이 TRY-CATCH만 쓰면, 일부 에러(예: 교착 상태)에서 CATCH 블록이 실행되지 않을 수 있다. 반드시 함께 사용하자.

User Defined Function (UDF)과의 차이

SP와 혼동하기 쉬운 것이 UDF(사용자 정의 함수)다. 둘의 차이를 명확히 정리한다.

-- 스칼라 함수 예시 : 현재 시간을 UTC 보정하여 반환
CREATE FUNCTION dbo.ufn_GetCustomDateTime()
    RETURNS DATETIME
AS
BEGIN
    DECLARE @utcDiff INT = 9    -- KST = UTC + 9
    RETURN DATEADD(HOUR, @utcDiff, GETUTCDATE())
END
GO

-- 사용 예시
SELECT dbo.ufn_GetCustomDateTime() AS KoreanTime
-- DEFAULT 값으로도 사용 가능
-- CreatedDate DATETIME DEFAULT(dbo.ufn_GetCustomDateTime())

정리

| 구분 | Stored Procedure | User Defined Function | |——|—————–|———————-| | 반환 | 결과 집합, OUTPUT 매개변수, RETURN 코드 | 스칼라 값 또는 테이블 | | 트랜잭션 | 사용 가능 | 사용 불가 | | DML(INSERT/UPDATE/DELETE) | 사용 가능 | 사용 불가 (테이블 변수 제외) | | SELECT 문 내 호출 | 불가 | 가능 | | 에러 핸들링 | TRY-CATCH 가능 | TRY-CATCH 불가 |

스칼라 UDF는 행마다 호출되므로 대용량 쿼리에서 성능 병목이 될 수 있다. SQL Server 2019+에서는 스칼라 UDF 인라인 최적화가 도입되어 개선되었지만, 여전히 주의가 필요하다.

DB 예외 로그 테이블 패턴

실무에서 SP 내부의 에러를 추적하려면 예외 로그 테이블이 필수다.

로그 테이블 생성

CREATE TABLE dbo.DBExceptionLog
(
    LogId        BIGINT IDENTITY(1, 1) NOT NULL,
    ObjectName   NVARCHAR(100)  NOT NULL,       -- 에러 발생한 SP/Function 이름
    ErrorMessage NVARCHAR(4000) NOT NULL,       -- 에러 메시지
    ErrorNumber  INT            NULL,           -- ERROR_NUMBER()
    ErrorLine    INT            NULL,           -- ERROR_LINE()
    CreatedDate  DATETIME       DEFAULT(GETDATE()) NOT NULL,

    CONSTRAINT PK_DBExceptionLog PRIMARY KEY CLUSTERED (LogId ASC)
)
GO

로그 입력 SP

CREATE PROCEDURE dbo.usp_InsertDBExceptionLog
    @ObjectName   NVARCHAR(100),
    @ErrorMessage NVARCHAR(4000),
    @ErrorNumber  INT = NULL,
    @ErrorLine    INT = NULL
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.DBExceptionLog (ObjectName, ErrorMessage, ErrorNumber, ErrorLine)
    VALUES (@ObjectName, ISNULL(@ErrorMessage, N''), @ErrorNumber, @ErrorLine)
END
GO

활용 : SP에서 에러 발생 시 로그 기록

CREATE PROCEDURE dbo.usp_SomeBusiness
    @UserId     INT,
    @ResultCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

    SET @ResultCode = 0

    BEGIN TRY
        BEGIN TRAN

        -- 비즈니스 로직
        UPDATE Users SET LastLoginDate = GETDATE() WHERE UserId = @UserId

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN

        SET @ResultCode = -99

        -- 예외 로그 기록
        EXEC dbo.usp_InsertDBExceptionLog
            @ObjectName   = N'usp_SomeBusiness',
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber  = ERROR_NUMBER(),
            @ErrorLine    = ERROR_LINE()
    END CATCH
END
GO

CATCH 블록에서 로그 SP를 호출할 때, 로그 SP 자체에서 에러가 나면 원래 에러 정보가 사라질 수 있다. 로그 SP는 최대한 단순하게 유지하자.

.NET 에서 SP 호출하기

.NET에서 SP를 호출하는 세 가지 주요 방법을 비교한다.

방법 1 : ADO.NET (직접 제어)

public async Task<User?> GetUserByIdAsync(int userId)
{
    await using var connection = new SqlConnection(_connectionString);
    await using var command = new SqlCommand("dbo.usp_GetUserById", connection)
    {
        CommandType = CommandType.StoredProcedure
    };

    // 매개변수 타입과 크기를 명시적으로 지정
    command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;

    await connection.OpenAsync();
    await using var reader = await command.ExecuteReaderAsync();

    if (await reader.ReadAsync())
    {
        return new User
        {
            UserId = reader.GetInt32(reader.GetOrdinal("UserId")),
            UserName = reader.GetString(reader.GetOrdinal("UserName")),
            Email = reader.GetString(reader.GetOrdinal("Email"))
        };
    }

    return null;
}

방법 2 : Dapper (간결함과 성능)

public async Task<User?> GetUserByIdAsync(int userId)
{
    await using var connection = new SqlConnection(_connectionString);

    var user = await connection.QuerySingleOrDefaultAsync<User>(
        "dbo.usp_GetUserById",
        new { UserId = userId },
        commandType: CommandType.StoredProcedure
    );

    return user;
}

방법 3 : Dapper + OUTPUT 매개변수

필자가 사용하는 방식이다.

public async Task<(int NewUserId, int ResultCode)> CreateUserAsync(
    string userName, string email)
{
    await using var connection = new SqlConnection(_connectionString);

    var parameters = new DynamicParameters();
    parameters.Add("@UserName", userName, DbType.String, size: 50);
    parameters.Add("@Email", email, DbType.String, size: 100);
    parameters.Add("@NewUserId", dbType: DbType.Int32,
                   direction: ParameterDirection.Output);
    parameters.Add("@ResultCode", dbType: DbType.Int32,
                   direction: ParameterDirection.Output);

    await connection.ExecuteAsync(
        "dbo.usp_CreateUser",
        parameters,
        commandType: CommandType.StoredProcedure
    );

    return (
        parameters.Get<int>("@NewUserId"),
        parameters.Get<int>("@ResultCode")
    );
}

방법	코드량	성능	유연성	적합한 상황
ADO.NET	많음	최고	완전 제어	OUTPUT/RETURN 복잡한 SP
Dapper	적음	매우 좋음	좋음	대부분의 CRUD SP
EF Core	적음	보통	제한적	ORM 위주 프로젝트에서 부분 사용

Dapper로 SP를 호출할 때는 반드시 commandType: CommandType.StoredProcedure 를 지정하자. 생략하면 내부적으로 EXEC 문으로 감싸서 실행되는데, 이 경우 OUTPUT 매개변수가 정상 동작하지 않고, SQL Injection에 더 취약해진다.

SP 네이밍 컨벤션

팀마다 다르지만, MSSQL 커뮤니티에서 널리 쓰이는 네이밍 규칙을 정리한다.

usp_GetUserById          -- usp_ : User Stored Procedure
usp_InsertOrder          -- 동사 + 명사 조합
usp_UpdateUserProfile    -- CRUD 동작을 명확히
usp_DeleteExpiredSessions

ufn_GetCustomDateTime    -- ufn_ : User Function
ufn_CalculateTax

sp_ 접두사는 절대 사용하지 마라. SQL Server는 sp_ 로 시작하는 프로시저를 시스템 프로시저로 인식하고, master DB에서 먼저 검색한다. 불필요한 Plan Cache Miss가 발생하고, COMPILE 락이 걸려 성능이 떨어진다.

SP vs ORM : 어떤 것을 선택할까

이 논쟁은 끝나지 않는다. 하지만 각각의 강점이 분명히 다르다.

SP가 유리한 경우

• 복잡한 비즈니스 로직 : 여러 테이블을 조작하고 조건 분기가 많은 경우
• 성능이 최우선 : Plan Cache 재사용, 네트워크 라운드트립 최소화
• 보안이 중요 : 테이블 직접 접근 권한 없이 SP 실행 권한만 부여 가능
• DBA와 협업 : DBA가 쿼리 튜닝을 직접 할 수 있음
• 배포 없이 수정 : 애플리케이션 재배포 없이 SP만 ALTER하면 됨

ORM이 유리한 경우

• 단순 CRUD : 기본적인 조회/수정은 ORM이 더 빠르게 개발 가능
• 마이그레이션 : DB 변경 이력 관리, 스키마 버전 관리
• 코드 중심 : 비즈니스 로직을 애플리케이션 레이어에 집중
• 테스트 : 단위 테스트가 상대적으로 쉬움

단순 CRUD는 ORM, 복잡한 로직이나 성능 크리티컬한 부분은 SP로 처리한다. 필자는 Dapper를 사용하되 모든 sql문을 SP로 작성하고있다.

성능 모니터링 쿼리 모음

SP를 운영하면서 자주 사용하는 진단 쿼리들이다.

가장 느린 SP TOP 10

SELECT TOP 10
    OBJECT_NAME(object_id) AS ProcedureName,
    execution_count,
    total_elapsed_time / 1000 AS TotalElapsedMs,
    total_elapsed_time / execution_count / 1000 AS AvgElapsedMs,
    total_logical_reads / execution_count AS AvgLogicalReads,
    cached_time,
    last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY total_elapsed_time / execution_count DESC

실행 계획 확인

SELECT
    qp.query_plan
FROM sys.dm_exec_procedure_stats AS ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE ps.database_id = DB_ID()
  AND OBJECT_NAME(ps.object_id) = 'usp_GetUserById'

특정 SP의 Plan Cache 삭제

-- 특정 SP만 재컴파일 요청
EXEC sp_recompile N'dbo.usp_GetUserById'

-- 전체 Plan Cache 삭제 (운영 환경 주의!)
-- DBCC FREEPROCCACHE

DBCC FREEPROCCACHE 는 모든 캐시된 실행 계획을 삭제한다. 운영 환경에서는 절대 함부로 실행하지 마라. 특정 SP만 재컴파일하려면 sp_recompile 을 사용하자.

마치며

SP의 핵심을 정리하면 다음과 같다.

SP는 데이터베이스에 저장된 컴파일된 SQL 집합 이다. Plan Cache에 실행 계획을 캐시하여 반복 호출 시 성능 이점을 얻는다. Sybase에서 시작된 T-SQL의 유산이며, MSSQL에서 가장 성숙한 형태로 발전했다.

Parameter Sniffing 은 SP의 가장 흔한 성능 함정이다. 데이터 분포가 편향된 경우 RECOMPILE, OPTIMIZE FOR, 지역 변수 등으로 대응할 수 있다. 만능 해결책은 없고, 상황에 맞는 방법을 선택해야 한다.

트랜잭션과 에러 핸들링 은 반드시 SET XACT_ABORT ON + TRY-CATCH 조합으로 사용하자. 에러 로그 테이블을 만들어두면 운영 중 문제 추적이 수월해진다.

.NET에서 SP 호출 은 ADO.NET으로 완전 제어하거나, Dapper로 간결하게 처리할 수 있다. 대부분의 프로젝트에서 Dapper가 적절한 균형점이다.

내용이 추가될 수 있으니 꾸준히 보고 다시 공부하고자 한다.

---

References

• Microsoft Docs - Stored Procedures
• Microsoft Docs - Plan Caching in SQL Server
• Microsoft Docs - Parameter Sniffing
• Brent Ozar - RECOMPILE Hints and Execution Plan Caching
• Brent Ozar - Parameter Sniffing
• SQLServerCentral - Stored Procedures and Caching
• SQLShack - Understanding SQL Server Query Plan Cache
• Dapper - Stored Procedures
• Microsoft Docs - ADO.NET Parameters
• Wikipedia - Microsoft SQL Server