Scalaire UDF-inlining

van toepassing op: SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed Instance

In dit artikel maakt u kennis met scalaire UDF-functies, een functie onder de Intelligente queryverwerking in SQL-databases suite met functies. Deze functie verbetert de prestaties van query's die scalaire UDF's aanroepen in SQL Server 2019 (15.x) en latere versies.

Door de gebruiker gedefinieerde T-SQL scalaire functies

User-Defined Functions (UDF's) die zijn geïmplementeerd in Transact-SQL en één gegevenswaarde retourneren, worden T-SQL Scalar User-Defined Functions genoemd. T-SQL UDF's zijn een elegante manier om code opnieuw te gebruiken en modulariteit te realiseren in Transact-SQL query's. Sommige berekeningen (zoals complexe bedrijfsregels) zijn eenvoudiger uit te drukken in imperatieve UDF-vorm. UDF's helpen bij het opbouwen van complexe logica zonder dat u expertise nodig hebt bij het schrijven van complexe SQL-query's. Zie Door de gebruiker gedefinieerde functies (Database Engine) makenvoor meer informatie over UDF's.

Prestaties van scalaire UDF's

Scalaire UDF's presteren meestal slecht vanwege de volgende redenen:

• Iteratieve aanroep. UDFs worden op iteratieve wijze aangeroepen, eenmaal per kwalificeerde tuple. Hierdoor worden extra kosten in rekening gebracht voor herhaalde contextwisselingen vanwege functieaanroepen. Met name UDF's die Transact-SQL query's in hun definitie uitvoeren, worden ernstig beïnvloed.

• Gebrek aan kosten. Tijdens optimalisatie worden alleen relationele operators gefactureerd, terwijl scalaire operators dat niet zijn. Vóór de introductie van scalaire UDF's waren andere scalaire operators over het algemeen goedkoop en hadden ze geen kosten nodig. Een kleine CPU-kost voor een scalaire bewerking was voldoende. Er zijn scenario's waarin de werkelijke kosten aanzienlijk zijn en toch nog steeds ondervertegenwoordigd blijven.

• Geïnterpreteerde uitvoering. UDF's worden geëvalueerd als een batch met instructies, uitgevoerde instructie per instructie. Elke instructie zelf wordt gecompileerd en het gecompileerde plan wordt in de cache opgeslagen. Hoewel deze cachingstrategie enige tijd bespaart omdat het hercompilaties vermijdt, wordt elke instructie geïsoleerd uitgevoerd. Er worden geen optimalisaties tussen statements uitgevoerd.

• Seriële uitvoering. SQL Server staat geen intra-query parallellisme toe in query's die UDF's aanroepen.

Automatisch inlijnen van scalaire UDF's

Het doel van de scalaire UDF-inliningfunctie is om de prestaties van query's te verbeteren die T-SQL scalaire UDF's aanroepen, waarbij UDF-uitvoering het belangrijkste knelpunt is.

Met deze nieuwe functie worden scalaire UDF's automatisch omgezet in scalaire expressies of scalaire subquery's die worden vervangen door de aanroepende query in plaats van de UDF-operator. Deze expressies en subquery's worden vervolgens geoptimaliseerd. Als gevolg hiervan heeft het queryplan geen door de gebruiker gedefinieerde functieoperator meer, maar worden de effecten ervan waargenomen in het plan, zoals weergaven of inline-functies met tabelwaarden (TVF's).

Voorbeelden

In de voorbeelden in deze sectie wordt de TPC-H benchmarkdatabase gebruikt. Zie de TPC-H Homepagevoor meer informatie.

Een. Scalaire UDF met één uitdrukking

Houd rekening met de volgende query.

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;

Met deze query wordt de som berekend van kortingsprijzen voor regelitems en worden de resultaten gegroepeerd op de verzenddatum en verzendprioriteit weergegeven. De expressie L_EXTENDEDPRICE *(1 - L_DISCOUNT) is de formule voor de kortingsprijs voor een bepaald regelitem. Dergelijke formules kunnen worden geëxtraheerd in functies ten behoeve van modulariteit en hergebruik.

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

De query kan nu worden gewijzigd om deze UDF aan te roepen.

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;

De query met de UDF presteert slecht vanwege de redenen die eerder zijn beschreven. Met scalaire UDF-inlining wordt de scalaire expressie in de hoofdtekst van de UDF rechtstreeks in de query vervangen. De resultaten van het uitvoeren van deze query worden weergegeven in de volgende tabel:

Vraag:	Query uitvoeren zonder UDF	Query uitvoeren met UDF (zonder inlining)	Query uitvoeren met scalaire UDF-inlining
Uitvoeringstijd:	1,6 seconden	29 minuten 11 seconden	1,6 seconden

Deze getallen zijn gebaseerd op een CCI-database van 10 GB (met behulp van het TPC-H schema), die wordt uitgevoerd op een computer met dubbele processor (12 kernen), 96 GB RAM, ondersteund door SSD. De getallen omvatten compilatie- en uitvoeringstijd met een koude procedurecache en buffergroep. De standaardconfiguratie is gebruikt en er zijn geen andere indexen gemaakt.

B. Scalaire UDF met meerdere instructies

Scalaire UDF's die zijn geïmplementeerd met behulp van meerdere T-SQL-instructies, zoals variabeletoewijzingen en voorwaardelijke vertakking, kunnen ook inline worden geplaatst. Houd rekening met de volgende scalaire UDF die, gezien een klantsleutel, de servicecategorie voor die klant bepaalt. Het komt bij de categorie aan door eerst de totale prijs te berekenen van alle orders die door de klant zijn geplaatst met behulp van een SQL-query. Vervolgens wordt een IF (...) ELSE logica gebruikt om de categorie te bepalen op basis van de totale prijs.

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

Overweeg nu een query die deze UDF aanroept.

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

Het uitvoeringsplan voor deze query in SQL Server 2017 (14.x) (compatibiliteitsniveau 140 en eerder) is als volgt:

Zoals het plan laat zien, gebruikt SQL Server hier een eenvoudige strategie: voor elke tuple in de CUSTOMER tabel roept u de UDF aan en voert u de resultaten uit. Deze strategie is naïef en inefficiënt. Met inlining worden dergelijke UDF's omgezet in equivalente scalaire subquery's, die worden ingezet ter vervanging van de UDF in de aanroepende query.

Voor dezelfde query ziet het plan met de inlined UDF er als volgt uit.

Zoals eerder vermeld, beschikt het queryplan niet langer over een gebruikersgedefinieerde functie-operator, maar zijn de effecten nu waarneembaar in het plan, vergelijkbaar met views of inline TVF's. Hier volgen enkele belangrijke opmerkingen uit het vorige plan:

• SQL Server leidt de impliciete koppeling tussen CUSTOMER en ORDERS af en maakt dit expliciet via een koppeloperator.

• SQL Server leidt ook de impliciete GROUP BY O_CUSTKEY on ORDERS af en gebruikt IndexSpool + StreamAggregate om de implementatie uit te voeren.

• SQL Server maakt nu gebruik van parallelle uitvoering voor alle operators.

Afhankelijk van de complexiteit van de logica in de UDF, kan het resulterende queryplan ook groter en complexer worden. Zoals we kunnen zien, zijn de bewerkingen in de UDF nu niet meer ondoorzichtig en kan de queryoptimalisatie deze bewerkingen dus kosten en optimaliseren. Omdat de UDF zich niet meer in het plan bevindt, wordt de iteratieve UDF-aanroep vervangen door een plan dat de overhead van functieoproepen volledig elimineert.

Inlineable scalaire UDF-vereisten

Een scalaire T-SQL UDF kan inline worden geplaatst als de functiedefinitie toegestane constructies gebruikt en de functie wordt gebruikt in een context die inlining mogelijk maakt:

Alle volgende voorwaarden van de UDF-definitie moeten waar zijn:

• De UDF wordt geschreven met behulp van de volgende constructies:
  • DECLARE, SET: Variabelen declaratie en toewijzingen.
  • SELECT: SQL-query met toewijzingen van één/meerdere variabelen ¹.
  • IF / ELSE: Vertakking met willekeurige niveaus van nesting.
  • RETURN: Enkele of meerdere return statements. Vanaf SQL Server 2019 (15.x) CU5 kan de UDF slechts één RETURN-instructie bevatten die moet worden overwogen voor het inlijnen van ⁶.
  • UDF: Geneste/recursieve functie-aanroepen ².
  • Andere: Relationele bewerkingen zoals EXISTS, IS NULL.
• De UDF roept geen intrinsieke functie aan die tijdafhankelijk is (zoals GETDATE()) of bijwerkingen heeft ³ (zoals NEWSEQUENTIALID()).
• De UDF maakt gebruik van de EXECUTE AS CALLER component (standaardgedrag als de EXECUTE AS component niet is opgegeven).
• De UDF verwijst niet naar tabelvariabelen of parameters met tabelwaarden.
• De UDF is niet systeemeigen gecompileerd (interop wordt ondersteund).
• De UDF verwijst niet naar door de gebruiker gedefinieerde typen.
• Er zijn geen handtekeningen toegevoegd aan de UDF ⁹.
• De UDF is geen partitiefunctie.
• De UDF bevat geen verwijzingen naar Common Table Expressions (CTE's).
• De UDF bevat geen verwijzingen naar intrinsieke functies die de resultaten kunnen wijzigen wanneer ze inline zijn (zoals @@ROWCOUNT) ⁴.
• De UDF bevat geen statistische functies die als parameters worden doorgegeven aan een scalaire UDF ⁴.
• De UDF verwijst niet naar ingebouwde weergaven (zoals OBJECT_ID) ⁴.
• De UDF verwijst niet naar XML-methoden ⁵.
• De UDF bevat geen SELECT met ORDER BY zonder een TOP 1 component ⁵.
• De UDF bevat geen SELECT-query waarmee een toewijzing wordt uitgevoerd met de ORDER BY component (zoals SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
• De UDF bevat niet meerdere RETURN-verklaringen ⁶.
• De UDF verwijst niet naar de functie STRING_AGG⁶.
• De UDF verwijst niet naar externe tabellen ⁷.
• De UDF verwijst niet naar versleutelde kolommen ⁸.
• De UDF bevat geen verwijzingen naar WITH XMLNAMESPACES⁸.
• Als de UDF-definitie duizenden regels code bevat, kan SQL Server ervoor kiezen om deze niet inline te zetten.

¹SELECT met variabele accumulatie/aggregatie wordt niet ondersteund voor inlineing (zoals SELECT @val += col1 FROM table1).

² recursieve UDF's worden alleen in een bepaalde diepte geplaatst.

³ Intrinsieke functies waarvan de resultaten afhankelijk zijn van de huidige systeemtijd, tijdafhankelijk zijn. Een intrinsieke functie die een interne globale status kan bijwerken, is een voorbeeld van een functie met bijwerkingen. Dergelijke functies retourneren verschillende resultaten telkens wanneer ze worden aangeroepen, op basis van de interne status.

⁴ Beperking toegevoegd in SQL Server 2019 (15.x) CU 2

⁵ Beperking toegevoegd in SQL Server 2019 (15.x) CU 4

⁶ Beperking toegevoegd in SQL Server 2019 (15.x) CU 5

⁷ Beperking toegevoegd in SQL Server 2019 (15.x) CU 6

⁸ Beperking toegevoegd in SQL Server 2019 (15.x) CU 11

⁹ Omdat handtekeningen kunnen worden toegevoegd en verwijderd nadat een UDF is gemaakt, wordt de beslissing of inline moet worden uitgevoerd wanneer de query naar een scalaire UDF verwijst. Systeemfuncties worden bijvoorbeeld meestal ondertekend met een certificaat. U kunt sys.crypt_properties gebruiken om te zoeken welke objecten zijn ondertekend.

Alle volgende vereisten van de uitvoeringscontext moeten waar zijn:

• De UDF wordt niet gebruikt in ORDER BY clausule.
• De query die een scalaire UDF aanroept, verwijst niet naar een scalaire UDF-aanroep in de GROUP BY-clausule.
• De query die een scalaire UDF aanroept in zijn selectielijst met DISTINCT clausule heeft geen ORDER BY clausule.
• De UDF wordt niet aangeroepen vanuit een RETURN-instructie ¹.
• De query die de UDF aanroept, heeft geen algemene tabelexpressies (CTE's) ³.
• De UDF-aanroepende query gebruikt geen GROUPING SETS, CUBEof ROLLUP².
• De aanroepende UDF-query bevat geen variabele die wordt gebruikt als UDF-parameter voor toewijzing (bijvoorbeeld SELECT @y = 2, @x = UDF(@y)) ².
• De UDF wordt niet gebruikt in een berekende kolom of een definitie van een controlebeperking.

¹ Beperking toegevoegd in SQL Server 2019 (15.x) CU 5

² Beperking toegevoegd in SQL Server 2019 (15.x) CU 6

³ Beperking toegevoegd in SQL Server 2019 (15.x) CU 11

Zie het Knowledge Base-artikel: FIX: scalaire UDF-inliningproblemen in SQL Server 2019voor informatie over de meest recente oplossingen en wijzigingen met betrekking tot T-SQL scalaire UDF-inlining en de geschiktheidsscenario's daarvoor.

Controleren of een UDF inline kan zijn

Voor elke scalaire T-SQL-UDF bevat de sys.sql_modules catalogusweergave een eigenschap met de naam is_inlineable, die aangeeft of een UDF inlineable is.

De eigenschap is_inlineable is afgeleid van de constructies in de UDF-definitie. Er wordt niet gecontroleerd of de UDF in feite inlineable is tijdens het compileren. Zie de voorwaarden voor het inlijnen vanvoor meer informatie.

Een waarde van 1 geeft aan dat de UDF inlineable is en 0 anders aangeeft. Deze eigenschap heeft ook een waarde van 1 voor alle inline-TVF's. Voor alle andere modules is de waarde 0.

Als een scalaire UDF inlineable is, betekent dit niet dat deze altijd inline is. SQL Server bepaalt (per query, per UDF) of een UDF inline moet worden geplaatst. Raadpleeg de lijsten met vereisten eerder in dit artikel.

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

Controleren of de inlining heeft plaatsgevonden

Als aan alle voorwaarden wordt voldaan en SQL Server besluit om inlining uit te voeren, wordt de UDF omgezet in een relationele expressie. In het queryplan kunt u achterhalen of inlining heeft plaatsgevonden.

• De XML van het plan heeft geen <UserDefinedFunction> XML-knooppunt voor een UDF die succesvol is ingelijnd.
• Bepaalde uitgebreide gebeurtenissen worden uitgezonden.

Scalaire UDF-inlining inschakelen

U kunt ervoor zorgen dat workloads automatisch in aanmerking komen voor scalaire UDF-inlining door compatibiliteitsniveau 150 in te schakelen voor de database. U kunt dit instellen met Behulp van Transact-SQL. Bijvoorbeeld:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Naast deze stap zijn er geen andere wijzigingen vereist voor UDF's of query's om te profiteren van deze functie.

Scalaire UDF-inlining uitschakelen zonder het compatibiliteitsniveau te wijzigen

Scalaire UDF-inlining kan worden uitgeschakeld op het niveau van de database, de instructies of de UDF, terwijl het compatibiliteitsniveau van de database behouden blijft op 150 en hoger. Als u scalaire UDF-inlining wilt uitschakelen voor het databasebereik, voert u de volgende instructie uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Als u scalaire UDF-inlineering voor de database wilt inschakelen, voert u de volgende instructie uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Wanneer ON, wordt deze instelling weergegeven als ingeschakeld in sys.database_scoped_configurations.

U kunt scalaire UDF-inlining voor een specifieke query ook uitschakelen door DISABLE_TSQL_SCALAR_UDF_INLINING aan te wijzen als USE HINT queryhint.

Een USE HINT query hint heeft voorrang boven de configuratie-instelling van het databasebereik of de compatibiliteitsniveau-instelling.

Bijvoorbeeld:

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

Scalaire UDF-inlining kan ook worden uitgeschakeld voor een specifieke UDF met behulp van de INLINE-component in de instructie CREATE FUNCTION of ALTER FUNCTION. Bijvoorbeeld:

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

Zodra de vorige instructie is uitgevoerd, wordt deze UDF nooit ingelijnd in een query die deze aanroept. Voer de volgende instructie uit om de inlijning voor deze UDF opnieuw in te schakelen:

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

De INLINE-component is niet verplicht. Als de INLINE-component niet is opgegeven, wordt deze automatisch ingesteld op ON/OFF op basis van of de UDF inline kan worden geplaatst. Als INLINE = ON is opgegeven, maar de UDF niet in aanmerking komt voor inlineing, wordt er een fout gegenereerd.

Opmerkingen

Zoals beschreven in dit artikel, transformeert scalaire UDF-inlining een query met scalaire UDF's in een query met een equivalente scalaire subquery. Vanwege deze transformatie ziet u mogelijk enkele verschillen in gedrag in de volgende scenario's:

• Het inlijnen van resultaten in een andere query-hash voor dezelfde querytekst.

• Bepaalde waarschuwingen in instructies binnen de UDF (zoals delen door nul, enzovoort) die eerder verborgen konden zijn, kunnen vanwege inlining naar voren komen.

• Hints voor join op queryniveau zijn mogelijk niet meer geldig, omdat inlining nieuwe joins kan introduceren. In plaats daarvan moeten lokale join hints worden gebruikt.

• Weergaven die verwijzen naar inline scalaire UDF's kunnen niet worden geïndexeerd. Als u een index voor dergelijke weergaven wilt maken, schakelt u de inlining uit voor de UDF's waarnaar wordt verwezen.

• Er zijn mogelijk enkele verschillen in het gedrag van dynamische gegevensmaskering met UDF-inlining.

  In bepaalde situaties (afhankelijk van de logica in de UDF) kan inlinen meer conservatief zijn met betrekking tot het maskeren van uitvoerkolommen. In scenario's waarin de kolommen waarnaar wordt verwezen in een UDF geen uitvoerkolommen zijn, worden ze niet gemaskeerd.

• Als een UDF verwijst naar ingebouwde functies zoals SCOPE_IDENTITY(), @@ROWCOUNTof @@ERROR, verandert de waarde die wordt geretourneerd door de ingebouwde functie met inlijning. Deze wijziging in gedrag komt doordat het inlijnen de reikwijdte van instructies binnen de UDF verandert. Vanaf SQL Server 2019 (15.x) CU2 wordt het inlijnen geblokkeerd als de UDF verwijst naar bepaalde intrinsieke functies (bijvoorbeeld @@ROWCOUNT).

• Als een variabele wordt toegewezen met het resultaat van een inline UDF en deze ook wordt gebruikt als index_column_name in FORCESEEKqueryhints, resulteert dit in fout 8622, wat aangeeft dat de queryprocessor geen queryplan kon produceren vanwege de hints die in de query zijn gedefinieerd.

Verwante inhoud

• door de gebruiker gedefinieerde functies (Database Engine) maken
• Prestatiecentrum voor SQL Server Database Engine en Azure SQL Database
• architectuurhandleiding voor queryverwerking
• Logische en fysieke showplan-operatorreferentie
• Joins (SQL Server)
• Intelligente queryverwerking demonstreren
• OPLOSSEN: scalaire UDF-inliningsproblemen in SQL Server 2019