T-SQL-Funktionen: Was der Optimizer sieht und was nicht

Drei Arten von Funktionen, drei Verträge mit dem Optimizer

Eingebaute Funktionen wie DATEADD oder COALESCE gehören nicht in diese Liste. Sie sind Teil der Sprache und keine Optimierungsgrenze, können aber auf einer Suchspalte einen Index unbrauchbar machen.

Die Skalar-Funktion: je Zeile ein eigener Aufruf

Ohne Inlining führt SQL Server eine Skalar-Funktion für jede Zeile aus, für die er ihren Wert braucht. Liest die Funktion selbst Daten, wird in jedem Aufruf eine eigene kleine Abfrage ausgeführt. Drei Dinge machen das teuer:

• Der Optimizer kennt die Kosten nicht. Im geschätzten Plan erscheint der Aufruf als billiger Rechenschritt. Die Arbeit in der Funktion fließt nicht in die Planwahl ein.
• Der ganze Plan läuft seriell. Eine T-SQL-Skalar-Funktion ohne Inlining verhindert Parallelität für die gesamte Abfrage. Im tatsächlichen Plan steht unter NonParallelPlanReason auf SQL Server 2022 und 2025 TSQLUserDefinedFunctionsNotParallelizable. SQL Server 2017 und 2019 nannten im Lab nur den allgemeinen Grund CouldNotGenerateValidParallelPlan.
• Die Arbeit wird nicht gebündelt. Statt einmal alle Positionen zu lesen und zu gruppieren, liest jede Ausführung ihre eigenen Positionen nach.

Im Lab berechnete eine Abfrage die Nettosumme je Kunde für die Bestellungen eines Zeitraums, auf fünf Wegen. Das Ergebnis war jedes Mal dasselbe, der Code und die Laufzeit nicht. Die Reiter zeigen den Code, darunter steht das Ergebnis auf SQL Server 2022 bei 100.000 Bestellungen mit 400.000 Positionen:

SELECT o.CustomerId,       CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))            AS decimal(19,2)) AS NettoFROM dbo.Orders AS oJOIN dbo.OrderLines AS ol ON ol.OrderId = o.OrderId   -- alle Positionen auf einmalWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto (@OrderId int)RETURNS decimal(19,2)ASBEGIN    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);END;GO SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- ein Aufruf je BestellungFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

-- Funktion und Abfrage sind unverändert.-- Anders sind nur Kompatibilitätsgrad (ab 150) und diese Option (Standard: ON):ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON; SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- wird eingebautFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.itvf_OrderNetto (@OrderId int)RETURNS TABLE   -- nur eine SELECT-Anweisung, kein BEGIN … ENDASRETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                    AS decimal(19,2)) AS Netto        FROM dbo.OrderLines AS ol        WHERE ol.OrderId = @OrderId);GO SELECT o.CustomerId, SUM(n.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.itvf_OrderNetto(o.OrderId) AS nWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.mstvf_OrderNetto (@OrderId int)RETURNS @t TABLE (Netto decimal(19,2))   -- Rückgabe über eine TabellenvariableASBEGIN    INSERT @t    SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                AS decimal(19,2))    FROM dbo.OrderLines AS ol    WHERE ol.OrderId = @OrderId;    RETURN;END;GO SELECT o.CustomerId, SUM(t.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.mstvf_OrderNetto(o.OrderId) AS tWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

Die relationale Abfrage und die Skalar-Funktion ohne Inlining auf allen vier Versionen:

Die relationale Abfrage lief auf allen Versionen parallel auf 8 Kernen, die Funktion seriell. Bei 1.000 Bestellungen stand es 11–12 ms gegen 44–62 ms, bei einer einzigen Bestellung gab es keinen messbaren Unterschied. Die Funktion ist also nicht an sich langsam. Teuer wird ihr Aufruf je Zeile, sobald viele Zeilen ihn brauchen. Warum die Funktion ab SQL Server 2019 langsamer lief als auf 2017, haben wir nicht untersucht.

Scalar UDF Inlining: ab SQL Server 2019

Seit SQL Server 2019 kann der Optimizer viele Skalar-Funktionen in die aufrufende Abfrage einbauen. Aus dem Funktionsaufruf wird ein Teil der Abfrage, den er schätzen, umstellen und parallelisieren kann. Voraussetzungen sind der Kompatibilitätsgrad 150 oder höher, die eingeschaltete Datenbankoption TSQL_SCALAR_UDF_INLINING (Standard) und eine Funktion, die die Bedingungen von Microsoft erfüllt.

Im Lab lief dieselbe Abfrage auf SQL Server 2019, 2022 und 2025 auch mit eingeschaltetem Inlining:

Das Inlining holt fast die ganze Zeit zurück. Im Plan steht kein Funktionsaufruf mehr, dafür ContainsInlineScalarTsqlUdfs="1", und er läuft wieder parallel. Der eingebaute Plan ist aber nicht der relationale: Er sucht die Positionen für jede Bestellung einzeln, statt sie einmal zu lesen und zu gruppieren. Das kostet 20-mal so viele Lesevorgänge und das 1,4- bis 1,9-Fache an CPU. Auf die Laufzeit schlug das nicht durch, weil sich die Arbeit auf 8 Kerne verteilte. Inliniert heißt also: Der Optimizer sieht die Logik. Es heißt nicht, dass das Ergebnis so günstig ist wie eine von Hand geschriebene Abfrage.

Inlinefähig ist nicht inliniert

sys.sql_modules.is_inlineable = 1 sagt nur, dass die Funktion selbst die Bedingungen erfüllt. Microsoft trennt ausdrücklich zwischen Bedingungen an die Definition der Funktion und Bedingungen an die aufrufende Abfrage. is_inlineable berücksichtigt nur die ersten. Ob ein bestimmter Aufruf eingebaut wird, hängt außerdem vom Aufruf, vom Kompatibilitätsgrad und von der Konfiguration ab. Und eine kleine Änderung an der Funktion kann das Inlining verhindern, ohne dass sich am Ergebnis etwas ändert.

Im Lab bekam dieselbe Funktion drei Formen und wurde in drei Zusammenhängen aufgerufen, jeweils für 100.000 Bestellungen mit eingeschaltetem Inlining auf SQL Server 2019, 2022 und 2025. Das Ergebnis war in allen Fällen dasselbe. So sahen die Varianten aus:

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto (@OrderId int)RETURNS decimal(19,2)ASBEGIN    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);   -- genau ein RETURNEND;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto_c (@OrderId int)RETURNS decimal(19,2)ASBEGIN    DECLARE @n decimal(19,2);    SET @n = (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                          AS decimal(19,2))              FROM dbo.OrderLines AS ol              WHERE ol.OrderId = @OrderId);    RETURN @n;   -- weiterhin nur ein RETURNEND;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto_b (@OrderId int)RETURNS decimal(19,2)ASBEGIN    IF @OrderId IS NULL    BEGIN        RETURN NULL;   -- zweites RETURN: nicht mehr inlinefähig    END    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);END;

SELECT dbo.fn_OrderNetto(o.OrderId) AS Netto, COUNT(*) AS AnzahlFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY dbo.fn_OrderNetto(o.OrderId);   -- nicht eingebaut

SELECT o.OrderId, o.CustomerIdFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'ORDER BY dbo.fn_OrderNetto(o.OrderId), o.OrderId;   -- nicht eingebaut

Zusammengefasst:

Zwei Befunde:

• Eine harmlose Zeile kostet das Inlining. Seit SQL Server 2019 CU5 kommt nur noch eine Funktion mit genau einem RETURN dafür in Frage. Der frühe Ausstieg für NULL ändert am Ergebnis nichts, die Laufzeit steigt aber auf das Achtfache. Lokale Variablen waren dagegen kein Hindernis.
• Inlinefähig heißt nicht inliniert. Dieselbe Funktion mit is_inlineable = 1 wurde im GROUP BY und im ORDER BY nicht eingebaut. Microsoft führt beide Fälle unter den Bedingungen an den Aufruf. Die Spalte sagt also, ob eine Funktion eingebaut werden kann. Ob sie es wurde, zeigt nur der Plan.

So prüfen Sie es: Im tatsächlichen Ausführungsplan einer inlinierten Funktion fehlt der Funktionsaufruf; stattdessen erscheinen die Tabellen, die die Funktion liest. Die Inventur auf der Übersichtsseite zeigt zusätzlich, welche Skalar-Funktionen tatsächlich als eigener Aufruf liefen. Im Lab erschien dort genau die Funktion, die nicht eingebaut worden war, auch in den Fällen mit is_inlineable = 1. Die Zahl der Aufrufe ist nicht immer die Zahl der Zeilen: Bei 1.000 Bestellungen zählte SQL Server 2.000 Aufrufe, weil der Plan die Funktion an zwei Stellen verwendete.

Microsoft hat seit 2019 mehrere Fehler im Inlining über kumulative Updates korrigiert und dabei die Bedingungen nachgeschärft. Prüfen Sie nach einem CU-Update deshalb nicht nur die Laufzeit, sondern auch, ob die Funktion noch inliniert wird. Abschalten lässt sich das Inlining gezielt je Funktion (WITH INLINE = OFF), je Abfrage (OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))) oder für die Datenbank.

Die Inline-Tabellenwertfunktion: Kapselung ohne Blackbox

Eine Inline-Tabellenwertfunktion besteht aus genau einer SELECT-Anweisung. Der Optimizer baut sie in die aufrufende Abfrage ein wie eine View, der man Parameter übergeben kann. Sie kapselt die Logik also, ohne sie zu verbergen, und das in jeder Version seit SQL Server 2000.

Im Lab lag die Inline-TVF auf allen vier Versionen gleichauf mit der relationalen Abfrage, auch auf SQL Server 2017, wo es noch kein Inlining von Skalar-Funktionen gibt. Bei 100.000 Bestellungen brauchte sie 0,71–0,81 s gegenüber 0,70–0,81 s, bei denselben rund 16.250 Lesevorgängen und mit parallelem Plan. Der Optimizer hatte sie vollständig aufgelöst.

Der Umbau einer Skalar-Funktion in eine Inline-TVF ändert den Aufruf: Der Funktionsaufruf wandert aus der Spaltenliste in die FROM-Klausel.

SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- Funktion in der SpaltenlisteFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

SELECT o.CustomerId,       SUM(n.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.itvf_OrderNetto(o.OrderId) AS n   -- Funktion in der FROM-KlauselWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

Das ist mehr Umbau als ein Schalter, funktioniert aber auch auf Versionen vor 2019 und hängt nicht von Inlining-Bedingungen ab. Liefert die Funktion für eine Bestellung ohne Positionen NULL, verhält sich CROSS APPLY hier genauso, weil eine Aggregation ohne GROUP BY immer eine Zeile zurückgibt.

Die Multi-Statement-Tabellenwertfunktion: die geschätzte Tabelle

Eine Multi-Statement-Tabellenwertfunktion füllt eine Tabellenvariable mit mehreren Anweisungen und gibt sie zurück. Was darin passiert, sieht der Optimizer nicht, nur die Tabelle am Ende. Deren Größe kennt er vor der Ausführung nicht:

• Mit dem alten Kardinalitätsschätzer (vor SQL Server 2014) schätzt er 1 Zeile, mit dem neuen ab 2014 100 Zeilen, unabhängig davon, wie viele es tatsächlich sind.
• Ab SQL Server 2017 (Kompatibilitätsgrad 140) kann Interleaved Execution greifen: Der Optimizer führt die Funktion zuerst aus, zählt die Zeilen und optimiert den Rest der Abfrage mit dieser Zahl.
• Diese Zahl wird mit dem Plan gespeichert. Ruft die nächste Ausführung die Funktion mit einem Parameter auf, der statt 10 Zeilen 100.000 liefert, bleibt es bei der ersten Zählung.

Im Vergleich der Funktionsarten oben war die Multi-Statement-Variante derselben Berechnung die langsamste: 8,7–11,1 s bei 100.000 Bestellungen, seriell. Die Funktion lief 100.000-mal, geschätzt waren je Aufruf 100 Zeilen, geliefert hat sie jeweils eine.

Für die Schätzung selbst lieferte eine zweite Multi-Statement-Funktion alle Positionen eines Kunden. Drei Kunden mit 10, 1.000 und 100.000 Positionen wurden nacheinander über sp_executesql abgefragt, also mit demselben Plan:

CREATE OR ALTER FUNCTION dbo.mstvf_KundenPositionen (@CustomerId int)RETURNS @t TABLE (OrderLineId int, OrderId int, ProductId int,                  Qty int, Price decimal(19,4), Discount decimal(5,4))ASBEGIN    INSERT @t    SELECT ol.OrderLineId, ol.OrderId, ol.ProductId, ol.Qty, ol.Price, ol.Discount    FROM dbo.Orders AS o    JOIN dbo.OrderLines AS ol ON ol.OrderId = o.OrderId    WHERE o.CustomerId = @CustomerId;    RETURN;END;GO -- Derselbe Text für alle drei Kunden: ein Plan, mehrfach verwendet.EXEC sp_executesql    N'SELECT p.ProductId, COUNT(*) AS Zeilen, SUM(t.Qty * p.Listenpreis) AS Wert      FROM dbo.mstvf_KundenPositionen(@c) AS t      JOIN dbo.Products AS p ON p.ProductId = t.ProductId      GROUP BY p.ProductId      ORDER BY SUM(t.Qty * p.Listenpreis) DESC',    N'@c int',    @c = 99998;   -- erster Aufruf, z. B. Kunde mit 10 Positionen

Die Ergebnisse:

• Interleaved Execution griff auf allen vier Versionen. Beim ersten Aufruf stimmte die Schätzung exakt.
• Die erste Zählung blieb für alle weiteren Aufrufe stehen. Kam der kleine Kunde zuerst, blieb es bei einem Plan für 10 Zeilen (Nested Loops), auch für 100.000. Kam der große zuerst, blieb es beim Plan für 100.000 Zeilen (Hash Match), auch für 10. Mit abgeschalteter Interleaved Execution (DISABLE_INTERLEAVED_EXECUTION_TVF) stand überall die feste Schätzung 100.
• Der größte Unterschied kam nicht aus dieser Schätzung. Kam der große Kunde zuerst, brauchte der kleine danach 0,53–0,70 s statt 1,4–1,9 ms. Das galt mit und ohne Interleaved Execution. Unsere Deutung, im Plan nicht belegt: Auch die Anweisungen innerhalb der Funktion werden mit dem ersten Parameter kompiliert und danach wiederverwendet, wie in einer gespeicherten Prozedur.
• Eine exakte Erstschätzung war nicht automatisch schneller. Der große Kunde lief mit dem Plan für 10 Zeilen in 0,76–0,85 s, mit dem passend geschätzten in 0,95–1,14 s. Dafür las der Plan für 10 Zeilen fünfmal so viel (rund 713.000 gegenüber 138.000 Lesevorgängen), und auf SQL Server 2017 und 2019 lagerte er die Sortierung nach TempDB aus.

Bei einer Multi-Statement-Funktion mit stark schwankender Ergebnismenge entscheidet also der erste Aufruf nach dem Kompilieren über die Laufzeit aller folgenden. Eine Inline-TVF nimmt diesen Zufall heraus, weil der Optimizer ihre Abfrage mit der aufrufenden zusammen plant.

Welche Funktion wofür?

So gehen Sie vor

1. Funktionen finden. Die Inventur zeigt, welche Skalar-Funktionen als eigener Aufruf liefen und wie oft.
2. Tatsächlichen Plan holen. Prüfen Sie NonParallelPlanReason, ob die Funktion als Aufruf erscheint, und bei Tabellenwertfunktionen die geschätzten gegenüber den tatsächlichen Zeilen.
3. Gezielt umbauen. Die Tabelle oben zeigt die Richtung. Prüfen Sie nach dem Umbau, ob das Ergebnis identisch ist, einschließlich NULL-Werten und Rundung.
4. Nach jedem Upgrade wiederholen. Kompatibilitätsgrad und kumulative Updates ändern, was mit Funktionen geschieht.

Woher die gemessenen Zeiten in Query Store kommen, wenn Funktionen im Spiel sind, und warum sie sich überlappen können, zeigt Wo steckt die Zeit?.

Quellen

• Microsoft: Scalar UDF Inlining
• Microsoft: Interleaved Execution für Multi-Statement-Tabellenwertfunktionen
• Microsoft Support: KB4538581 – Korrekturen am Scalar UDF Inlining
• Microsoft: sys.dm_exec_function_stats
• Microsoft: Benutzerdefinierte Funktionen

Weitere Themen zur Abstraktion: Verschachtelte Views · Zurück zur Übersicht: Das Paradox der Abstraktion

Weitere Themen zur Abstraktion