스칼라 UDF 인라인 처리
적용 대상: 
SQL Server 2019(15.x) Azure SQL 데이터베이스 Azure SQL Managed Instance
이 문서에서는 SQL Database의 지능형 쿼리 처리 기능 집합에 속하는 기능인 스칼라 UDF 인라인 처리에 대해 소개합니다. 이 기능은 SQL Server 2019(15.x) 이상 버전에서 스칼라 UDF를 호출하는 쿼리의 성능을 개선합니다.
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와 같은 효과는 계획에서 관찰됩니다.
예제
이 섹션의 예제에서는 TPC-H 벤치마크 데이터베이스를 사용합니다. 자세한 내용은 TPC-H 홈페이지를 참조하세요.
A. 단일 문 스칼라 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초 |
이 수치는 듀얼 프로세서(12코어), 96GB RAM, SSD가 지원되는 시스템에서 실행되는 10GB CCI 데이터베이스(TPC-H 스키마 사용)를 기준으로 합니다. 이 수치에는 콜드 프로시저 캐시 및 버퍼 풀을 사용한 컴파일 및 실행 시간이 포함됩니다. 기본 구성이 사용되었으며 다른 인덱스는 생성되지 않았습니다.
B. 다중 문 스칼라 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 AS DECIMAL (18, 2);
DECLARE @category AS 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 이하)에서 이 쿼리의 실행 계획은 다음과 같습니다.
계획에서 보듯이 SQL Server는 간단한 전략을 택하고 있습니다. 즉 CUSTOMER 테이블의 모든 튜플에 대해 UDF를 호출하고 결과를 출력합니다. 이 전략은 단순하고 비효율적입니다. 인라인 처리가 있으면 이런 UDF가 해당하는 스칼라 하위 쿼리로 변환되며 호출하는 쿼리에서 UDF 대신 대체됩니다.
동일한 쿼리에 대해 UDF가 인라인 처리된 계획은 다음과 같습니다.
앞의 설명과 같이, 쿼리 계획에는 더 이상 사용자 정의 함수 연산자가 없지만 이제 뷰 또는 인라인 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 쿼리1IF/ELSE: 임의의 수준의 중첩을 사용한 분기.RETURN: 단일 또는 다중 반환 문. SQL Server 2019(15.x) CU5부터 UDF에는 6 인라이닝에 고려할 단일 RETURN 문만 포함할 수 있습니다.UDF: 중첩/재귀 함수 호출2.- 기타: 관계형 작업(예:
EXISTS,ISNULL``).
- UDF는 시간에 종속적(예:
GETDATE())이거나 부정적인 영향3(예:NEWSEQUENTIALID())이 있는 내재 함수를 호출하지 않습니다. - UDF가
EXECUTE AS CALLER절을 사용합니다(EXECUTE AS절이 지정되지 않은 경우 기본 동작). - UDF가 테이블 변수 또는 테이블 값 매개 변수를 참조하지 않습니다.
- 스칼라 UDF를 호출하는 쿼리가
GROUP BY절에서 스칼라 UDF 호출을 참조하지 않습니다. - 선택 목록에서
DISTINCT절을 사용하여 스칼라 UDF를 호출하는 쿼리에는ORDER BY절이 없습니다. - UDF는
ORDER BY절에 사용되지 않습니다. - UDF는 기본적으로 컴파일되지 않았습니다(상호 운용이 지원됨).
- 계산된 열 또는 검사 제약 조건 정의에서는 UDF가 사용되지 않습니다.
- UDF는 사용자 정의 형식을 참조하지 않습니다.
- UDF에 추가된 서명이 없습니다.
- UDF가 파티션 함수가 아닙니다.
- UDF에 CTE(공용 테이블 식)에 대한 참조가 포함되어 있지 않습니다.
- UDF에는 인라인 처리(예:
@@ROWCOUNT) 시 결과를 변경할 수 있는 내재 함수에 대한 참조가 포함되어 있지 않습니다4. - UDF에 스칼라 UDF4에 매개 변수로 전달되는 집계 함수가 포함되어 있지 않습니다.
- UDF가 내장 뷰(예:
OBJECT_ID)를 참조하지 않습니다.4 - UDF가 XML 메서드를 참조하지 않습니다5.
- UDF에
ORDER BY절이 없는TOP 1가 포함된 SELECT가 포함되어 있지 않습니다5. - UDF에
ORDER BY절(예:SELECT @x = @x + 1 FROM table1 ORDER BY col1)로 할당을 수행하는 SELECT 쿼리가 포함되어 있지 않습니다5. - UDF에 RETURN 문이 여러 개 포함되어 있지 않습니다6.
- UDF가 RETURN 문에서 호출되지 않습니다6.
- UDF가
STRING_AGG함수를 참조하지 않습니다6. - UDF가 원격 테이블을 참조하지 않습니다7.
- UDF 호출 쿼리가
GROUPING SETS,CUBE또는ROLLUP을 사용하지 않습니다7. - UDF 호출 쿼리에는 할당에 대한 UDF 매개 변수로 사용되는 변수(예:
SELECT @y = 2,@x = UDF(@y))가 포함되어 있지 않습니다7. - UDF는 암호화된 열을 참조하지 않습니다8.
- UDF에는
WITH XMLNAMESPACES에 대한 참조가 포함되어 있지 않습니다8. - UDF를 호출하는 쿼리에는 CTE(공용 테이블 식)이 없습니다8.
1 변수 누적/집계가 있는 SELECT는 인라인 처리(예: SELECT @val += col1 FROM table1)에 대해 지원되지 않습니다.
2 재귀 UDF는 특정 깊이에만 인라인 처리됩니다.
3 현재 시스템 시간에 따라 결과가 달라지는 내장 함수는 시간 종속적입니다. 일부 내부 전역 상태를 업데이트할 수 있는 내장 함수는 부작용이 있는 함수의 한 예입니다. 이러한 함수는 내부 상태에 따라 호출할 때마다 서로 다른 결과를 반환합니다.
4 SQL Server 2019(15.x) CU2에 추가된 제한 사항
5 SQL Server 2019(15.x) CU4에 추가된 제한 사항
6 SQL Server 2019(15.x) CU5에 추가된 제한 사항
7 SQL Server 2019(15.x) CU6에 추가된 제한 사항
8 SQL Server 2019(15.x) CU11에 추가된 제한 사항
최신 T-SQL Scalar UDF 인라인 처리 수정 및 인라인 처리 적격 시나리오에 관한 내용은 기술 자료 문서 해결 방법: 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는 인라인 처리 안 함을 선택할 수 있습니다.
GROUP BY절의 UDF 호출은 인라인 처리되지 않습니다. 이 결정은 스칼라 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에 대한
<UserDefinedFunction>XML 노드가 없습니다. - 특정 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에서 이 설정이 enabled로 표시됩니다.
DISABLE_TSQL_SCALAR_UDF_INLINING을 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대해 스칼라 UDF 인라인 처리를 사용하지 않게 설정할 수도 있습니다.
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'));
CREATE FUNCTION 또는 ALTER FUNCTION 문의 INLINE 절을 사용하여 특정 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로 자동으로 설정됩니다. INLINE = ON이 지정되었지만 UDF를 인라인 처리할 수 없는 것으로 확인되면 오류가 발생합니다.
설명
이 문서에 설명된 대로 스칼라 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 사용되는 경우 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없음을 나타내는 오류 메시지 8622가 발생합니다.