UDF(User Defined Function) 사용시 잘못된 비용을 예측하여 실행계획을 생성하는 경우가 있어 공유 드립니다.


UDF 사용시에는 실제 실행계획 + SET STATISTICS IO ON 뿐만 아니라 예상 실행 계획 + SET STATISTICS TIME ON+ PROFILER 를 확인하여 검토하여야 할 것 같습니다.


아래는 UDF 사용에 따른 비용 확인을 테스트 한 내용입니다.

TEST 1 . SELECT 내에서 계산과 함수계산에 대한 실행계획을 비교

 --테스트용 테이블 생성

CREATE TABLE dbo.FuncTestTable(

       idx int

       ,name varchar(255)

)

GO

--데이터 입력

INSERT INTO FuncTestTable(idx,name)

       SELECT top 100000 ROW_NUMBER() OVER(ORDER BY (SELECT 1))

             ,'TESTNAME'

             FROM master..spt_values AS a

                    CROSS JOIN master..spt_values AS b;

-- 테스트 함수 생성 RETURN(입력값 * 5)

CREATE FUNCTION dbo.FuncTest5(@idx int)

       RETURNS INT

AS

BEGIN

       DECLARE @R int;

       SET @R = @idx *5;

       RETURN @R

END

 

-- CPU 사용 시간 비용 확인을 위하여 SET STATISTICS TIME ON  추가

SET STATISTICS TIME ON

SELECT MAX(dbo.FuncTest5(idx))

       FROM dbo.FuncTestTable;

SELECT MAX(5*idx)

       FROM dbo.FuncTestTable;

SET STATISTICS TIME OFF


TEST1. 결과확인

실제 실행계획 : 함수 사용시 비용이 49% 로 더 적은 비용을 사용한 것을 확인 할 수 있음

CPU Time 확인 : 실제 실행 계획과는 틀리게 CPU 를 더 소모 하였다는 것을 확인 할 수 있음

예상실행계획 : 예상실행 계획에서는 실제 실행계획에서 확인하지 못했던 UDF의 실행계획을 확인 할 수 있음


TEST 1 결론 : UDF 사용시 함수내에서 사용되는 비용이 계산 되지 않아 실제 실행계획과 함께 SET STATISTICS TIME ON 및 예상실행 계획을 통하여 확인하여야 함.


TEST 2 .함수에 SELECT 문장을 추가하여 추가된 IO 비용 및 실행계획을 확인

--IO 비용을 추가하기 위하여 SELECT 절을 추가한 함수 생성

CREATE FUNCTION dbo.FuncTest5_2(@idx int)

       RETURNS INT

AS

BEGIN

       DECLARE @R int;

       DECLARE @R2 int;

       SET @R = @idx *5;

       SELECT top 1 @R2 = idx FROM dbo.FuncTestTable;

       RETURN @R

END

 

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT MAX(dbo.FuncTest5(idx))

       FROM dbo.FuncTestTable;

      

SELECT MAX(dbo.FuncTest5_2(idx))

       FROM dbo.FuncTestTable;

 

SELECT MAX(5*idx)

       FROM dbo.FuncTestTable;

SET STATISTICS TIME OFF

SET STATISTICS IO OFF 

TEST2. 결과확인

TEST2 실제 실행계획 : 기존 함수와 동일한 비용인 33% 로 SELECT 절에서 계산하는 비용보다 더 적은 비용을 사용하였음을 확인 할 수 있음

CPU Time 확인 :  SELECT 를 포함한 UDF 함수가 CPU 를 훨씬 많이 소모 하였다는 것을 확인 할 수 있음

 

 

IO 비용 확인 : IO 비용은 함수내에 SELECT 가 포함되어 있어 더 많이 확인 되어야 하는데 동일한 결과가 출력 (이것 역시 믿을 수 없는 결과로 확인)

프로파일러 확인 : 함수를 포함한 쿼리의 IO 비용 및 CPU 비용을 이제서야 확인 할 수 있음


예상 실행 계획 : 예상실행 계획에서는 실제 실행계획에서 확인하지 못했던 UDF의 실행계획을 확인 할 수 있음


최종 결론 .

UDF 사용시 실제 실행 계획 및 SET STATISTICS IO ON 만으로는 비용을 계산할 수 없으므로 PROFILER , 예상 실행계획 , SET STATISTICS TIME ON 을 확인하여 비용을 확인 하여야 할 것 같습니다.



신고
Posted by 조창원