[SQL Server] 쿼리 개선 - WHERE 구문 UDF 사용

개요 

개발팀에서 특정 SP에서 지연이 발생한다는 내용 전달과 확인 요청을 받음.

통계값을 확인하였을 때 큰 이슈는 없어 보이는 SP 였음.

 

확인 내용

1. API CALL 과 동일하게 ANSI 셋팅 이후 진행 (SP의 경우 플랜캐시로 컴파일을 생략하므로 ANSI 셋팅이 다르면 API 호출과는 다른 플랜캐시가 생성되어 정확한 확인이 안될 수 있음)
2. 출력되는 데이터는 0건 메타데이터 관리 테이블을 필터링 하는 과정에서 대부분의 시간이 소요됨.

특이사항

1. SET STATISTICS IO, TIME 으로 확인한 메타데이터 관리 테이블 및 데이터 처리 내용
2. 실행계획 중 필터 부분의 확인 내용
   

 

3. Trace를 통한 확인 내용 (duration, cpu는 show statistics io, time 을 통해 확인한 값과 동일했지만 reads 지표는 상당히 높게 잡힘)

 

테스트 진행 내용
SELECT * 
FROM TABLEA as A WITH(NOLOCK)        
INNER JOIN TABLEB B WITH (NOLOCK) ON A.COL1= B.COL1
WHERE B.COL2 IS not NULL
AND dbo.UF_TEST1(A.COL1, B.COL1 ) LIKE '%%'
AND dbo.UF_TEST2( A.COL2, B.COL2 ) LIKE '%%'

SELECT * 
FROM TABLEA as AWITH(NOLOCK)        
INNER JOIN TABLEB AS B WITH (NOLOCK) ON A.COL1= B.COL1
WHERE B.COL2 IS not NULL

차이 확인
SET STATISTICS IO 출력 값 동일 확인  (상단 WHERE 조건 있을 떄 하단은 없을 때)

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 243, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TABLEB'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4656 ms,  elapsed time = 7456 ms.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 243, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TABLEB'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 51 ms.

 

PROFILER (Duration, Reads, Cpu 순)
서비스 상황과 동일하게 Reads 에서만 SHOW STATISTICS IO,TIME 확인 값과 크게 다름

 

결론 : 

 

WHERE 조건에 추가된 함수를 실행으로 인해 Duration, Reads, Cpu 증가 

해당 값은 SET STATISTICS IO 를 통하여 확인이 안됨

실행 계획에서 필터 부분으로만 노출되며 어떤 함수를 사용하였는지는 필터 속성의 상세보기 후 조건자를 통하여 추측 가능

→   위 SP는 WHERE 조건에 함수 제거를 통하여 성능 향상을 기대할 수 있음 →  함수 제거 후 많은 성능 향상 확인. 

WHERE 과 SELECT 구문에서 함수 사용은 항상 성능의 일관성을 보장하기가 어려운 부분이다.

최대한 사용하지 않는 방향으로 가이드를 주고 있지만 종종 과거 SP에서 이런 이슈가 확인이 된다.

 

또한 해당 SP에서는 CTE 및 CTE 내 SELECT 구문에서 UDF를 사용하고 있었으며

CTE 함수로 인한 Concatenation 부분에서 대부분의 시간이 소요되며 Spool 또한 발생하였다.

 

해당 부분도 함께 제거하였고 최종 개선은 아래와 같이 되었다

 

duration 55,826 reads 8,160,759 cpu 52,610

-> WHERE 구문 함수 제거 

duration 372 reads 69,922 cpu 343

-> CTE 구문 및 CTE 구문 함수 제거

duration 116 reads 33,385 cpu 110