특정 Stored Procedure가 느려질 때 원인 진단 방법 (MSSQL 기준)

ERP나 대규모 시스템을 운영하다 보면, 어제까지는 잘 돌던 SP(Stored Procedure)가 갑자기 느려지는 경우가 자주 발생합니다.
이번 글에서는 제가 실무에서 경험한 SP 성능 저하 원인 진단 방법을 정리해보겠습니다.

---

1. 세션/프로세스 확인

먼저, 현재 어떤 쿼리가 오래 걸리고 있는지 확인해야 합니다.

 

오래 걸리고 있는 세션 확인

 

SELECT spid, blocked, waittype, waittime, lastwaittype, program_name, cmd, cpu, physical_io FROM sys.sysprocesses WHERE spid > 50 AND blocked <> 0;

• blocked: 다른 세션에 의해 대기 중인 경우
• waittype / lastwaittype: 어떤 리소스를 기다리고 있는지 확인 가능

👉 여기서 락(Block) 때문인지, 단순 실행 계획 문제인지 1차로 분리할 수 있습니다.

---

2. 실행 계획(Execution Plan) 분석

SP가 느려졌다면 대부분 실행 계획(Execution Plan) 문제일 확률이 큽니다.
특히, 통계가 오래되어 비효율적인 인덱스 탐색을 선택했을 수 있습니다.

SSMS에서 실행 계획 보기 (Ctrl + M) 를 통해

→ Index Seek 대신 Table Scan이 발생하는지 확인해야 합니다.

---

3. 통계/인덱스 점검

실행 계획이 꼬였을 경우 다음 작업을 고려합니다.

 

인덱스 통계 업데이트 UPDATE STATISTICS 테이블명; -- 전체 DB 인덱스 통계 갱신 EXEC sp_updatestats;

👉 ERP 시스템은 데이터가 하루에도 수만 건 쌓이다 보니, 주기적인 통계 갱신 작업이 필수입니다.

---

4. 캐시된 실행계획(Plan Cache) 문제

SP는 보통 한 번 실행된 실행 계획을 캐시에 저장해 재사용합니다.
하지만 특정 파라미터로 인해 잘못된 실행 계획이 캐싱되면 이후 호출도 전부 느려집니다.

이럴 때는 Recompile로 해결할 수 있습니다.

 

특정 SP 실행 계획 재컴파일 EXEC sp_recompile N'스키마명.프로시저명';

👉 저도 ERP SP가 하루종일 안돌다가 sp_recompile 한 번으로 바로 정상화된 경험이 있습니다.

---

5. 잠금(Lock) / Deadlock 확인

실행 계획이 정상인데도 느리다면, 동시 접속자가 많은 상황에서 잠금 문제가 발생했을 수 있습니다.

 

잠금 확인 SELECT request_session_id AS spid, resource_type, resource_description, request_mode, request_status FROM sys.dm_tran_locks;

특정 테이블이나 인덱스에 락이 걸려 다른 세션을 전부 기다리게 하는 경우가 많습니다.

---

6. 최적화/튜닝 포인트

• SELECT * 지양 → 필요한 컬럼만 가져오기
• NOLOCK 옵션 검토 (조회 위주일 경우)
• 불필요한 JOIN 제거
• 조건절(WHERE)에 인덱스 컬럼이 잘 사용되는지 확인

---

✅ 정리

Stored Procedure가 갑자기 느려졌을 때는 보통 다음 네 가지 중 하나입니다:

1. 실행 계획 꼬임 → sp_recompile
2. 인덱스/통계 문제 → UPDATE STATISTICS
3. 잠금(Block) 문제 → 세션/Lock 확인
4. 잘못된 쿼리 구조 → 실행 계획 튜닝

👉 실무에서는 즉시 대응(recompile) + 근본 원인(인덱스/쿼리 튜닝) 두 가지를 병행하는 게 중요합니다.