안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 8-7. 저장 프로시저 - 프로시저 캐시와 RECOMPILE을 진행 하겠습니다.


SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

 

이번에 진행할 강좌는 저장 프로시저 캐시와 RECOMPILE입니다. 

 

 

 

TL;DR

저장 프로시저 캐시 기능이 성능 문제를 일으킬 수 있습니다. 이를 방지하기 위해 저장 프로시저 RECOMPILE 옵션을 사용할 수 있습니다.


지난 8-1. 저장 프로시저(Stored Procedure) - 소개 강좌에서 간략히 저장 프로시저 사용 주의사항으로 캐시 성능 문제를 언급했습니다.

 

 

SQL Server 쿼리 실행 계획

SQL Server는 일반 SQL 구문을 실행할 때 실행 계획(Query execution plan)을 생성하고, 이 계획대로 쿼리를 수행합니다. 이런 실행 계획을 생성할 때, SQL Server는 현재 대상 데이터베이스 개체의 상태와 Index, 통계 정보 및 쿼리 패턴 등을 조합해 실행 계획을 작성하는데, 이 과정에서 CPU와 IO가 사용될 수 있습니다. 일반 SQL 구문도 여려 번 서버에 비슷한 패턴이 전달되면 SQL Server는 일반 SQL을 캐시해 버퍼 공간에 두고 재사용하게 됩니다.


저장 프로시저를 이용하면 저장 프로시저를 생성하고 최초 실행될 때 실행 계획을 생성하며, SQL Server가 재시작되거나, 참조하는 데이터베이스 개체의 구조가 변경되기 전까지 실행계획을 재사용합니다.

 

 

저장 프로시저 캐시 기능이 성능 문제를 유발

저장 프로시저는 최초 실행 시 캐시 됩니다. 캐시 기능이 CPU 부하를 낮추고 성능을 높이지만, 캐싱되면서 적용된 테이블의 인덱스 사용 루틴이 잘못 캐시 되어 좋지 않은 결과가 나올 수 있습니다.

예를 들어, 비클러스터형(Non-clustered) 인덱스는 전체 테이블에서 약 3% 내외의 데이터를 가져올 경우 인덱스 검색(Index Seek)을 수행합니다. 하지만, 가변 파라미터로 인해 최초 실행 시 인덱스 스캔(Index Scan)을 하게 되면, 지속적으로 스캔을 하게 되어 성능에 영향이 있을 수 있습니다.

이렇게 비정형 패턴이나 가변 파라미터로 인해 인덱스 선택도를 취하기 어렵거나 성능 이슈가 발생할 수 있고, 저장 프로시저의 실행 계획 캐시가 성능 저하를 유발할 수도 있습니다, 갑자기 인덱스가 나와서 혼란스럽습니다. 이후 강좌에서 배우게 되니 이런 게 있다는 것 정도만 기억해 두세요.

 

 

저장 프로시저 RECOMPILE로 실행 계획 재생성

이런 경우를 방지하기 위해 비정형 쿼리 패턴이나 가변적인 파라미터로 저장 프로시저 실행계획에 문제가 있을 경우를 대비해 저장 프로시저에 RECOMPILE 옵션을 줄 수 있습니다. 


RECOMPILE 옵션을 사용하면, 프로시저가 실행될 때, 일반 SQL 구문처럼 실행계획을 재생성하도록 힌트를 쿼리 최적화기에 전달하게 됩니다. 아래 SQL 구문처럼 RECOMPILE을 설정할 수 있습니다.

 

USE AdventureWorks;
GO  

-- 저장 프로시저 생성 구문에 RECOMPILE
CREATE OR ALTER PROC pEmployeeDisp
@emp_num INT
WITH RECOMPILE  -- RECOMPILE 설정
AS
SELECT emp_num, emp_name, email FROM Employee
WHERE emp_num = @emp_num; 
GO

-- 저장 프로시저 실행
EXEC pEmployeeDisp 2;
GO

-- 또는, 저장 프로시저 실행 시 RECOMPILE 수행
EXEC pEmployeeDisp 2 WITH RECOMPILE
GO

-- 또는, sp_recompile 시스템 저장 프로시저로 RECOMPILE 수행
EXEC sp_recompile N'dbo.pEmployeeDisp'
GO

 

이렇게 저장 프로시저 내부에 RECOMPILE을 설정할 수 있고, 프로시저 실행 시, 또는 sp_recompile로 설정할 수도 있습니다.
 

 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 22127
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12259
2294 SQL강좌: 12-1. 인덱스 생성과 관리 - 인덱스(Index) 소개 file 코난(김대우) 2023.08.18 61
2293 SQL강좌: 11-8. 관계형 데이터베이스 이해 - 데이터베이스 구현 코난(김대우) 2023.08.18 24
2292 SQL강좌: 11-7. 관계형 데이터베이스 이해 - 데이터베이스 물리적 설계 코난(김대우) 2023.08.18 23
2291 SQL강좌: 11-6. 관계형 데이터베이스 이해 - 데이터베이스 논리적 설계 코난(김대우) 2023.08.18 23
2290 SQL강좌: 11-5. 관계형 데이터베이스 이해 - 데이터베이스 설계 요구사항(Requirements) 분석과 개념적 설계 코난(김대우) 2023.08.18 20
2289 SQL강좌: 11-4. 관계형 데이터베이스 이해 - 데이터베이스 설계 목표와 고려사항 코난(김대우) 2023.08.18 16
2288 SQL강좌: 11-3. 관계형 데이터베이스 이해 - 데이터베이스 설계 단계 file 코난(김대우) 2023.08.18 19
2287 SQL강좌: 11-2. 관계형 데이터베이스 이해 - 데이터베이스 생명주기 file 코난(김대우) 2023.08.18 24
2286 SQL강좌: 11-1. 관계형 데이터베이스 이해 - 관계형 모델 코난(김대우) 2023.08.18 33
2285 SQL강좌: 10-4. T-SQL 프로그래밍 - 공통 테이블 식(CTE-Common Table Expression) 코난(김대우) 2023.08.18 47
2284 SQL강좌: 10-3. T-SQL 프로그래밍 - 변수, 조건문(IF-ELSE), 반복문(WHILE), CASE 표현식 코난(김대우) 2023.08.18 27
2283 SQL강좌: 10-2. T-SQL 프로그래밍 - 일괄처리(Batch) 코난(김대우) 2023.08.18 32
2282 SQL강좌: 10-1. T-SQL 프로그래밍 - 소개 코난(김대우) 2023.08.18 21
2281 SQL강좌: 9-3. 커서 - 현업에서 커서 사용 및 주의사항 코난(김대우) 2023.08.18 49
2280 SQL강좌: 9-2. 커서 - 커서 구문 코난(김대우) 2023.08.18 23
2279 SQL강좌: 9-1. 커서(CURSOR) - 커서 소개 코난(김대우) 2023.08.18 40
» SQL강좌: 8-7. 저장 프로시저 - 프로시저 캐시와 RECOMPILE 코난(김대우) 2023.08.18 40
2277 SQL강좌: 8-6. 저장 프로시저 - 종속성(Dependency) 확인 코난(김대우) 2023.08.18 44
2276 SQL강좌: 8-5. 저장 프로시저 - OUTPUT 파라미터 코난(김대우) 2023.08.18 44
2275 SQL강좌: 8-4. 저장 프로시저 - 동적 SQL(Dynamic SQL)과 SQL 주입(Injection) 공격 코난(김대우) 2023.08.18 56





XE Login