안녕하세요. 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
