본문 바로가기
● Data Insights/SQL

SQL 저장 프로시저 최적화 가이드

by DATA Canvas 2025. 11. 19.
반응형

 

저장 프로시저(Stored Procedure)는 SQL Server에서 미리 컴파일된 SQL 문 그룹으로, 다음과 같은 이점을 제공합니다

  • 성능 향상: 쿼리 계획이 재사용되어 CPU 오버헤드 감소
  • 보안 강화: SQL 인젝션 공격 방지 (파라미터화된 쿼리 사용 시)
  • 코드 재사용성: 비즈니스 로직 캡슐화
  • 네트워크 트래픽 감소: 배치 처리 최적화

그러나 적절한 오류 처리와 성능 튜닝 없이는 데이터베이스 성능 저하, 데이터 무결성 문제, 사용자 경험 악화로 이어질 수 있습니다.

 

오류 처리 전략

1. TRY-CATCH 블록 기본 구조

TRY-CATCH는 T-SQL의 표준 예외 처리 메커니즘입니다.

BEGIN TRY
    -- 예외가 발생할 수 있는 문장들
    INSERT INTO Products (ProductID, Name, Price)
    VALUES (@ProductID, @Name, @Price);
END TRY

BEGIN CATCH
    -- 오류 처리 코드
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH
반응형

2. 오류 정보 수집 함수

함수 설명 반환값
ERROR_NUMBER() 오류 번호 정수 (예: 2627 중복 키 위반)
ERROR_SEVERITY() 심각도 수준 1~25 (높을수록 심각)
ERROR_STATE() 오류 상태 코드 정수
ERROR_PROCEDURE() 오류 발생 프로시저명 문자열
ERROR_LINE() 오류 발생 라인 번호 정수
ERROR_MESSAGE() 상세 오류 메시지  

주의: 이 함수들은 CATCH 블록 내에서만 NULL이 아닌 값을 반환합니다.

3. 오류 로깅 테이블 구현

-- 오류 로그 테이블 생성
CREATE TABLE ErrorLog (
    ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(128),
    ErrorLine INT,
    ErrorMessage NVARCHAR(4000),
    UserName NVARCHAR(128),
    HostName NVARCHAR(128),
    AppName NVARCHAR(128),
    SessionID INT,
    LoggedDateTime DATETIME DEFAULT GETDATE()
);

-- 인덱스 추가 (쿼리 성능 향상)
CREATE INDEX IX_ErrorLog_DateTime ON ErrorLog(LoggedDateTime DESC);
CREATE INDEX IX_ErrorLog_Procedure ON ErrorLog(ErrorProcedure);

4. 종합적인 오류 처리 예제

CREATE PROCEDURE usp_InsertEmployee
    @EmployeeID INT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Department NVARCHAR(50),
    @ErrorMessage NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ReturnCode INT = 0;

    -- 파라미터 검증
    IF @EmployeeID IS NULL OR @EmployeeID <= 0
    BEGIN
        SET @ErrorMessage = 'EmployeeID는 필수이며 0보다 커야 합니다.';
        RETURN 1;
    END

    IF @FirstName IS NULL OR LEN(@FirstName) = 0
    BEGIN
        SET @ErrorMessage = 'FirstName은 필수입니다.';
        RETURN 1;
    END

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Employees 
            (EmployeeID, FirstName, LastName, Department, CreatedDate)
        VALUES 
            (@EmployeeID, @FirstName, @LastName, @Department, GETDATE());

        -- 감시 업데이트 (감사 추적)
        INSERT INTO AuditLog 
            (TableName, Action, RecordID, ChangedBy, ChangedDateTime)
        VALUES 
            ('Employees', 'INSERT', @EmployeeID, SUSER_SNAME(), GETDATE());

        COMMIT TRANSACTION;

        SET @ErrorMessage = '직원 정보가 성공적으로 추가되었습니다.';
        SET @ReturnCode = 0;

    END TRY
    BEGIN CATCH
        -- 트랜잭션 상태 확인
        IF XACT_STATE() = -1
            ROLLBACK TRANSACTION;
        ELSE IF XACT_STATE() = 1
            ROLLBACK TRANSACTION;

        -- 오류 로깅
        INSERT INTO ErrorLog 
            (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, 
             ErrorLine, ErrorMessage, UserName, HostName, SessionID)
        VALUES 
            (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), 
             ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), 
             SUSER_SNAME(), HOST_NAME(), @@SPID);

        SET @ErrorMessage = 'ERROR [' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ']: ' + 
                           ERROR_MESSAGE();
        SET @ReturnCode = -1;

    END CATCH

    RETURN @ReturnCode;
END;

5. 트랜잭션 관리

CREATE PROCEDURE usp_TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 첫 번째 계정에서 출금
        UPDATE Accounts
        SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccount;

        IF @@ROWCOUNT = 0
            THROW 50001, '출금 계정을 찾을 수 없습니다.', 1;

        -- 두 번째 계정에 입금
        UPDATE Accounts
        SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccount;

        IF @@ROWCOUNT = 0
            THROW 50002, '입금 계정을 찾을 수 없습니다.', 1;

        COMMIT TRANSACTION;
        PRINT '송금이 완료되었습니다.';

    END TRY
    BEGIN CATCH
        -- XACT_STATE() 반환값:
        -- -1: 롤백 불가능한 트랜잭션
        --  0: 활성 트랜잭션 없음
        --  1: 롤백 가능한 트랜잭션

        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;  -- 오류 재발생 (SQL Server 2012+)
    END CATCH
END;

6. THROW 문 사용 (권장)

SQL Server 2012 이상에서는 THROW 문을 사용하여 더 나은 오류 처리가 가능합니다.

CREATE PROCEDURE usp_UpdateProduct
    @ProductID INT,
    @Price DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    -- 파라미터 검증
    IF @ProductID <= 0
        THROW 50001, '유효하지 않은 ProductID입니다.', 1;

    IF @Price < 0
        THROW 50002, '가격은 음수일 수 없습니다.', 1;

    BEGIN TRY
        UPDATE Products
        SET Price = @Price
        WHERE ProductID = @ProductID;

        IF @@ROWCOUNT = 0
            THROW 50003, '제품을 찾을 수 없습니다.', 1;

    END TRY
    BEGIN CATCH
        THROW;  -- 원본 오류와 함께 전파
    END CATCH
END;

성능 튜닝 기법

1. SET NOCOUNT ON/OFF

CREATE PROCEDURE usp_GetEmployees
    @Department NVARCHAR(50)
AS
BEGIN
    -- NOCOUNT ON: "n개 행 영향을 받음" 메시지 억제
    -- 네트워크 트래픽 감소 및 성능 향상
    SET NOCOUNT ON;

    SELECT EmployeeID, FirstName, LastName, Department, Salary
    FROM Employees
    WHERE Department = @Department
    ORDER BY FirstName, LastName;

    -- 필요시 OFF로 전환 (예: 영향 받은 행 수 확인 필요)
    SET NOCOUNT OFF;
END;

효과: CURSOR와 함께 사용할 때 특히 효과적 ("1행 영향" 메시지가 반복되는 오버헤드 제거)

2. 스키마 이름 사용

-- 좋은 예: 스키마 명시
CREATE PROCEDURE dbo.usp_GetSales
AS
BEGIN
    SET NOCOUNT ON;

    SELECT s.SalesID, s.Amount, s.SalesDate
    FROM dbo.Sales s  -- 스키마 명시
    WHERE s.SalesDate >= CAST(GETDATE() AS DATE);
END;

-- 피할 예: 스키마 생략
CREATE PROCEDURE GetSales  -- sp_ 접두사 피함
AS
BEGIN
    SELECT SalesID, Amount, SalesDate
    FROM Sales  -- 스키마 미지정 (검색 시간 증가)
    WHERE SalesDate >= CAST(GETDATE() AS DATE);
END;

이유: SQL Server는 스키마 명시 없이 주인 스키마를 검색하므로 오버헤드 발생

3. 저장 프로시저 명명 규칙

-- 권장
CREATE PROCEDURE dbo.usp_InsertCustomer ...  -- usp_ 접두사
CREATE PROCEDURE dbo.usp_UpdateOrder ...
CREATE PROCEDURE dbo.usp_DeleteInvoice ...

-- 피할 규칙
CREATE PROCEDURE sp_InsertCustomer ...       -- sp_ 접두사
-- 이유: SQL Server는 sp_로 시작하는 프로시저를 마스터 DB에서 먼저 검색

4. SELECT 문 최적화

-- 피할 예: SELECT *
CREATE PROCEDURE usp_GetEmployeeInfo
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *  -- 불필요한 큰 바이너리 데이터 포함 가능
    FROM Employees;
END;

-- 권장: 필요한 열만 선택
CREATE PROCEDURE usp_GetEmployeeInfo
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeID, FirstName, LastName, Email, Department
    FROM Employees;
END;

이점:

  • 네트워크 대역폭 절감
  • 메모리 사용량 감소
  • 쿼리 성능 향상

5. 커서 대신 집합 기반 연산 사용

-- 피할 예: CURSOR 사용
CREATE PROCEDURE usp_UpdateSalesBonus_Cursor
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmployeeID INT, @Sales DECIMAL(10,2);
    DECLARE SalesCursor CURSOR FOR
        SELECT EmployeeID, TotalSales FROM EmployeeSales
        WHERE TotalSales > 50000;

    OPEN SalesCursor;
    FETCH NEXT FROM SalesCursor INTO @EmployeeID, @Sales;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Employees
        SET Bonus = @Sales * 0.1
        WHERE EmployeeID = @EmployeeID;

        FETCH NEXT FROM SalesCursor INTO @EmployeeID, @Sales;
    END

    CLOSE SalesCursor;
    DEALLOCATE SalesCursor;
END;

-- 권장: 집합 기반 연산
CREATE PROCEDURE usp_UpdateSalesBonus
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE e
    SET Bonus = es.TotalSales * 0.1
    FROM Employees e
    INNER JOIN EmployeeSales es ON e.EmployeeID = es.EmployeeID
    WHERE es.TotalSales > 50000;
END;

집합 기반 연산의 이점:

  • 메모리 효율성 (CURSOR 메타데이터 제거)
  • 잠금 감소
  • 병렬 처리 가능
  • 성능: 10~100배 향상 가능

6. 커서가 필요한 경우의 최적화

-- 최적화된 CURSOR 사용
CREATE PROCEDURE usp_ProcessOrders_Optimized
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OrderID INT;

    -- FAST_FORWARD: 성능 최적화 (읽기 전용, 순방향만 가능)
    DECLARE OrderCursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT OrderID
        FROM Orders
        WHERE OrderStatus = 'Pending'
        AND OrderDate >= DATEADD(DAY, -30, GETDATE());

    OPEN OrderCursor;
    FETCH NEXT FROM OrderCursor INTO @OrderID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC usp_ProcessOrder @OrderID;
        FETCH NEXT FROM OrderCursor INTO @OrderID;
    END

    CLOSE OrderCursor;
    DEALLOCATE OrderCursor;
END;

CURSOR 옵션 비교:

  • LOCAL: CURSOR 범위를 프로시저로 제한
  • FAST_FORWARD: 읽기 전용 + 순방향 (최고 성능)
  • READ_ONLY: 데이터 수정 불가 (잠금 제거)

7. 임시 테이블과 테이블 변수 활용

-- 큰 결과 집합: 임시 테이블 사용
CREATE PROCEDURE usp_ProcessLargeDataset
AS
BEGIN
    SET NOCOUNT ON;

    -- 임시 테이블 (디스크 기반, 인덱스 가능)
    CREATE TABLE #TempProcessing (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        SourceID INT,
        ProcessedValue DECIMAL(10,2),
        ProcessedDate DATETIME
    );

    -- 데이터 삽입
    INSERT INTO #TempProcessing (SourceID, ProcessedValue, ProcessedDate)
    SELECT SourceID, Value * 1.1, GETDATE()
    FROM SourceData
    WHERE Value > 1000;

    -- 처리...
    UPDATE #TempProcessing
    SET ProcessedValue = ProcessedValue * 0.95
    WHERE ProcessedDate < DATEADD(DAY, -1, GETDATE());

    -- 결과 반환
    SELECT * FROM #TempProcessing;

    -- 자동 정리 (프로시저 종료 시)
    DROP TABLE #TempProcessing;
END;

-- 작은 결과 집합: 테이블 변수 사용
CREATE PROCEDURE usp_ProcessSmallDataset
AS
BEGIN
    SET NOCOUNT ON;

    -- 테이블 변수 (메모리 기반, 더 빠름)
    DECLARE @TempData TABLE (
        ID INT,
        Value DECIMAL(10,2)
    );

    INSERT INTO @TempData
    SELECT ID, Value FROM Orders WHERE OrderAmount > 500;

    SELECT * FROM @TempData;
END;

임시 테이블 vs 테이블 변수:

특성 임시 테이블 테이블 변수
저장소 디스크 (대량 데이터) 메모리 (소량 데이터)
인덱스 가능 기본 키만 가능
통계 지원 미지원
재사용 아니오 (매번 생성) 아니오 (범위 제한)
대량 데이터 추천 비추천

8. 트랜잭션 범위 최소화

-- 피할 예: 긴 트랜잭션
CREATE PROCEDURE usp_LongTransaction
AS
BEGIN
    BEGIN TRANSACTION;

    -- 1. 데이터 검증 (트랜잭션 외부로 이동)
    IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = 1)
    BEGIN
        ROLLBACK;
        RETURN 1;
    END

    -- 2. 비즈니스 로직
    WAITFOR DELAY '00:00:05';  -- 네트워크 대기

    -- 3. DB 수정
    UPDATE Customers SET LastOrder = GETDATE();

    COMMIT;
END;

-- 권장: 트랜잭션 최소화
CREATE PROCEDURE usp_OptimizedTransaction
AS
BEGIN
    -- 1. 트랜잭션 외부에서 검증
    IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = 1)
        RETURN 1;

    -- 2. 비즈니스 로직 (트랜잭션 외부)
    WAITFOR DELAY '00:00:05';

    -- 3. 최소 범위의 트랜잭션
    BEGIN TRANSACTION;

    UPDATE Customers SET LastOrder = GETDATE();

    COMMIT;
END;

이점:

  • 잠금 시간 단축
  • 데드락 위험 감소
  • 동시성(Concurrency) 향상

인덱싱 전략

1. 인덱스의 역할

인덱스는 테이블 스캔을 피하고 빠른 데이터 검색을 가능하게 합니다.

-- 인덱스 없음: 전체 테이블 스캔 (느림)
SELECT * FROM Orders WHERE CustomerID = 100;

-- 인덱스 있음: 인덱스 탐색 (빠름)
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

2. 클러스터드 인덱스 vs 비클러스터드 인덱스

-- 클러스터드 인덱스 (테이블당 1개만 가능)
-- 주로 기본 키에 자동 생성됨
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID);

-- 비클러스터드 인덱스 (테이블당 999개까지 가능)
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

3. 복합 인덱스 설계

-- 자주 함께 사용되는 열에 대한 복합 인덱스
CREATE INDEX IX_Sales_CompanyDate ON Sales(CompanyID, SalesDate)
INCLUDE (TotalAmount, Discount);

-- 쿼리 예
SELECT SalesID, TotalAmount
FROM Sales
WHERE CompanyID = 5
  AND SalesDate >= '2024-01-01'
ORDER BY SalesDate DESC;
-- 이 인덱스가 위 쿼리를 완전히 커버함

4. 포함 열 (INCLUDE) 활용

-- 인덱스 키: 탐색에 사용
-- 포함 열: 조회에 사용 (키 검색 이후)
CREATE INDEX IX_Products_Category_Price ON Products(CategoryID)
INCLUDE (ProductName, Price, Stock);

-- 커버링 인덱스: 테이블 액세스 없이 인덱스만으로 데이터 제공
SELECT ProductName, Price, Stock
FROM Products
WHERE CategoryID = 1;

5. 인덱싱 우수 사례

-- 좋은 후보:
-- 1. WHERE 절에 자주 사용되는 열
CREATE INDEX IX_Employees_Department ON Employees(Department);

-- 2. JOIN 조건 열 (외래 키)
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

-- 3. ORDER BY 열
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate DESC);

-- 나쁜 후보:
-- 1. 선택도 낮은 열 (중복값 많음)
-- 예: Gender (남/여 2개 값만) - 인덱스 도움 없음

-- 2. 매우 큰 텍스트 열
CREATE FULLTEXT INDEX ON Products(Description);  -- 전문 검색 사용

-- 3. 자주 업데이트되는 열 (DML 오버헤드)

6. 인덱스 유지보수

-- 인덱스 단편화 확인
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000;

-- 단편화 해결
-- 10% 이상 30% 미만: 재구성 (온라인 가능)
ALTER INDEX IX_Orders_CustomerID ON Orders
REORGANIZE;

-- 30% 이상: 재구축 (온라인으로 진행 권장)
ALTER INDEX IX_Orders_CustomerID ON Orders
REBUILD WITH (ONLINE = ON);

쿼리 최적화

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

파라미터 스니핑은 저장 프로시저가 첫 실행의 파라미터 값에 맞춘 실행 계획을 생성하고 재사용할 때 발생합니다.

-- 문제 예시
CREATE PROCEDURE usp_GetOrders
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

-- 첫 실행: @CustomerID = 1 (1개 행)
-- → 인덱스 탐색 실행 계획 생성

-- 두 번째 실행: @CustomerID = 999 (10,000개 행)
-- → 첫 번째 계획 재사용 (최적이 아님)
-- → 테이블 스캔이 더 나은 경우도 같은 계획 사용

해결 방법 1: WITH RECOMPILE (임시 해결)

CREATE PROCEDURE usp_GetOrders
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);  -- 매번 새로운 계획 생성
END;

-- 단점: CPU 오버헤드 증가 (매번 재컴파일)

해결 방법 2: 프로시저 분할

-- 부모 프로시저
CREATE PROCEDURE usp_GetOrders
    @CustomerID INT
AS
BEGIN
    -- 조건에 따라 다른 프로시저 호출
    IF @CustomerID IS NULL
        EXEC usp_GetOrders_Null
    ELSE
        EXEC usp_GetOrders_Value @CustomerID;
END;

-- 자식 프로시저들 (각각 고유 계획)
CREATE PROCEDURE usp_GetOrders_Null
AS
BEGIN
    SELECT OrderID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID IS NULL;
END;

CREATE PROCEDURE usp_GetOrders_Value
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

해결 방법 3: 동적 SQL (sp_executesql)

CREATE PROCEDURE usp_GetOrders_Dynamic
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = 'SELECT OrderID, OrderDate, Amount ' +
               'FROM Orders ' +
               'WHERE 1=1 ';

    -- 파라미터 처리
    IF @CustomerID IS NOT NULL
        SET @SQL = @SQL + 'AND CustomerID = @CustomerID ';

    -- sp_executesql로 실행 (파라미터 스니핑 우회)
    EXEC sp_executesql @SQL,
        N'@CustomerID INT',
        @CustomerID = @CustomerID;
END;

2. 실행 계획 분석

-- 예상 실행 계획 보기 (Ctrl+L)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01'
ORDER BY o.OrderDate DESC;

-- 결과 확인
-- Table 'Orders'. Scan count 1, logical reads 150
-- Table 'Customers'. Scan count 1, logical reads 50
-- SQL Server 실행 시간: 150ms

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

해석:

  • Scan count: 테이블 접근 횟수 (낮을수록 좋음)
  • Logical reads: 읽은 8KB 페이지 수 (낮을수록 좋음)
  • 높은 Logical reads = 인덱스 부재 또는 부적절한 조인 표시

3. JOIN 최적화

-- 피할 예: 비효율적 JOIN
SELECT o.OrderID, c.CustomerName
FROM Orders o
CROSS JOIN Customers c  -- 카르테시안 곱 (위험)
WHERE o.CustomerID = c.CustomerID;

-- 권장: 명시적 INNER JOIN
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 복합 JOIN 최적화
SELECT o.OrderID, o.Amount, p.ProductName, s.SalesPersonName
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Employees s ON o.SalesPersonID = s.EmployeeID
WHERE o.OrderDate >= '2024-01-01'
  AND p.Category = 'Electronics';
  -- 먼저 Orders와 OrderItems를 조인한 후
  -- Products 조인 (필터링 후)
  -- Employees 마지막 조인

4. 집계 함수 최적화

-- 피할 예: 서브쿼리 집계
SELECT CustomerID, 
       (SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID) AS OrderCount,
       (SELECT SUM(Amount) FROM Orders WHERE CustomerID = c.CustomerID) AS TotalAmount
FROM Customers c;

-- 권장: GROUP BY로 한 번의 스캔
SELECT 
    c.CustomerID,
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    SUM(o.Amount) AS TotalAmount,
    AVG(o.Amount) AS AvgOrderAmount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING COUNT(o.OrderID) > 0;

5. 윈도우 함수 활용 (SQL Server 2012+)

-- 부서별 급여 순위 계산
SELECT 
    EmployeeID,
    FirstName,
    Department,
    Salary,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank,
    LAG(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS PreviousSalary,
    LEAD(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS NextSalary
FROM Employees;

-- 이동 평균 계산 (성능 분석용)
SELECT 
    SalesDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY SalesDate 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAverage_7Days
FROM Sales
WHERE SalesDate >= '2024-01-01'
ORDER BY SalesDate;

 

데이터베이스 유지보수

1. 통계 관리

-- 자동 통계 생성/업데이트 확인
SELECT 
    DATABASEPROPERTYEX('YourDB', 'IsAutoCreateStatistics') AS AutoCreateStats,
    DATABASEPROPERTYEX('YourDB', 'IsAutoUpdateStatistics') AS AutoUpdateStats;

-- 통계 직접 업데이트
UPDATE STATISTICS TableName;  -- 모든 통계 업데이트

UPDATE STATISTICS TableName StatName  -- 특정 통계만
WITH FULLSCAN;  -- 전체 스캔 (정확도 높음, 시간 오래 걸림)

UPDATE STATISTICS TableName StatName
WITH SAMPLE 50 PERCENT;  -- 50% 샘플 (빠름, 정확도 낮음)

-- 오래된 통계 확인
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticName,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
    GETDATE() AS CurrentDate
FROM sys.stats s
WHERE STATS_DATE(s.object_id, s.stats_id) < DATEADD(DAY, -7, GETDATE())
ORDER BY LastUpdated DESC;

2. 통합 유지보수 계획

-- 저장 프로시저: 완전한 데이터베이스 유지보수
CREATE PROCEDURE usp_DatabaseMaintenance
    @DatabaseName NVARCHAR(128),
    @IndexFragThreshold INT = 30  -- 30% 이상 단편화 시 재구축
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);

    -- 1. 데이터베이스 무결성 검사
    PRINT '데이터베이스 무결성 검사 시작...';
    SET @SQL = 'DBCC CHECKDB (' + @DatabaseName + ', REPAIR_REBUILD);';
    EXEC sp_executesql @SQL;

    -- 2. 인덱스 재구축/재구성
    PRINT '인덱스 유지보수 시작...';

    DECLARE IndexCursor CURSOR FOR
    SELECT 
        DB_NAME(ips.database_id) AS DBName,
        OBJECT_NAME(ips.object_id) AS TableName,
        i.name AS IndexName,
        ips.index_id,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName), NULL, NULL, NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
        AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10
        AND ips.page_count > 1000;

    DECLARE @TableName NVARCHAR(128), @IndexName NVARCHAR(128), @Fragmentation FLOAT;

    OPEN IndexCursor;
    FETCH NEXT FROM IndexCursor INTO @DBName, @TableName, @IndexName, @IndexID, @Fragmentation;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Fragmentation >= @IndexFragThreshold
        BEGIN
            PRINT '재구축 중: ' + @TableName + '.' + @IndexName;
            SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + 
                      ' REBUILD WITH (ONLINE = ON);';
        END
        ELSE
        BEGIN
            PRINT '재구성 중: ' + @TableName + '.' + @IndexName;
            SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE;';
        END

        BEGIN TRY
            EXEC sp_executesql @SQL;
        END TRY
        BEGIN CATCH
            PRINT 'ERROR: ' + ERROR_MESSAGE();
        END CATCH

        FETCH NEXT FROM IndexCursor INTO @DBName, @TableName, @IndexName, @IndexID, @Fragmentation;
    END

    CLOSE IndexCursor;
    DEALLOCATE IndexCursor;

    -- 3. 통계 업데이트
    PRINT '통계 업데이트 시작...';
    EXEC sp_updatestats;

    -- 4. 트랜잭션 로그 백업
    PRINT '트랜잭션 로그 백업...';
    SET @SQL = 'BACKUP LOG ' + @DatabaseName + 
               ' TO DISK = ''\\backup\' + @DatabaseName + '_' + 
               CONVERT(NVARCHAR(20), GETDATE(), 112) + '.bak'';';
    EXEC sp_executesql @SQL;

    PRINT '데이터베이스 유지보수 완료!';
END;

-- 실행
EXEC usp_DatabaseMaintenance @DatabaseName = 'YourDatabase', @IndexFragThreshold = 30;

3. 성능 모니터링

-- 현재 실행 중인 쿼리 확인
SELECT 
    r.session_id,
    r.status,
    r.command,
    SUBSTRING(st.text, r.statement_start_offset/2, 
              (CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(st.text) 
                    ELSE r.statement_end_offset END - r.statement_start_offset)/2) AS QueryText,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50;

-- 느린 쿼리 기록 조회 (Query Store)
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.avg_duration,
    rs.execution_count
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_query q ON rs.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.avg_duration > 1000  -- 1초 이상
ORDER BY rs.avg_duration DESC;

-- 메모리 사용량 확인
SELECT 
    type,
    SUM(single_pages_kb + multi_pages_kb) / 1024 AS UsedMemory_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY UsedMemory_MB DESC;

 

모범 사례 체크리스트

개발 단계

  • 파라미터 검증: NULL 체크 및 범위 확인
  • TRY-CATCH 구현: 모든 트랜잭션 감싸기
  • 오류 로깅: 프로덕션 지원을 위한 상세 정보 기록
  • 반환 코드: 성공/실패 상태 명확히 전달
  • 트랜잭션 관리: ROLLBACK 및 XACT_STATE() 확인
  • SET NOCOUNT ON: 성능 향상
  • 스키마 명시: dbo. 접두사 사용
  • 명명 규칙: usp_ 접두사 (sp_ 피함)

쿼리 최적화

  • SELECT * 피함: 필요한 열만 선택
  • 집합 기반 연산: CURSOR 최소화
  • JOIN 최적화: 올바른 조인 타입 사용
  • 인덱스 활용: WHERE, JOIN, ORDER BY 열 인덱싱
  • 서브쿼리 최소화: GROUP BY 활용
  • 실행 계획 분석: 파라미터 스니핑 감지

성능 튜닝

  • 임시 테이블/변수: 적절한 자료구조 선택
  • 트랜잭션 범위: 최소한으로 유지
  • DEADLOCK_PRIORITY: 필요시 우선순위 설정
  • 통계 업데이트: 주기적인 유지보수 계획

유지보수

  • 정기적 백업: 일일 백업 및 테스트
  • 인덱스 단편화 모니터링: 매달 확인
  • 통계 업데이트: 주간 스케줄
  • 무결성 검사: DBCC CHECKDB 월 1회
  • 성능 기준선: 새로운 프로시저 성능 측정

보안

  • 파라미터화된 쿼리: SQL 인젝션 방지
  • 권한 최소화: 필요한 권한만 부여
  • 민감한 데이터: 암호화 및 감시
  • 감사 로깅: 중요 작업 기록

결론

효과적인 SQL 저장 프로시저 개발은 다음 세 가지 기둥 위에 서 있습니다:

  1. 견고한 오류 처리: TRY-CATCH와 트랜잭션 관리로 데이터 무결성 보장
  2. 쿼리 최적화: 인덱싱, 집합 기반 연산, 실행 계획 분석으로 성능 확보
  3. 정기적 유지보수: 통계 업데이트, 인덱스 재구축, 성능 모니터링으로 안정성 유지

이 가이드의 모든 우수 사례를 적용하면 프로덕션 환경에서 안정적이고 고성능인 데이터베이스 시스템을 구축할 수 있습니다.

추가 자료

  • Microsoft SQL Server 공식 문서: https://learn.microsoft.com/sql/
  • Query Store: 성능 분석 및 튜닝
  • Execution Plans: 매월 성능 기준선 비교
  • Extended Events: 고급 성능 추적
  • SQL Server Management Studio: 내장 도구 활용

마지막 기억할 점: 성능 문제는 측정 → 분석 → 개선의 반복 프로세스입니다. 추측하지 말고 항상 데이터로 검증하세요.

반응형
저작자표시 비영리 변경금지 (새창열림)

'● Data Insights > SQL' 카테고리의 다른 글

SQL Server DB 모니터링 실전: 성능 진단 및 장애 대응 시나리오  (2) 2025.11.24
MSSQL 분산 테이블(Distribution) 방식 개요  (0) 2025.11.17
MSSQL 실행계획과 쿼리 통계: 개념, 중요성, 활용 방법 및 내부 로직  (0) 2025.11.15
MSSQL 로그인 및 보안 관리 가이드  (0) 2025.11.14
SQL에서 Stored Procedure 사용 가이드  (0) 2025.11.08

관련글

티스토리툴바