[MSSQL] API 에서 SP 호출 시 Timeout, SSMS 에서 실행하면 정상 수행하는 현상 해결하기

상황

---

API 에서 SP 를 호출하면 Timeout 이 발생합니다.

SSMS 에서 매개변수를 똑같이 해서 제가 직접 SP 를 호출하면 0초만에 수행이 완료됩니다.

 

참고로 Timeout 의 기준은 3초입니다.

 

많은 사이트에서는 'recompile' 이나 '매개변스 스니핑'을 하라고 가이드 하고 있습니다.

실제로 recompile 을 하면 해결될 수 있습니다.

그러나 이 방법은 근본적인 방법은 아닙니다.

 

임시 해결 방안 SP recompile

타 사이트에서 말하는 것처럼 해결하기 위한 결론부터 말씀드리면  SP recompile 을 하면 해결됩니다.

다만 SP recompile 은 현재상황을 해결하기 위한 임시방편이고 다시 똑같은 상황이 발생할 수 있습니다.

exec sp_recompile 'sp_name'

 

 

원인 분석

---

원인 분석 - Query Plan 확인

1. Query Plan 에서 Timeout 된 SP 를 조회합니다.

2. max_elapsed_time 과 max_logical_reads 를 확인합니다.

3. max_elapsed_time 이 Timeout 값에 근접한 실행계획을 확인합니다. ( 노란 표시 부분의 query_plan )

참고로 하나의 SP 에서 여러개의 실행계획이 생성될 수 있습니다.

4. 해당 실행계획에서 어떤 인덱스가 사용되었는지 확인합니다.

5. 해당 실행계획을 우클릭 후 xml 로 보기로 하여 실행계획 생성 시 사용된 매개변수를 확인합니다.

매개변수 확인되는 이유는 아래 상황에서 이슈가 발생할 수 있기 때문입니다.

데이터 분포가 얼마 없는 데이터를 조회하면서 플랜을 만들었는데, 이 후 데이터의 분포가 많은 데이터를 조회할 때

6. 원인을 분석 후 개선합니다.

 

원인 분석 결과 - 인덱스 개선

테이블 건수는 4천만건 정도입니다.

맨 처음 실행계획이 생성될 때 사용된 매개인자로 확인한 데이터는 4건입니다.

4건인 결과를 가지고 실행계획을 생성하다 보니 제대로 된 통계를 얻을 수 없었고 최적의 인덱스를 사용하지 못했습니다.

이렇게 잘못된 인덱스로 이 후 백만건이 넘는 데이터를 조회하려고 했고 이슈가 발생하였습니다.

 

잘못된 실행계획으로 Non Clustered 인덱스를 사용

최적의 인덱스로 Clustered 인덱스를 사용

 

 

해결 방안

---

인덱스 힌트 기입

위의 단계를 전부 확인 후, 최종적으로 실행계획 생성 시 인덱스가 잘못 사용되었다는 것을 확인하였습니다.

 

SP 내의 이슈가 있는 쿼리를 찾아서 힌트에 최적화 된 인덱스를 기입한 후에

API 에서 호출 시 Timeout 나는 이슈가 해결되었습니다.

 

인덱스 힌트를 지정함으로 써 부작용이 없는지 꼭 확인을 먼저 하길 바랍니다.

 

 

 

 

 

 

 

 

SQL Server API SP Timeout But Not Timeout In SSMS

SQL Server API SP 호출 시 Timeout 나지만 SSMS 에서는 정상적으로 수행