[SQL Server] 쿼리 개선 - SP RECOMPILE 추가

수정내용 : 파라미터 값에 따라 쿼리 리소스가 증가하는 SP에 대하여 RECOMPILE OPTION 추가

RECOMPILE에 대해 잘못 알고 있었던 점이 있었다. RECOMPILE을 할 경우 나는 최신의 통계로 실행계획을 다시 수립해서 옵티마이저가 더 좋은 실행계획을 생성하고 이로 인하여 성능이 개선되는 경우가 있다고 생각했는데, 사실 RECOMPILE은 통계 업데이트와는 상관없다. 다만 로컬변수의 상수화가 되는 것이 가장 큰 차이였다. 다만 RECOMPILE 옵션은 매번 SP 호출마다 컴파일을 해야하므로 약간의 CPU 상승은 동반된다(다만 개선된 쿼리가 더 큰 차이의 CPU 개선을 보였음).

 

※ RECOMPILE을 하는 이유

1. 파라미터 스니핑 대책
   SP의 경우 처음 들어오는 매개변수 값에 따라 실행계획이 생성되어지며 이로 인해 데이터 분포도 차이가 있을 경우 비 효율적임. RECOMPILE을 주면 매번 새로운 실행계획을 수립하므로 파라미터 스니핑에 대한 대처 방안이 될 수 있음.
2. 로컬변수의 상수화
   SP 내에 선언된 로컬 변수는 통계값을 사용하지 못하고 전체 데이터 분포 값으로 실행계획이 수립 됨
   RECOMPILE을 사용하면 해당 로컬 변수가 쿼리 내에서 상수값으로 변경되어 처리가 되므로 통계를 사용하게 됨.
   여기서 통계와 분포값은 DBCC SHOW_STATISTICS (테이블, 인덱스) 에서 확인이 가능하며 2번째 칸에 나오는 All density가 전체밀도 (분포값) 3번째 칸에 나오는 각 데이터 마다의 분포도 (통계) 이다.
   이로 인하여 (ISNULL(@변수,'') = ''  OR (COL1 = @변수)) 같은 조건도 변수가 NULL이 아닐 경우 COL1 = 변수의 인덱스 조건을 사용할 수 있게 된다(상수화로 인하여)