SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

 

·         Version : SQL Server 2017 later, Azure SQL Database

 

SQL Server 2017  Azure SQL Database 런타임 환경에 최적화된 쿼리 처리 개선 기능을 도입했다이러한 개선 사항에는 Batch mode Adaptive Joins, Batch mode memory grant feedback, Interleaved execution for multi-statement table valued functions 포함된다.

·         Batch mode Adaptive Joins : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-adaptive-joins

·         Batch mode memory grant feedback  https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback

·         Interleaved execution for multi-statement table valued functions : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#interleaved-execution-for-multi-statement-table-valued-functions

 

SQL Server 2019에서는 Intelligent query processing(QP) 제품군에서  가지 새로운 기능으로 쿼리 처리 기능을 더욱 확장하고 있다.

·         Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

 

이번 포스트에서는 SQL Server 2019 CTP 2.1에서 사용할  있는QP 기능  하나인 Scalar T-SQL UDF  inline 대해서 살펴본다.

 

T-SQL UDF SQL 쿼리에서 코드 재사용  모듈화를 구현한다복잡한 비즈니스 규칙과 같은 일부 계산은 명령형 UDF 형식으로 표현하기가  쉽다. UDF 복잡한 SQL 조회를 작성하는 전문 지식 없이 복잡한 논리를 해결하는데 도움이 된다하지만 이러한 장점에도 불구하고 열악한 성능문제 때문에 최대한 사용을 자제하거나 금지한다.

Scala UDF inline 기능의 목표는 UDF 실행의 주요 병목인 Scala UDF 호출하는 쿼리의 성능을 향상 시키는 것이다 Scala UDF 느렸던 이유는 Scala UDF 도입되었을 변수 할당, IF-ELSE 분기루프 등과 같은 친숙한 구문을 사용하여 사용자가 비즈니스 로직을 표현하였다아래 스크립트는 @ckey값이 주어지면 스칼라 UDF 사용하여 고객이 지정한 모든 주문의  가격을 계산한 다음 IF-ELSE 논리를 사용하여  가격을 기준으로 카테고리 값을 반환한다.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)

RETURNS CHAR(10) AS

BEGIN

       DECLARE @total_price DECIMAL(18,2);

       DECLARE @category CHAR(10);

     

       SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  

       IF @total_price < 500000

              SET @category = 'REGULAR';

       ELSE IF @total_price < 1000000

              SET @category = 'GOLD';

       ELSE

              SET @category = 'PLATINUM';

       RETURN @category;

END

 

UDF 여러 조회에서 사용할  있으며 임계 값을 갱신하거나  카테고리를 추가해야하는 경우 UDF에서만 변경해야한다아래 스크립트는 위에서 만든 UDF 호출하는 간단한 쿼리이다.

-- Q1:

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

 

SQL Server 2017(호환성 수준 140  이전 버전버전에서는 쿼리의 실행 계획은 아래와 같다.


SQL Server CUSTOMER 테이블의 모든 튜플에 대해 UDF 호출하고 결과를 출력한다 실행계획  매우 비효율적이다이러한 쿼리는 다음과 같은 이유로 성능에 좋지 않다.

·         반복 호출 : UDF 대상 튜플  반복적으로 호출된다이로 인해 함수 호출로 인한 반복된 컨텍스트 전환의 추가 비용이 발생한다특히 본문에서 SQL 조회를 실행하는 UDF 심각한 영향을 준다.

·         비용 증가 : 최적화 중에는 관계 연산자만 비용이 청구되고 스칼라 연산자는 계산되지 않는다. Scala UDF 도입되기 전에 다른 스칼라 연산자는 일반적으로 저렴했으며 비용이 필요하지 않았다스칼라 작업에 추가된 작은 CPU 비용으로 충분했다.

·         Interpreted 실행 : UDF 명령문 별로 실행되고 명령문의 일괄 처리로 평가 된다 명령문 자체는 컴파일 되고 컴파일된 계획은 캐시 된다 캐싱 전략은 재컴파일이 발생할때 까지 시간을 절약하지만  문은 독립적으로 실행된다교차 명령문 최적화는 수행되지 않는다.

·         Serial 실행 : SQL Server UDF 호출하는 쿼리에서 쿼리  병렬 처리를 사용하지 않는다.

 

새로운 Scalar UDF inline 기능으로 변경된 사항은 스칼라 UDF 표현식이나 서브쿼리로 변환되어 UDF 연산자의 호출 조회에서 대체된다그러면 이러한 표현식과 서브쿼리가 최적화 된다결과적으로 쿼리 계획에는  이상 사용자 정의 함수 연산자가 없지만  또는 인라인 TVF 같은 계획에서  효과가 관찰된다이를   이해하기 위해서 먼저 아래 예제를 살펴 본다.

-- Q2 (Query with no UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(- L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

 쿼리는 광고 항목의 할인 가격 합계를 계산하고 출하 날짜  배송 우선 순위별로 그룹화 하여 결과를 표시한다표현식 L_EXTENDEDPRICE * (1 - L_DISCOUNT) 해당 광고 항목의 할인 가격에 대한 수식이다할인된 가격을 계산해야하는 곳이면 어디서든 사용할  있도록 계산 함수를 만드는 것이 합리적이다.

-- Scalar UDF to encapsulate the computation of discounted price

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))

RETURNS DECIMAL (12,2) AS

BEGIN

       RETURN @price * (- @discount);

END

 

이제 다음과 같이 쿼리 Q2 수정하여 UDF 사용할  있다.

-- Q3 (Query with UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

아래 표는  3개의 쿼리를 실행한 결과이다앞에서 설명한 이유 때문에 Q3 Q2 비해 성능이 좋지 않다이제 Scala UDF inline 통해 SQL Server 스칼라 표현식을 쿼리로 직접 대체하여 UDF 성능의 한계를 극복한다.

[2CPU (12 core), 96GB RAM, SSD, TPC-H 10GB CCI 데이터 사용]

Query

Q2(UDF 없음)

인라인 업이 Q3

인라인이 포함된 Q3

Execution Time

1.6 second

29 minute 11 second

1.6 second

 

 수있듯이 인라인 없이 Q3 Q2 비해 속도가 매우 느리다그러나 Scala UDF inline 사용하면 Q3 성능은 거의 오버헤드 없이 거의 Q2 동일하다쿼리성능을 저하시키지 않으면서 UDF 모든 이점을 누릴  있다또한 쿼리나 UDF 수정이 없었음을 확인할  있다. Scala UDF inline 통해 SQL Server 다중문 UDF 인라인   있다위에서 주어진 dbo.customer_category 함수와 Q1함수를 살펴보고 이것이 어떻게 동작하는지 살펴보자쿼리 Q1 경우 UDF 인라인된 쿼리 계획은 아래와 같다.


위의 계획에서 얻은  가지 주요 관찰 내용은 아래와 같다.

1.       SQL Server CUSTOMER ORDERS 사이의 암시적 조인을 유추했으며 Join 연산자를 통해 이를 명시적으로 만들었다.

2.       SQL Server ORDERS 대한 암시적 GROUP BY O_CUSTKEY 유추했으며 이를 구현하기 위해 Index Spool  Stream Aggregate 사용했다.

3.       SQL Server 모든 연산자에서 병렬처리를 사용한다.

 

UDF 논리 복잡성에 따라 결과 쿼리 계획이  커지고 복잡해  수도 있다  있듯이 UDF 내부의 작업은 이제  이상 블랙 박스가 아니므로 쿼리 최적화 프로그램은 비용을 절감하고 이러한 작업을 최적화   있다또한 UDF  이상 계획에 없으므로 반복 UDF 호출은 함수 호출 오버헤드를 회피하는 계획으로 대체된다.

 

Scala UDF inline 장점은 성능 오버헤드에 대한 걱정없이 사용자가 Scala UDF 사용할  있다 이로써 사용자는 모듈화되고 재사용가능한 응용프로그램을 구축   있다. UDF 사용한 쿼리에 대해 집합 지향병렬 계획을 수행할  있을뿐만 아니라 기능은  다른 장점이 있다. Scala UDF  이상 interpreted 되지 않으므로 ( : 명령문마다 실행됨데드코드 제거상수 폴딩  상수 전파와 같은 최적화가 가능하다이러한 기술은 UDF 따라  간단하고 효율적인 쿼리 계획으로 이어질  있다.

 

Scala UDF inline 가능한 범위는 아래 링크를 참고 한다.

·         Inlineable Scalar UDFs requirements : https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sqlallproducts-allversions#inlineable-scalar-udfs-requirements

 

Scala UDF inline 가능한지 여부는 sys.sql_modules 카탈로그 뷰에서 is_inlineable라는 속성이 있다 1 인라인이 가능함을 나타내고 0 그렇지 않다 속성값 정의에 의해 인라인 가능하기 때문에 인라인 테이블 반환 함수의 값은 1이다.

 

Scala UDF inline UDF 실행이 쿼리의 주요 병목일때 가장 유용하다병목 현상이 다른곳에 있다면 이점이 없을 수도 있다인라인은 CREATE /ALTER FUNCTION문에서 INLINE = OFF 옵션을 사용하여 UDF 대해 해제할  있다. Scala UDF inline 테스트하려면 기본적으로 데이터베이스 호환성 150 사용해야한다. SQL Server 2019 CTP 2.1에서 Scala UDF inline 사용하려면 쿼리를 실행할  연결된 데이터베이스에 대해 데이터베이스 호환성 수준 150 활성화 한다.

USE [master];

GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150;

GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/introducing-scalar-udf-inlining/

 

 

2018-11-12 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, UDF, Scala UDF Inline, 스칼라 UDF 인라인



출처: https://sqlmvp.tistory.com/1278?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.