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

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 *(1 - 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 * (1 - @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 인라인