• 08/04/2020
• 12 minuten om te lezen
• • s
  • M
  • M
  • p
  • j
  • +6

Geldt voor: SQL Server (alle ondersteunde versies)

Dit artikel introduceert Scalar UDF Inlining, een functie in de suite Intelligent Query Processing. Deze functie verbetert de prestaties van query’s die scalaire UDF’s aanroepen in SQL Server (vanaf SQL Server 2019 (15.x)).

T-SQL scalaire User-Defined Functions

User-Defined Functions (UDF’s) die zijn geïmplementeerd in Transact-SQL en een enkele gegevenswaarde retourneren, worden aangeduid als T-SQL Scalar User-Defined Functions. T-SQL UDFs zijn een elegante manier om code hergebruik en modulariteit over Transact-SQL queries te bereiken. Sommige berekeningen (zoals complexe business rules) zijn eenvoudiger uit te drukken in imperatieve UDF vorm. UDF’s helpen bij het opbouwen van complexe logica zonder dat expertise in het schrijven van complexe SQL queries nodig is. Zie Gebruikersgedefinieerde functies maken (Database Engine) voor meer informatie over UDF’s.

Prestaties van scalaire UDF’s

Scalaire UDF’s presteren doorgaans slecht om de volgende redenen:

• Iteratieve aanroeping: UDF’s worden op een iteratieve manier aangeroepen, eenmaal per kwalificerende tupel. Dit brengt extra kosten met zich mee voor het herhaaldelijk wisselen van context als gevolg van het aanroepen van functies. Met name UDF’s die Transact-SQL-query’s in hun definitie uitvoeren, ondervinden hiervan ernstige gevolgen.

• Het ontbreken van kosten: Tijdens optimalisatie worden alleen relationele operators gekost, terwijl scalaire operators dat niet doen. Vóór de introductie van scalaire UDF’s waren andere scalaire operatoren over het algemeen goedkoop en hoefden ze niet te worden doorberekend. Een kleine toevoeging van CPU-kosten voor een scalaire operatie was voldoende. Er zijn scenario’s waarin de werkelijke kosten aanzienlijk zijn, en toch ondervertegenwoordigd blijven.

• Geïnterpreteerde uitvoering: UDF’s worden geëvalueerd als een reeks statements, die statement-voor-statement worden uitgevoerd. Elk statement zelf wordt gecompileerd, en het gecompileerde plan wordt in de cache opgeslagen. Hoewel deze caching-strategie enige tijd bespaart omdat hercompilaties worden vermeden, wordt elk statement afzonderlijk uitgevoerd. Er worden geen cross-statement optimalisaties uitgevoerd.

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

Automatische inlining van scalaire UDF’s

Het doel van de functie voor scalaire UDF’s inlining is om de prestaties te verbeteren van query’s die T-SQL scalaire UDF’s aanroepen, waarbij de uitvoering van UDF’s de belangrijkste bottleneck is.

Met deze nieuwe functie worden scalaire UDF’s automatisch omgezet in scalaire expressies of scalaire subqueries die in de aanroepende query in plaats van de UDF operator worden gebruikt. Deze expressies en subquery’s worden vervolgens geoptimaliseerd. Het resultaat is dat het query-plan niet langer een door de gebruiker gedefinieerde functie-operator bevat, maar de effecten ervan worden waargenomen in het plan, net als views of inline TVF’s.

Voorbeeld 1 – Scalaire UDF met één statement

Overweeg de volgende query.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE;

Deze query berekent de som van gereduceerde prijzen voor regelitems en presenteert de resultaten gegroepeerd per verzenddatum en verzendprioriteit. De uitdrukking L_EXTENDEDPRICE *(1 - L_DISCOUNT) is de formule voor de korting op de prijs van een bepaald artikel. Dergelijke formules kunnen in functies worden ondergebracht ten behoeve van modulariteit en hergebruik.

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

Nu kan de query worden aangepast om deze UDF aan te roepen.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

Om de eerder geschetste redenen presteert de query met de UDF slecht. Nu, met scalaire UDF inlining, wordt de scalaire expressie in de body van de UDF direct in de query gesubstitueerd. De resultaten van het uitvoeren van deze query staan in de onderstaande tabel:

Query:	Query zonder UDF	Query met UDF (zonder inlining)	Query 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 het TPC-H-schema), die wordt uitgevoerd op een machine met dubbele processor (12 core), 96 GB RAM, ondersteund door een SSD. De getallen zijn inclusief compilatie- en uitvoeringstijd met een cold procedure cache en buffer pool. De standaard configuratie werd gebruikt, en er werden geen andere indexen aangemaakt.

Example 2 – Multi-statement scalar UDF

Scalar UDFs die worden geïmplementeerd met behulp van meerdere T-SQL statements zoals variabele toewijzingen en conditionele vertakkingen kunnen ook worden inlined. Beschouw de volgende scalaire UDF die, gegeven een klantsleutel, de servicecategorie voor die klant bepaalt. De categorie wordt bepaald door eerst de totale prijs te berekenen van alle orders die door de klant zijn geplaatst met behulp van een SQL-query. Vervolgens gebruikt het een IF (...) ELSE logica om de categorie te bepalen op basis van de totale prijs.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) RETURNS CHAR(10) ASBEGIN 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

Nu, beschouw 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) ziet er als volgt uit:

Zoals het plan laat zien, hanteert 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 inlineing worden dergelijke UDF’s omgezet in equivalente scalaire subquery’s, die in plaats van de UDF in de aanroepende query worden gesubstitueerd.

Voor dezelfde query ziet het plan er met de inline UDF uit als hieronder.

Zoals eerder vermeld, heeft het query plan niet langer een door de gebruiker gedefinieerde functie-operator, maar de effecten ervan zijn nu waarneembaar in het plan, net als views of inline TVF’s. Hier zijn enkele belangrijke observaties uit het bovenstaande plan:

• SQL Server heeft de impliciete join tussen CUSTOMER en ORDERS afgeleid en dat expliciet gemaakt via een join operator.
• SQL Server heeft ook de impliciete GROUP BY O_CUSTKEY on ORDERS afgeleid en heeft de IndexSpool + StreamAggregate gebruikt om die te implementeren.
• SQL Server gebruikt nu parallellisme over alle operatoren.

Afhankelijk van de complexiteit van de logica in de UDF, kan het resulterende query plan ook groter en complexer worden. Zoals we kunnen zien, zijn de operaties in de UDF nu niet langer een ondoorzichtige doos, en dus is de query optimizer in staat om die operaties te kosten en te optimaliseren. En omdat de UDF niet meer in het plan zit, wordt het iteratief aanroepen van UDF’s vervangen door een plan waarin de overhead van functieaanroepen volledig wordt vermeden.

Inlinebare scalaire UDF’s vereisten

Een scalaire T-SQL UDF kan worden geïnlined als aan alle volgende voorwaarden is voldaan:

• De UDF is geschreven met behulp van de volgende constructen:
  • DECLARESET: Variabele declaratie en toewijzingen.
  • SELECT: SQL query met enkele/meerdere variabele toewijzingen 1.
  • IFELSE: Branching met willekeurige niveaus van nesting.
  • RETURN: Enkelvoudige of meervoudige return statements.
  • UDF: Geneste/recursieve functie-aanroepen 2.
  • Anderen: Relationele bewerkingen zoals EXISTSISNULL.

De UDF roept geen intrinsieke functie aan die tijdafhankelijk is (zoals GETDATE()) of neveneffecten 3 heeft (zoals NEWSEQUENTIALID()).

• De UDF gebruikt de EXECUTE AS CALLER-clausule (standaardgedrag als de EXECUTE AS-clausule niet is opgegeven).
• De UDF verwijst niet naar tabelvariabelen of tabelgewaardeerde parameters.
• De query die een scalaire UDF oproept, verwijst niet naar een scalaire UDF-oproep in zijn GROUP BY-clausule.
• De query die een scalaire UDF aanroept in zijn select list met DISTINCT-clausule, heeft geen ORDER BY-clausule.
• De UDF wordt niet gebruikt in ORDER BY clausule.
• De UDF is niet native gecompileerd (interop wordt ondersteund).
• De UDF wordt niet gebruikt in een berekende kolom of een controle constraint definitie.
• De UDF verwijst niet naar door de gebruiker gedefinieerde typen.
• Er zijn geen signatures aan de UDF toegevoegd.
• 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 worden ingelijnd (zoals @@ROWCOUNT).
• De UDF bevat geen verwijzingen naar intrinsieke functies die de resultaten kunnen wijzigen wanneer ze worden ingelijnd (zoals @@ROWCOUNT).
• De UDF bevat geen aggregaatfuncties die als parameters aan een scalaire UDF worden doorgegeven 4.
• De UDF bevat geen verwijzingen naar ingebouwde weergaven (zoals OBJECT_ID) 4.
• De UDF bevat geen verwijzingen naar ingebouwde weergaven (zoals OBJECT_ID)
• De UDF verwijst niet naar XML-methoden 5.
• De UDF bevat geen SELECT met ORDER BY zonder een TOP 1-clausule 5.
• De UDF bevat geen SELECT-query die een opdracht uitvoert in combinatie met de ORDER BY-clausule (zoals SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• De UDF bevat niet meerdere RETURN-verklaringen 6.
• De UDF wordt niet aangeroepen vanuit een RETURN-verklaring 6.
• De UDF verwijst niet naar de STRING_AGG functie 6.
• De UDF verwijst niet naar externe tabellen 7.
• De query die de UDF aanroept, gebruikt geen GROUPING SETSCUBE, of ROLLUP
• De UDF-oproepende query bevat geen variabele die wordt gebruikt als een UDF-parameter voor toewijzing (bijvoorbeeld SELECT @y = 2@x = UDF(@y))7.

De UDF-oproepende query bevat geen variabele die wordt gebruikt als een UDF-parameter voor toewijzing (bijvoorbeeld

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

2 Recursieve UDF’s zullen slechts tot een bepaalde diepte worden inlined.

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

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

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

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

7 Beperking toegevoegd in SQL Server 2019 (15.x) CU6

Checking whether or not a UDF can be inlined

Voor elke T-SQL scalar UDF wordt in de sys.sql_modules catalogus een eigenschap genaamd is_inlineable, die aangeeft of een UDF inlinebaar is of niet.

Een waarde van 1 geeft aan dat deze inlineable is, en 0 geeft het tegendeel aan. Deze eigenschap zal ook een waarde van 1 hebben 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 zal worden geinlineed. SQL Server beslist (per query, per UDF) of een UDF al dan niet wordt geïnline. Enkele voorbeelden van wanneer een UDF mogelijk niet wordt geinlined:

• Als de UDF-definitie duizenden regels code beslaat, kan SQL Server ervoor kiezen deze niet te inlinen.

• Een UDF die wordt aangeroepen in een GROUP BY-clausule, wordt niet geinlined. Deze beslissing wordt genomen wanneer de query die naar een scalaire UDF verwijst, wordt gecompileerd.

• Als de UDF is ondertekend met een certificaat. Omdat handtekeningen kunnen worden toegevoegd en verwijderd nadat een UDF is gemaakt, wordt de beslissing om al dan niet te inlinen genomen wanneer de query die naar een scalaire UDF verwijst, wordt gecompileerd. Bijvoorbeeld, systeemfuncties worden gewoonlijk ondertekend met een certificaat. U kunt sys.crypt_properties gebruiken om te zien welke objecten zijn ondertekend.

  SELECT * FROM sys.crypt_properties AS cpINNER JOIN sys.objects AS o ON cp.major_id = o.object_id;

Controleer of inlining is gebeurd of niet

Als aan alle voorwaarden is voldaan en SQL Server besluit om inlining uit te voeren, wordt de UDF omgezet in een relationele expressie. Uit het queryplan kan eenvoudig worden opgemaakt of de inlining al dan niet is uitgevoerd:

• In het plan xml staat geen <UserDefinedFunction> xml-node voor een UDF die met succes is geïnlined.
• Er worden bepaalde XEvents uitgezonden.

Scalar UDF Inlining

U kunt workloads automatisch in aanmerking laten komen voor Scalar UDF Inlining door compatibiliteitsniveau 150 voor de database in te schakelen. U kunt dit instellen met Transact-SQL. Bijvoorbeeld:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

Afgezien hiervan zijn er geen andere wijzigingen nodig in UDF’s of query’s om te profiteren van deze functie.

Scalar UDF Inlining uitschakelen zonder het compatibiliteitsniveau te wijzigen

Scalar UDF inlining kan worden uitgeschakeld op het niveau van de database, het statement of de UDF, terwijl het compatibiliteitsniveau van de database 150 en hoger gehandhaafd blijft. Om scalar UDF inlining op database-niveau uit te schakelen, voert u het volgende statement uit in de context van de betreffende database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Om Scalar UDF Inlining voor de database weer in te schakelen, voert u het volgende statement uit binnen de context van de betreffende database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Als deze instelling is ingeschakeld, wordt deze weergegeven in sys.database_scoped_configurations.U kunt Scalar UDF Inlining ook uitschakelen voor een specifieke query door DISABLE_TSQL_SCALAR_UDF_INLINING aan te wijzen als een USE HINT queryhint. Bijvoorbeeld:

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATEOPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Scalar UDF Inlining kan ook worden uitgeschakeld voor een specifieke UDF met behulp van de INLINE-clausule in hetCREATE FUNCTIONofALTER FUNCTIONstatement.Bijvoorbeeld:

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

Als het bovenstaande statement eenmaal is uitgevoerd, zal deze UDF nooit meer worden geïnlined in een query die hem aanroept. Om in-lining voor deze UDF weer in te schakelen, voert u het volgende statement uit:

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

Belangrijke opmerkingen

Zoals beschreven in dit artikel, transformeert scalaire UDF inlining een query met scalaire UDFs in een query met een equivalente scalaire subquery. Door deze transformatie kunnen gebruikers in de volgende scenario’s verschillen in gedrag opmerken:

1. Inlining resulteert in een andere query hash voor dezelfde query tekst.
2. Zekere waarschuwingen in statements binnen de UDF (zoals delen door nul etc.) die eerder verborgen waren, kunnen door inlining tevoorschijn komen.
3. Hints voor joins op query niveau zijn mogelijk niet meer geldig, omdat inlining nieuwe joins kan introduceren. In plaats daarvan moeten lokale join hints worden gebruikt.
4. Views die verwijzen naar inline scalar UDFs kunnen niet worden geïndexeerd. Als je een index op zulke views moet maken, schakel dan inlining uit voor de UDF’s waarnaar wordt verwezen.
5. Er kunnen enkele verschillen zijn in het gedrag van Dynamic Data masking met UDF inlining. In bepaalde situaties (afhankelijk van de logica in de UDF) kan inlining conservatiever zijn met betrekking tot het maskeren van outputkolommen. In scenario’s waarin de kolommen waarnaar in een UDF wordt verwezen, geen uitvoerkolommen zijn, worden ze niet gemaskeerd.
6. Als een UDF naar ingebouwde functies verwijst, zoals SCOPE_IDENTITY()@@ROWCOUNT, of @@ERROR, verandert de waarde die door de ingebouwde functie wordt geretourneerd bij in-lining. Deze gedragsverandering komt doordat inlining de scope van statements binnen de UDF verandert. Vanaf SQL Server 2019 (15.x) CU2, wordt inlining geblokkeerd als de UDF verwijst naar bepaalde intrinsieke functies (bijvoorbeeld @@ROWCOUNT).

Zie ook