스칼라 UDF 인라인 처리

아티클
08/23/2023

적용 대상: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

이 문서에서는 지능형 쿼리 처리 기능 제품군의 기능인 스칼라 UDF 인라인을 소개합니다. 이 기능은 SQL Server에서 스칼라 UDF를 호출하는 쿼리의 성능을 향상시킵니다(SQL Server 2019(15.x)부터 시작).

T-SQL 스칼라 사용자 정의 함수

Transact-SQL에서 구현되고 단일 데이터 값을 반환하는 UDF(사용자 정의 함수)를 T-SQL 스칼라 사용자 정의 함수라고 합니다. T-SQL UDF는 Transact-SQL 쿼리에서 코드 재사용 및 모듈성을 구현하는 우아한 방법입니다. 일부 계산(예: 복잡한 비즈니스 규칙)은 명령적 UDF 형태에서 더 표현하기 쉽습니다. UDF는 복잡한 SQL 쿼리 작성에 대한 전문 지식 없이도 복잡한 논리를 구축하는 데 도움이 됩니다. UDF에 대한 자세한 내용은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

스칼라 UDF 성능

스칼라 UDF는 일반적으로 다음과 같은 이유로 인해 제대로 수행되지 않습니다.

반복 호출입니다. UDF는 한정된 튜플당 한 번씩 반복 방식으로 호출됩니다. 이로 인해 함수 호출로 인해 반복되는 컨텍스트 전환에 대한 추가 비용이 발생합니다. 특히 정의에서 Transact-SQL 쿼리를 실행하는 UDF는 심각한 영향을 받습니다.
비용 부족. 최적화 중에는 관계형 연산자만 비용이 들지만 스칼라 연산자는 비용이 들지 않습니다. 스칼라 UDF가 도입되기 전에는 다른 스칼라 연산자가 일반적으로 저렴했으며 비용이 필요하지 않았습니다. 스칼라 작업에 추가된 작은 CPU 비용만으로 충분했습니다. 실제 비용이 중요하지만 여전히 과소 대표되는 기본 시나리오가 있습니다.
해석된 실행입니다. UDF는 문 일괄 처리, 실행된 문별 문으로 평가됩니다. 각 문 자체가 컴파일되며 컴파일된 계획은 캐시됩니다. 이 캐싱 전략에서는 다시 컴파일하지 않고 각각의 문이 격리 실행되므로 시간을 상당 수준 절약할 수 있습니다. 문 간 최적화는 수행되지 않습니다.
직렬 실행. SQL Server는 UDF를 호출하는 쿼리에서 쿼리 내 병렬 처리를 허용하지 않습니다.

스칼라 UDF의 자동 인라인 처리

스칼라 UDF 인라인 기능의 목표는 UDF 실행이 기본 병목 상태인 T-SQL 스칼라 UDF를 호출하는 쿼리의 성능을 향상시키는 것입니다.

이 새로운 기능을 사용하면 스칼라 UDF가 UDF 연산자 대신 호출 쿼리에서 대체되는 스칼라 식 또는 스칼라 하위 쿼리로 자동으로 변환됩니다. 그런 다음 이러한 식과 하위 쿼리가 최적화됩니다. 따라서 쿼리 계획에는 더 이상 사용자 정의 함수 연산자가 없지만 뷰 또는 인라인 TVF와 같은 해당 효과는 계획에서 관찰됩니다.

예제 1 - 단일 문 스칼라 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) 은 지정된 품목의 할인된 가격에 대한 수식입니다. 이러한 수식은 모듈화 및 재사용의 이점을 위해 함수로 추출할 수 있습니다.

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2) AS
BEGIN
  RETURN @price * (1 - @discount);
END

이제 이 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

앞에서 설명한 이유로 인해 UDF를 사용한 쿼리가 제대로 수행되지 않습니다. 이제 스칼라 UDF 인라인을 사용하면 UDF 본문의 스칼라 식이 쿼리에서 직접 대체됩니다. 이 쿼리를 실행한 결과는 아래 표에 나와 있습니다.

쿼리:	UDF 없는 쿼리	UDF를 사용하여 쿼리(인라인 처리 없이)	스칼라 UDF 인라인을 사용하여 쿼리
실행 시간:	1.6초	29분 11초	1.6초

이러한 숫자는 10GB CCI 데이터베이스(TPC-H 스키마 사용)를 기반으로 하며, SSD에서 지원되는 이중 프로세서(12코어), 96GB RAM이 있는 컴퓨터에서 실행됩니다. 숫자에는 콜드 프로시저 캐시 및 버퍼 풀이 있는 컴파일 및 실행 시간이 포함됩니다. 기본 구성이 사용되었으며 다른 인덱스가 만들어지지 않았습니다.

예제 2 - 다중 문 스칼라 UDF

변수 할당, 조건 분기 등과 같이 여러 T-SQL 문을 통해 구현되는 스칼라 UDF도 인라인 처리가 가능합니다. 다음 스칼라 UDF는 고객 키가 주어지면 해당 고객에 대한 서비스 범주를 결정합니다. SQL 쿼리를 사용하여 고객이 수행한 모든 주문의 총 가격을 먼저 계산하여 범주에 도달합니다. 그런 다음 논리를 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를 호출하는 쿼리를 고려해 보겠습니다.

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

SQL Server 2017(14.x)(호환성 수준 140 이하)에서 이 쿼리에 대한 실행 계획은 다음과 같습니다.

Query Plan without inlining.

계획에서 보듯이 SQL Server는 간단한 전략을 택하고 있습니다. 즉 CUSTOMER 테이블의 모든 튜플에 대해 UDF를 호출하고 결과를 출력합니다. 이 전략은 순진하고 비효율적입니다. 인라인 처리가 있으면 이런 UDF가 해당하는 스칼라 하위 쿼리로 변환되며 호출하는 쿼리에서 UDF 대신 대체됩니다.

동일한 쿼리의 경우 UDF 인라인이 있는 계획은 아래와 같습니다.

Query Plan with inlining.

앞에서 멘션 쿼리 계획에는 더 이상 사용자 정의 함수 연산자가 없지만 이제 보기 또는 인라인 TVF와 같은 계획에서 해당 효과를 관찰할 수 있습니다. 위의 계획에서 몇 가지 주요 관찰은 다음과 같습니다.

SQL Server는 조인 연산자를 통해 암시적 조인을 CUSTOMER 유추하고 ORDERS 명시적으로 만들었습니다.
또한 SQL Server는 암시적 GROUP BY O_CUSTKEY on ORDERS 유추를 수행했으며 IndexSpool + StreamAggregate를 사용하여 구현했습니다.
이제 SQL Server는 모든 연산자에서 병렬 처리를 사용하고 있습니다.

UDF의 논리 복잡성에 따라 결과적인 쿼리 계획이 더 크고 복잡할 수도 있습니다. 여기서 볼 수 있듯이 UDF 내의 작업은 더 이상 불투명하지 않으므로 쿼리 최적화 프로그램은 이러한 작업의 비용을 절감하고 최적화할 수 있습니다. 또한 UDF가 더 이상 계획에 없으므로 반복 UDF 호출이 함수 호출 과부하를 완전히 방지하는 계획으로 바뀝니다.

인라인 가능 스칼라 UDF 요구 사항

다음 조건이 모두 충족되면 스칼라 T-SQL UDF를 인라인 처리할 수 있습니다.

UDF는 다음 구문을 사용하여 작성됩니다.
- DECLARESET: 변수 선언 및 할당
- SELECT: 단일/다중 변수 할당 ^{이 있는 SQL 쿼리 1}.
- IF/ELSE: 임의의 중첩 수준을 사용하여 분기합니다.
- RETURN: 단일 또는 여러 반환 문입니다. SQL Server 2019(15.x) CU5부터 UDF는 인라인 ⁶으로 간주할 단일 RETURN 문만 포함할 수 있습니다.
- UDF: 중첩/재귀 함수는 2^{를 호출}합니다.
- 기타: 관계형 작업(예: EXISTS, IS NULL.
UDF는 시간 종속 함수(예: GETDATE())이거나 부작용 3^{(예: NEWSEQUENTIALID())이 있는 내장 함수를}호출하지 않습니다.
UDF는 절을 EXECUTE AS CALLER 사용합니다(절이 EXECUTE AS 지정되지 않은 경우 기본 동작).
UDF는 테이블 변수 또는 테이블 반환 매개 변수를 참조하지 않습니다.
스칼라 UDF를 호출하는 쿼리는 해당 GROUP BY 절에서 스칼라 UDF 호출을 참조하지 않습니다.
절이 있는 선택 목록에서 DISTINCT 스칼라 UDF를 호출하는 쿼리에는 절이 ORDER BY 없습니다.
UDF는 절에 ORDER BY 사용되지 않습니다.
UDF는 고유하게 컴파일되지 않습니다(interop이 지원됨).
UDF는 계산 열 또는 검사 제약 조건 정의에서 사용되지 않습니다.
UDF는 사용자 정의 형식을 참조하지 않습니다.
UDF에 추가된 서명이 없습니다.
UDF는 파티션 함수가 아닙니다.
UDF에는 CTE(Common Table Expressions)에 대한 참조가 포함되어 있지 않습니다.
UDF에는 인라인될 때(예: @@ROWCOUNT) ⁴를 변경할 수 있는 내장 함수에 대한 참조가 포함되어 있지 않습니다.
UDF에는 스칼라 UDF ⁴에 매개 변수로 전달되는 집계 함수가 포함되어 있지 않습니다.
UDF는 기본 제공 뷰(예: OBJECT_ID4⁾를 참조하지 않습니다.
UDF는 XML 메서드 ⁵를 참조하지 않습니다.
UDF에는 절 ⁵가 없는 SELECT ORDER BY 가 TOP 1 포함되어 있지 않습니다.
UDF에는 절(예: 5)^{을 사용하여 할당을 수행하는 SELECT 쿼리가 ORDER BY 포함되어 있지} 않습니다.SELECT @x = @x + 1 FROM table1 ORDER BY col1
UDF에는 여러 RETURN 문 6^이포함되어 있지 않습니다.
UDF는 RETURN 문 ⁶에서 호출되지 않습니다.
UDF는 함수 ⁶을 STRING_AGG 참조하지 않습니다.
UDF는 원격 테이블 ⁷을 참조하지 않습니다.
UDF 호출 쿼리는 7^{을 ROLLUP}사용하지 GROUPING SETSCUBE않습니다.
UDF 호출 쿼리에는 할당에 대한 UDF 매개 변수로 사용되는 변수(예: SELECT @y = 2@x = UDF(@y)7⁾가 포함되어 있지 않습니다.
UDF는 암호화된 열 8^을참조하지 않습니다.
UDF에는 8^{에 WITH XMLNAMESPACES}대한 참조가 포함되어 있지 않습니다.
UDF를 호출하는 쿼리에는 CTE(Common Table Expressions) ⁸이 없습니다.

^{변수 누적/집계가 있는 1}SELECT 은 인라인 처리(예: SELECT @val += col1 FROM table1)에 대해 지원되지 않습니다.

² 재귀 UDF는 특정 깊이에만 인라인 처리됩니다.

³ 현재 시스템 시간에 따라 결과가 달라지는 내장 함수는 시간에 따라 달라집니다. 일부 내부 전역 상태를 업데이트할 수 있는 내장 함수는 부작용이 있는 함수의 예입니다. 이러한 함수는 내부 상태에 따라 호출할 때마다 서로 다른 결과를 반환합니다.

⁴ SQL Server 2019(15.x) CU2에 추가된 제한 사항

⁵ SQL Server 2019(15.x) CU4에 추가된 제한 사항

⁶ SQL Server 2019(15.x) CU5에 추가된 제한 사항

⁷ SQL Server 2019(15.x) CU6에 추가된 제한 사항

⁸ SQL Server 2019(15.x) CU11에 추가된 제한 사항

최신 T-SQL 스칼라 UDF 인라인 처리 수정 및 인라인 자격 시나리오 변경에 대한 자세한 내용은 기술 자료 문서: FIX: SQL Server 2019의 스칼라 UDF 인라인 문제를 참조하세요.

UDF를 인라인 처리할 수 있는지 여부를 확인합니다.

모든 T-SQL 스칼라 UDF에 대해 sys.sql_modules 카탈로그 보기에는 UDF의 인라인 처리 가능 여부를 표시하는 is_inlineable이라는 속성이 포함되어 있습니다.

이 is_inlineable 속성은 UDF 정의 내에 있는 구문에서 파생됩니다. UDF가 실제로 컴파일 시간에 인라인화할 수 있는지 여부는 검사 않습니다. 자세한 내용은 인라인 처리 조건을 참조하세요.

값 1은 인라인화할 수 있음을 나타내고 0은 그렇지 않은 경우를 나타냅니다. 이 속성은 모든 인라인 TVF에 대해 1 값을 갖습니다. 다른 모든 모듈의 경우 값은 0입니다.

스칼라 UDF를 인라인 처리할 수 있는 경우 항상 인라인 처리된다는 의미는 아닙니다. SQL Server는 UDF를 인라인할지 여부를 쿼리별로 UDF별로 결정합니다. UDF가 인라인되지 않을 수 있는 경우의 몇 가지 예는 다음과 같습니다.

UDF 정의가 수천 줄의 코드로 실행되는 경우 SQL Server는 인라인하지 않도록 선택할 수 있습니다.
절의 UDF 호출 GROUP BY 은 인라인되지 않습니다. 이 결정은 스칼라 UDF를 참조하는 쿼리가 컴파일될 때 결정됩니다.
UDF가 인증서로 서명된 경우 UDF를 만든 후에 서명을 추가하고 삭제할 수 있으므로 스칼라 UDF를 참조하는 쿼리가 컴파일될 때 인라인할지 여부를 결정합니다. 예를 들어 시스템 함수는 일반적으로 인증서로 서명됩니다. sys.crypt_properties 사용하여 서명된 개체를 찾을 수 있습니다.
```
SELECT *
FROM sys.crypt_properties AS cp
INNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
```

인라인 처리가 발생했는지 여부를 확인합니다.

모든 전제 조건이 충족되고 SQL Server가 인라인을 수행하기로 결정하면 UDF를 관계형 식으로 변환합니다. 쿼리 계획에서 인라인 처리가 발생했는지 여부를 쉽게 파악할 수 있습니다.

계획 xml에는 성공적으로 인라인된 UDF에 대한 xml 노드가 없습니다 <UserDefinedFunction> .
특정 XEvent가 내보내집니다.

스칼라 UDF 인라인 사용

데이터베이스에 대해 호환성 수준 150을 사용하도록 설정하여 워크로드를 스칼라 UDF 인라인 처리에 자동으로 적합하게 만들 수 있습니다. Transact-SQL을 사용하여 설정할 수 있습니다. 예시:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

이 외에도 이 기능을 활용하기 위해 UDF 또는 쿼리를 변경해야 하는 다른 변경 사항은 없습니다.

호환성 수준을 변경하지 않고 스칼라 UDF 인라인 사용 안 함

데이터베이스 호환성 수준 150 이상을 기본 동안 데이터베이스, 문 또는 UDF 범위에서 스칼라 UDF 인라인을 사용하지 않도록 설정할 수 있습니다. 데이터베이스 범위에서 스칼라 UDF 인라인을 사용하지 않도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 다음 문을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

데이터베이스에 스칼라 UDF 인라인을 다시 사용하도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 다음 문을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

ON이면 이 설정이 .에서 sys.database_scoped_configurations활성화된 것으로 표시됩니다. 쿼리 힌트로 USE HINT 지정하여 특정 쿼리에 대해 스칼라 UDF 인라인을 DISABLE_TSQL_SCALAR_UDF_INLINING 사용하지 않도록 설정할 수도 있습니다.

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 호환성 수준 설정보다 우선합니다.

예시:

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
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

또는 ALTER FUNCTION 문의 INLINE 절 CREATE FUNCTION 을 사용하여 특정 UDF에 대해 스칼라 UDF 인라인을 사용하지 않도록 설정할 수도 있습니다. 예시:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END;

위의 문이 실행되면 이 UDF는 해당 문을 호출하는 쿼리에 인라인되지 않습니다. 이 UDF에 인라인을 다시 사용하도록 설정하려면 다음 문을 실행합니다.

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

절은 INLINE 필수가 아닙니다. 절을 지정하지 않으면 INLINE UDF를 인라인 처리할 ON/OFF 수 있는지 여부에 따라 자동으로 설정됩니다. 지정되었지만 UDF가 인라인 처리할 수 없는 경우 INLINE = ON 오류가 throw됩니다.

중요 참고 사항

이 문서에 설명된 대로 스칼라 UDF 인라인 처리는 스칼라 UDF가 있는 쿼리를 동등한 스칼라 하위 쿼리가 있는 쿼리로 변환합니다. 이 변환으로 인해 사용자는 다음 시나리오에서 몇 가지 동작 차이를 확인할 수 있습니다.

인라인 처리는 동일한 쿼리 텍스트에 대해 다른 쿼리 해시를 생성합니다.
이전에 숨겨졌을 수 있는 UDF 내 문(예: 0으로 나누기 등)의 특정 경고는 인라인 처리로 인해 표시될 수 있습니다.
인라인 처리에서 새 조인을 도입할 수 있으므로 쿼리 수준 조인 힌트가 더 이상 유효하지 않을 수 있습니다. 로컬 조인 힌트를 대신 사용해야 합니다.
인라인 스칼라 UDF를 참조하는 뷰는 인덱싱할 수 없습니다. 이러한 뷰에서 인덱스를 만들어야 하는 경우 참조된 UDF에 대한 인라인을 사용하지 않도록 설정합니다.
UDF 인라인 처리로 동적 데이터 마스킹의 동작에 몇 가지 차이가 있을 수 있습니다.

특정 상황에서(UDF의 논리에 따라) 출력 열 마스킹과 관련하여 인라인 처리가 더 보수적일 수 있습니다. UDF에서 참조되는 열이 출력 열이 아닌 시나리오에서는 마스킹되지 않습니다.
UDF가 기본 제공 함수(예: SCOPE_IDENTITY(), @@ROWCOUNT 또는 @@ERROR)를 참조할 경우 기본 제공 함수에서 반환한 값이 인라인 처리에 따라 변경됩니다. 이 동작 변경은 인라인 처리가 UDF 내 문 범위를 변경하기 때문입니다. SQL Server 2019(15.x) CU2부터 UDF가 특정 내장 함수(예 @@ROWCOUNT: )를 참조하는 경우 인라인 처리가 차단됩니다.
인라인된 UDF의 결과로 변수가 할당되고 FORCESEEK 쿼리 힌트에서 index_column_name 사용되는 경우 쿼리 프로세서가 쿼리에 정의된 힌트 때문에 쿼리 계획을 생성할 수 없음을 나타내는 오류 Msg 8622가 발생합니다.