Skalär UDF-inlining

Artikel
01/31/2025

gäller för: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

Den här artikeln introducerar skalär UDF-inlining, en funktion under Intelligent frågebearbetning i SQL-databaser uppsättning funktioner. Den här funktionen förbättrar prestandan för frågor som anropar skalära UDF:er i SQL Server 2019 (15.x) och senare versioner.

Användardefinierade funktioner för T-SQL-skalär

User-Defined Functions (UDF: er) som implementeras i Transact-SQL och returnerar ett enda datavärde kallas T-SQL Scalar User-Defined Functions. T-SQL UDF:er är ett elegant sätt att uppnå återanvändning av kod och modularitet i Transact-SQL frågor. Vissa beräkningar (till exempel komplexa affärsregler) är lättare att uttrycka i imperativ UDF-form. UDF:er hjälper dig att skapa komplex logik utan att behöva expertis för att skriva komplexa SQL-frågor. Mer information om UDF:er finns i Skapa användardefinierade funktioner (databasmotor).

Prestanda för skalära UDF:er

Skalära UDF:er presterar vanligtvis dåligt på grund av följande orsaker:

Iterativt anrop. UDF:er anropas på ett iterativt sätt, en gång per kvalificerande tuppeln. Detta medför en extra kostnad för upprepade kontextväxlingar på grund av funktionsanrop. I synnerhet påverkas UDF:er som kör Transact-SQL frågor i definitionen påtagligt.
Brist på kostnader. Under optimering beräknas endast kostnader för relationsoperatorer, medan skalära operatorer inte beräknas. Före introduktionen av skalära UDF:er var andra skalära operatörer vanligtvis billiga och krävde inte kostnadsberäkning. En liten ytterligare processorkostnad för en skalär beräkning var tillräcklig. Det finns scenarier där den faktiska kostnaden är betydande och ändå fortfarande är underrepresenterad.
Tolkad körning. UDF:er utvärderas som en batch med instruktioner som körs instruktion för instruktion. Varje instruktion kompileras och den kompilerade planen cachelagras. Även om den här cachelagringsstrategin sparar lite tid eftersom den undviker omkompileringar, körs varje instruktion isolerat. Inga optimeringar utförs mellan satser.
Seriell körning. SQL Server tillåter inte parallellitet mellan frågor i frågor som anropar UDF:er.

Automatisk inlining av skalära UDF:er

Målet med den skalära UDF-inliningsfunktionen är att förbättra prestandan för frågor som anropar T-SQL-skalär-UDF:er, där UDF-körning är huvudflaskhalsen.

Med den här nya funktionen omvandlas skalära UDF:er automatiskt till skalära uttryck eller skalära underfrågor som ersätts i den anropande frågan i stället för UDF-operatorn. Dessa uttryck och underfrågor optimeras sedan. Därför har frågeplanen inte längre en användardefinierad funktionsoperator, men dess effekter observeras i planen, till exempel vyer eller infogade tabellvärdesfunktioner (TVF:er).

Exempel

Exemplen i det här avsnittet använder TPC-H benchmark-databasen. Mer information finns på startsidan för TPC-H.

A. Skalbar UDF med en instruktion

Överväg följande fråga.

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;

Den här frågan beräknar summan av rabatterade priser för radartiklar och visar resultatet grupperat efter leveransdatum och leveransprioritet. Uttrycket L_EXTENDEDPRICE *(1 - L_DISCOUNT) är formeln för det rabatterade priset för ett visst radobjekt. Sådana formler kan extraheras till funktioner till förmån för modularitet och återanvändning.

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

Nu kan frågan ändras för att anropa denna 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;

Frågningen med UDF funkar dåligt på grund av anledningarna som beskrevs tidigare. Med skalär UDF-inlining ersätts det skalära uttrycket i UDF-brödtexten direkt i frågan. Resultatet av att köra den här frågan visas i följande tabell:

Fråga:	Fråga utan UDF	Fråga med UDF (utan inlining)	Fråga med skalär UDF-inlining
Körtid	1,6 sekunder	29 minuter och 11 sekunder	1,6 sekunder

Dessa siffror baseras på en CCI-databas på 10 GB (med hjälp av TPC-H-schemat), som körs på en dator med dubbla processorer (12 kärnor), 96 GB RAM-minne som backas upp av SSD. Siffrorna inkluderar kompilerings- och körningstid med en kall procedurcache och buffertpool. Standardkonfigurationen användes och inga andra index skapades.

B. Skalbar UDF med flera instruktioner

Skalära UDF:er som implementeras med flera T-SQL-instruktioner, till exempel variabeltilldelningar och villkorsstyrd förgrening, kan också infogas. Betrakta följande skalära UDF som, givet en kundnyckel, bestämmer tjänstkategorin för den kunden. Den anländer till kategorin genom att först beräkna det totala priset för alla beställningar som kunden har gjort med hjälp av en SQL-fråga. Sedan använder den en IF (...) ELSE logik för att bestämma kategorin baserat på det totala priset.

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

Tänk nu på en förfrågan som anropar den här UDF:en.

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

Körningsplanen för den här frågan i SQL Server 2017 (14.x) (kompatibilitetsnivå 140 och tidigare) är följande:

Som planen visar antar SQL Server en enkel strategi här: för varje tuppel i tabellen CUSTOMER, anropar UDF och matar ut resultatet. Denna strategi är naiv och ineffektiv. Med inlining omvandlas sådana UDF:er till motsvarande skalärunderfrågor, som ersätts i den anropande frågan i stället för UDF.

För samma fråga ser planen med UDF integrerad ut på följande sätt.

Som tidigare nämnts har frågeplanen inte längre en användardefinierad funktionsoperator, men dess effekter kan nu observeras i planen, till exempel vyer eller infogade TVF:er. Här följer några viktiga observationer från föregående plan:

SQL Server härleder den implicita kopplingen mellan CUSTOMER och ORDERS och gör den explicit via en kopplingsoperator.
SQL Server härleder även implicita GROUP BY O_CUSTKEY on ORDERS och använder IndexSpool + StreamAggregate för att implementera den.
SQL Server använder nu parallellitet mellan alla operatorer.

Beroende på logikens komplexitet i UDF kan den resulterande frågeplanen också bli större och mer komplex. Som vi kan se är operationerna i UDF nu inte längre ogenomskinliga, vilket gör att frågeoptimeraren kan beräkna och optimera dessa operationer. Också, eftersom UDF inte längre finns i planen, ersätts iterativt UDF-anrop av en plan som helt undviker funktionanrop.

Inlinjerbara skalära UDF-krav

En skalär T-SQL UDF kan infogas om funktionsdefinitionen använder tillåtna konstruktioner och funktionen används i en kontext som möjliggör inlinning:

Alla följande villkor i UDF-definition måste vara sanna:

UDF skrivs med hjälp av följande konstruktioner:
- DECLARE, SET: Variabeldeklaration och tilldelningar.
- SELECT: SQL-fråga med en/flera variabeltilldelningar ¹.
- IF / ELSE: Förgrening med godtyckliga kapslingsnivåer.
- RETURN: Enkla eller flera returinstruktioner. Från och med SQL Server 2019 (15.x) CU5 kan UDF endast innehålla en enda RETURN-instruktion som ska beaktas för att ange ⁶.
- UDF: Kapslade/rekursiva funktionsanrop ².
- Övriga: Relationella operationer som EXISTS, IS NULL.
UDF anropar inte någon inbyggd funktion som antingen är tidsberoende (till exempel GETDATE()) eller har biverkningar ³ (till exempel NEWSEQUENTIALID()).
UDF använder EXECUTE AS CALLER-satsen (standardbeteende om EXECUTE AS-satsen inte har angetts).
UDF refererar inte till tabellvariabler eller tabellvärdesparametrar.
UDF:n kompileras inte internt (interop stöds).
UDF refererar inte till användardefinierade typer.
Inga signaturer har lagts till i UDF ⁹.
UDF är inte en partitionsfunktion.
UDF innehåller inte referenser till Common Table Expressions (CTE).
UDF innehåller inte referenser till inbyggda funktioner som kan ändra resultatet när de infogas (till exempel @@ROWCOUNT) ⁴.
UDF innehåller inte mängdfunktioner som skickas som parametrar till en skalär UDF-⁴.
UDF refererar inte till inbyggda vyer (till exempel OBJECT_ID) ⁴.
UDF refererar inte till XML-metoder ⁵.
UDF innehåller inte en SELECT med ORDER BY utan en TOP 1-sats ⁵.
UDF innehåller inte en SELECT-fråga som utför en tilldelning med ORDER BY-satsen (till exempel SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
UDF innehåller inte flera RETURN-instruktioner ⁶.
UDF refererar inte till funktionen STRING_AGG⁶.
UDF refererar inte till fjärrtabeller ⁷.
UDF refererar inte till krypterade kolumner ⁸.
UDF innehåller inte referenser till WITH XMLNAMESPACES⁸.
Om UDF-definitionen består av tusentals kodrader kan det hända att SQL Server väljer att inte infoga den.

¹SELECT med variabel ackumulering/aggregering stöds inte för inlinning (till exempel SELECT @val += col1 FROM table1).

² Rekursiva UDF:er är endast inlinade på ett visst djup.

³ Inbyggda funktioner vars resultat är beroende av den aktuella systemtiden är tidsberoende. En inbyggd funktion som kan uppdatera ett internt globalt tillstånd är ett exempel på en funktion med biverkningar. Sådana funktioner returnerar olika resultat varje gång de anropas, baserat på det interna tillståndet.

⁴ begränsning som lagts till i SQL Server 2019 (15.x) CU 2

⁵ en begränsning som lagts till i SQL Server 2019 (15.x) CU 4

⁶ en begränsning som har lagts till i SQL Server 2019 (15.x) CU 5

⁷ begränsning tillagd i SQL Server 2019 (15.x) CU 6

⁸ begränsning som lagts till i SQL Server 2019 (15.x) CU 11

⁹ Eftersom signaturer kan läggas till och tas bort efter att en UDF har skapats, görs beslutet att infoga när frågan som refererar till en skalär UDF kompileras. Systemfunktioner signeras till exempel vanligtvis med ett certifikat. Du kan använda sys.crypt_properties för att hitta vilka objekt som är signerade.

Alla följande krav i exekveringskontexten måste uppfyllas.

UDF används inte i ORDER BY-satsen.
Frågan som anropar en skalär UDF refererar inte till ett skalärt UDF-anrop i dess GROUP BY-sats.
Den fråga som anropar en skalär UDF i sin SELECT-lista med DISTINCT-satsen innehåller ingen ORDER BY-sats.
UDF anropas inte från en RETURN-instruktion. ¹
Frågan som anropar UDF har inte vanliga tabelluttryck (CTE) ³.
Frågan som anropar UDF:en använder inte GROUPING SETS, CUBEeller ROLLUP².
Den UDF-anropande frågan innehåller inte en variabel som används som en UDF-parameter för tilldelning (till exempel SELECT @y = 2, @x = UDF(@y)) ².
UDF används inte i en beräknad kolumn eller en kontrollvillkorsdefinition.

¹ begränsning som lagts till i SQL Server 2019 (15.x) CU 5

² begränsning som lagts till i SQL Server 2019 (15.x) CU 6

³ begränsning som lagts till i SQL Server 2019 (15.x) CU 11

Information om de senaste T-SQL-skalära UDF-inliningskorrigeringarna och ändringar i berättigandescenarier för inlining finns i kunskapsbasartikeln: FIX: scalar UDF inlining issues in SQL Server 2019.

Kontrollera om en UDF kan infogas

För varje T-SQL-skalär UDF innehåller sys.sql_modules katalogvyn en egenskap som heter is_inlineable, som anger om en UDF är inlinebar.

Egenskapen is_inlineable härleds från konstruktionerna som finns i UDF-definitionen. Det kontrollerar inte om UDF i själva verket är inlineable vid kompileringstillfället. Mer information finns i villkor för att ange.

Värdet 1 anger att UDF:en kan inlinas, och 0 anger motsatsen. Den här egenskapen har värdet 1 även för alla inline-TVF:er. För alla andra moduler är värdet 0.

Om en skalbar UDF är infogad innebär det inte att den alltid är inlindad. SQL Server bestämmer (per fråga, per UDF-basis) om en UDF ska infogas. Se listan med krav tidigare i den här artikeln.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Kontrollera om inlinning har inträffat

Om alla förhandsvillkor uppfylls och SQL Server bestämmer sig för att utföra inlinning omvandlas UDF till ett relationsuttryck. Från frågeplanen kan du ta reda på om inlining inträffade.

Plan-XML har ingen <UserDefinedFunction> XML-nod för en UDF som är infogad framgångsrikt.
Vissa utökade händelser genereras.

Aktivera inlining av skalära UDF:er

Du kan göra arbetsbelastningar automatiskt berättigade till skalär UDF-inlining genom att aktivera kompatibilitetsnivå 150 för databasen. Du kan ange detta med Transact-SQL. Till exempel:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Förutom det här steget krävs inga andra ändringar i UDF:er eller frågor för att dra nytta av den här funktionen.

Inaktivera skalär UDF-inlining utan att ändra kompatibilitetsnivån

Skalär UDF-inlining kan inaktiveras i databasen, instruktionen eller UDF-omfånget samtidigt som databaskompatibilitetsnivån 150 och högre bibehålls. Om du vill inaktivera skalär UDF-inlining i databasomfånget kör du följande instruktion i kontexten för den tillämpliga databasen:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Om du vill återaktivera skalär UDF-inlining för databasen kör du följande instruktion i kontexten för den tillämpliga databasen:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

När ONvisas den här inställningen som aktiverad i sys.database_scoped_configurations.

Du kan också inaktivera skalär UDF-inlining för en specifik fråga genom att ange DISABLE_TSQL_SCALAR_UDF_INLINING som ett USE HINT frågetips.

Ett USE HINT frågetips har företräde framför inställningen för databasomfattningskonfiguration eller kompatibilitetsnivå.

Till exempel:

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'));

Skalär UDF-inlining kan också inaktiveras för en specifik UDF med INLINE-klausulen i CREATE FUNCTION- eller ALTER FUNCTION-satsen. Till exempel:

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

När den föregående instruktionen har körts kommer denna UDF aldrig att inlineras i någon fråga som anropar den. Kör följande kommando för att återaktivera inlinning för denna 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

Satsen INLINE är inte obligatorisk. Om INLINE-satsen inte specificeras, ställs den automatiskt in på ON/OFF beroende på om UDF kan infogas. Om INLINE = ON är specificerat men UDF inte är lämpligt för inlining, kastas ett fel.

Anmärkningar

Som beskrivs i den här artikeln omvandlar skalär UDF-inlining en fråga med skalära UDF:er till en fråga med motsvarande skalär underfråga. På grund av den här omvandlingen kan du märka några skillnader i beteendet i följande scenarier:

Inlining resulterar i en annan frågehash för samma frågetext.
Vissa varningar i instruktioner i UDF (till exempel dividera med noll, osv.) som tidigare kan ha varit dolda, kan visas på grund av inlining.
Tips om koppling på frågenivå kanske inte längre är giltiga, eftersom inlining kan introducera nya kopplingar. Lokala join-anvisningar måste användas i stället.
Vyer som refererar till infogade skalära UDF:er kan inte indexeras. Om du behöver skapa ett index för sådana vyer, inaktivera inlining för UDF:erna som refereras.
Det kan finnas vissa skillnader i beteendet för Dynamisk datamaskering med UDF-inlinning.

I vissa situationer (beroende på logiken i UDF) kan inlinning vara mer konservativ när det gäller maskering av utdatakolumner. I scenarier där kolumnerna som refereras i en UDF inte är utdatakolumner maskeras de inte.
Om en UDF refererar till inbyggda funktioner som SCOPE_IDENTITY(), @@ROWCOUNTeller @@ERROR, ändras värdet som returneras av den inbyggda funktionen vid direktinförande. Den här beteendeändringen beror på att inlining ändrar omfånget för uttryck i UDF. Från och med SQL Server 2019 (15.x) CU2 blockeras inlinning om UDF refererar till vissa inbyggda funktioner (till exempel @@ROWCOUNT).
Om en variabel tilldelas resultatet av en inlined UDF och den även används som index_column_name i FORCESEEKFrågetipsresulterar det i fel 8622, vilket indikerar att frågeprocessorn inte kunde skapa en frågeplan på grund av de tips som definierats i frågan.

Dela via

Skalär UDF-inlining

Användardefinierade funktioner för T-SQL-skalär

Prestanda för skalära UDF:er

Automatisk inlining av skalära UDF:er

Exempel

A. Skalbar UDF med en instruktion

B. Skalbar UDF med flera instruktioner

Inlinjerbara skalära UDF-krav

Kontrollera om en UDF kan infogas

Kontrollera om inlinning har inträffat

Aktivera inlining av skalära UDF:er

Inaktivera skalär UDF-inlining utan att ändra kompatibilitetsnivån

Anmärkningar

Feedback

Ytterligare resurser

Dela via

Skalär UDF-inlining

Användardefinierade funktioner för T-SQL-skalär

Prestanda för skalära UDF:er

Automatisk inlining av skalära UDF:er

Exempel

A. Skalbar UDF med en instruktion

B. Skalbar UDF med flera instruktioner

Inlinjerbara skalära UDF-krav

Kontrollera om en UDF kan infogas

Kontrollera om inlinning har inträffat

Aktivera inlining av skalära UDF:er

Inaktivera skalär UDF-inlining utan att ändra kompatibilitetsnivån

Anmärkningar

Relaterat innehåll

Feedback

Ytterligare resurser