• 08/04/2020
• 12 Minuten lesen
• • s
  • M
  • M
  • p
  • j
  • +6

Gilt für: SQL Server (alle unterstützten Versionen)

Dieser Artikel stellt das Scalar UDF Inlining vor, ein Feature aus der Reihe der Intelligent Query Processing Features. Dieses Feature verbessert die Leistung von Abfragen, die skalare UDFs in SQL Server (ab SQL Server 2019 (15.x)) aufrufen.

T-SQL skalare benutzerdefinierte Funktionen

Benutzerdefinierte Funktionen (UDFs), die in Transact-SQL implementiert sind und einen einzelnen Datenwert zurückgeben, werden als T-SQL skalare benutzerdefinierte Funktionen bezeichnet. T-SQL UDFs sind ein eleganter Weg, um Code-Wiederverwendung und Modularität über Transact-SQL-Abfragen hinweg zu erreichen. Einige Berechnungen (z. B. komplexe Geschäftsregeln) lassen sich leichter in imperativer UDF-Form ausdrücken. UDFs helfen beim Aufbau komplexer Logik, ohne dass Fachkenntnisse im Schreiben komplexer SQL-Abfragen erforderlich sind. Weitere Informationen über UDFs finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine).

Leistung von skalaren UDFs

Skalare UDFs haben in der Regel eine schlechte Leistung aus folgenden Gründen:

• Iterativer Aufruf: UDFs werden iterativ aufgerufen, und zwar einmal pro qualifiziertem Tupel. Dadurch entstehen zusätzliche Kosten durch wiederholte Kontextwechsel aufgrund des Funktionsaufrufs. Insbesondere UDFs, die in ihrer Definition Transact-SQL-Abfragen ausführen, sind davon stark betroffen.

• Fehlende Kostenberechnung: Bei der Optimierung werden nur relationale Operatoren kalkuliert, skalare Operatoren hingegen nicht. Vor der Einführung von skalaren UDFs waren andere skalare Operatoren in der Regel billig und mussten nicht kalkuliert werden. Es genügte ein kleiner CPU-Aufwand für eine skalare Operation. Es gibt Szenarien, in denen die tatsächlichen Kosten signifikant sind und dennoch unterrepräsentiert bleiben.

• Interpretierte Ausführung: UDFs werden als ein Stapel von Anweisungen ausgewertet und Anweisung für Anweisung ausgeführt. Jede Anweisung selbst wird kompiliert, und der kompilierte Plan wird zwischengespeichert. Obwohl diese Caching-Strategie eine gewisse Zeitersparnis mit sich bringt, da sie Neukompilierungen vermeidet, wird jede Anweisung isoliert ausgeführt. Es werden keine anweisungsübergreifenden Optimierungen durchgeführt.

• Serielle Ausführung: SQL Server erlaubt keine Intra-Query-Parallelität in Abfragen, die UDFs aufrufen.

Automatisches Inlining von skalaren UDFs

Das Ziel der Inlining-Funktion für skalare UDFs ist es, die Leistung von Abfragen zu verbessern, die skalare T-SQL-UDFs aufrufen, bei denen die UDF-Ausführung den Hauptengpass darstellt.

Mit dieser neuen Funktion werden skalare UDFs automatisch in skalare Ausdrücke oder skalare Unterabfragen umgewandelt, die in der aufrufenden Abfrage anstelle des UDF-Operators eingesetzt werden. Diese Ausdrücke und Unterabfragen werden dann optimiert. Als Ergebnis hat der Abfrageplan keinen benutzerdefinierten Funktionsoperator mehr, aber seine Auswirkungen werden im Plan beobachtet, wie Views oder Inline-TVFs.

Beispiel 1 – Skalare UDF mit einer Anweisung

Betrachten Sie die folgende Abfrage.

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;

Diese Abfrage berechnet die Summe der rabattierten Preise für Einzelposten und stellt die Ergebnisse gruppiert nach Versanddatum und Versandpriorität dar. Der Ausdruck L_EXTENDEDPRICE *(1 - L_DISCOUNT) ist die Formel für den rabattierten Preis für eine bestimmte Position. Solche Formeln können zum Vorteil der Modularität und Wiederverwendung in Funktionen extrahiert werden.

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

Nun kann die Abfrage so modifiziert werden, dass sie diese UDF aufruft.

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

Aus den oben genannten Gründen führt die Abfrage mit der UDF schlecht. Jetzt, mit skalarem UDF-Inlining, wird der skalare Ausdruck im Körper der UDF direkt in der Abfrage ersetzt. Die Ergebnisse der Ausführung dieser Abfrage sind in der folgenden Tabelle dargestellt:

Ausführungszeit:

Abfrage:	Abfrage ohne UDF	Abfrage mit UDF (ohne Inlining)	Abfrage mit skalarem UDF-Inlining
1.6 Sekunden	29 Minuten 11 Sekunden	1.6 Sekunden

Diese Zahlen basieren auf einer 10-GB-CCI-Datenbank (unter Verwendung des TPC-H-Schemas), die auf einer Maschine mit Dual-Prozessor (12 Kerne), 96-GB-RAM und SSD läuft. Die Zahlen beinhalten die Kompilierungs- und Ausführungszeit mit einem Cold-Procedure-Cache und Pufferpool. Es wurde die Standardkonfiguration verwendet, und es wurden keine weiteren Indizes erstellt.

Beispiel 2 – Skalare UDF mit mehreren Anweisungen

Skalare UDFs, die mit mehreren T-SQL-Anweisungen implementiert werden, wie z. B. Variablenzuweisungen und bedingte Verzweigungen, können ebenfalls inlined werden. Betrachten Sie die folgende skalare UDF, die anhand eines Kundenschlüssels die Servicekategorie für diesen Kunden ermittelt. Sie ermittelt die Kategorie, indem sie zunächst den Gesamtpreis aller vom Kunden getätigten Bestellungen mit Hilfe einer SQL-Abfrage errechnet. Dann verwendet sie eine IF (...) ELSE Logik, um die Kategorie basierend auf dem Gesamtpreis zu bestimmen.

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

Betrachten wir nun eine Abfrage, die diese UDF aufruft.

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

Der Ausführungsplan für diese Abfrage in SQL Server 2017 (14.x) (Kompatibilitätsstufe 140 und früher) sieht wie folgt aus:

Wie der Plan zeigt, verfolgt SQL Server hier eine einfache Strategie: für jedes Tupel in der Tabelle CUSTOMER die UDF aufrufen und die Ergebnisse ausgeben. Diese Strategie ist naiv und ineffizient. Mit Inlining werden solche UDFs in äquivalente skalare Unterabfragen umgewandelt, die in der aufrufenden Abfrage anstelle der UDF eingesetzt werden.

Für die gleiche Abfrage sieht der Plan mit der inlined UDF wie folgt aus.

Wie bereits erwähnt, verfügt der Abfrageplan nicht mehr über einen benutzerdefinierten Funktionsoperator, aber seine Auswirkungen sind nun im Plan zu beobachten, wie Views oder Inline-TVFs. Hier sind einige wichtige Beobachtungen aus dem obigen Plan:

• SQL Server hat die implizite Verknüpfung zwischen CUSTOMER und ORDERS abgeleitet und diese über einen Verknüpfungsoperator explizit gemacht.
• SQL Server hat auch das implizite GROUP BY O_CUSTKEY on ORDERS abgeleitet und den IndexSpool + StreamAggregate verwendet, um es zu implementieren.
• SQL Server verwendet jetzt Parallelität über alle Operatoren.

Abhängig von der Komplexität der Logik in der UDF kann der resultierende Abfrageplan auch größer und komplexer werden. Wie wir sehen können, sind die Operationen innerhalb der UDF jetzt keine undurchsichtige Box mehr, und daher ist der Abfrageoptimierer in der Lage, diese Operationen zu berechnen und zu optimieren. Da die UDF nicht mehr im Plan enthalten ist, wird außerdem der iterative UDF-Aufruf durch einen Plan ersetzt, der den Overhead durch Funktionsaufrufe vollständig vermeidet.

Anforderungen an inlinefähige skalare UDFs

Eine skalare T-SQL UDF kann inlined werden, wenn alle der folgenden Bedingungen erfüllt sind:

• Die UDF wird unter Verwendung der folgenden Konstrukte geschrieben:
  • DECLARESET: Variablendeklaration und -zuweisungen.
  • SELECT: SQL-Abfrage mit einfachen/mehrfachen Variablenzuweisungen 1.
  • IFELSE: Verzweigung mit beliebigen Verschachtelungsebenen.
  • RETURN: Einzelne oder mehrere Return-Anweisungen.
  • UDF: Verschachtelte/rekursive Funktionsaufrufe 2.
  • Sonstiges: Relationale Operationen wie EXISTSISNULL.
• Die UDF ruft keine intrinsische Funktion auf, die entweder zeitabhängig ist (z. B. GETDATE()) oder Seiteneffekte hat3 (z. B. NEWSEQUENTIALID()).
• Die UDF verwendet die Klausel EXECUTE AS CALLER (Standardverhalten, wenn die Klausel EXECUTE AS nicht angegeben wird).
• Die UDF referenziert keine Tabellenvariablen oder tabellenwertigen Parameter.
• Die Abfrage, die eine skalare UDF aufruft, referenziert keinen skalaren UDF-Aufruf in ihrer GROUP BY-Klausel.
• Die Abfrage, die eine skalare UDF in ihrer Select-Liste mit DISTINCT-Klausel aufruft, hat keine ORDER BY-Klausel.
• Die UDF wird nicht in der ORDER BY-Klausel verwendet.
• Die UDF ist nicht nativ kompiliert (Interop wird unterstützt).
• Die UDF wird nicht in einer berechneten Spalte oder einer Prüfeinschränkungsdefinition verwendet.
• Die UDF referenziert keine benutzerdefinierten Typen.
• Es werden keine Signaturen zur UDF hinzugefügt.
• Die UDF ist keine Partitionsfunktion.
• Die UDF enthält keine Verweise auf Common Table Expressions (CTEs).
• Die UDF enthält keine Verweise auf intrinsische Funktionen, die beim Inlinen die Ergebnisse verändern können (z. B. @@ROWCOUNT) 4.
• Die UDF enthält keine aggregierten Funktionen, die als Parameter an eine skalare UDF übergeben werden 4.
• Die UDF referenziert keine eingebauten Ansichten (wie OBJECT_ID) 4.
• Die UDF referenziert keine XML-Methoden 5.
• Die UDF enthält kein SELECT mit ORDER BY ohne eine TOP 1 Klausel 5.
• Die UDF enthält keine SELECT-Abfrage, die eine Zuweisung in Verbindung mit der ORDER BY-Klausel durchführt (wie z. B. SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• Die UDF enthält nicht mehrere RETURN-Anweisungen 6.
• Die UDF wird nicht von einer RETURN-Anweisung aufgerufen 6.
• Die UDF referenziert nicht die Funktion STRING_AGG 6.
• Die UDF referenziert keine entfernten Tabellen 7.
• Die UDF-aufrufende Abfrage verwendet keine GROUPING SETSCUBE, oder ROLLUP
• Die UDF-aufrufende Abfrage enthält keine Variable, die als UDF-Parameter für die Zuweisung verwendet wird (zum Beispiel SELECT @y = 2@x = UDF(@y))7.

1 SELECT mit variabler Akkumulation/Aggregation wird für das Inlining nicht unterstützt (wie z.B. SELECT @val += col1 FROM table1).

2 Rekursive UDFs werden nur bis zu einer bestimmten Tiefe inlined.

3 Intrinsische Funktionen, deren Ergebnisse von der aktuellen Systemzeit abhängen, sind zeitabhängig. Eine intrinsische Funktion, die möglicherweise einen internen globalen Zustand aktualisiert, ist ein Beispiel für eine Funktion mit Seiteneffekten. Solche Funktionen geben bei jedem Aufruf unterschiedliche Ergebnisse zurück, basierend auf dem internen Zustand.

4 Einschränkung hinzugefügt in SQL Server 2019 (15.x) CU2

5 Einschränkung hinzugefügt in SQL Server 2019 (15.x) CU4

6 Einschränkung hinzugefügt in SQL Server 2019 (15.x) CU5

7 Einschränkung hinzugefügt in SQL Server 2019 (15.x) CU6

Prüfen, ob eine UDF inlined werden kann

Für jede T-SQL-Skalar-UDF wird in der sys.sql_modules-Katalogansicht eine Eigenschaft namens is_inlineable, die anzeigt, ob eine UDF inline-fähig ist oder nicht.

Ein Wert von 1 bedeutet, dass sie inline-fähig ist, und 0 bedeutet das Gegenteil. Diese Eigenschaft hat auch für alle Inline-TVFs den Wert 1. Für alle anderen Module ist der Wert 0.

Wenn eine skalare UDF inlinefähig ist, bedeutet das nicht, dass sie immer inlinefähig ist. SQL Server entscheidet (auf Basis der einzelnen Abfragen und UDFs), ob eine UDF inlinefähig ist oder nicht. Einige Beispiele dafür, wann eine UDF möglicherweise nicht inline ist:

• Wenn die UDF-Definition Tausende von Codezeilen umfasst, entscheidet SQL Server möglicherweise, sie nicht zu inline zu machen.

• Ein UDF-Aufruf in einer GROUP BY-Klausel wird nicht inline sein. Diese Entscheidung wird getroffen, wenn die Abfrage, die eine skalare UDF referenziert, kompiliert wird.

• Wenn die UDF mit einem Zertifikat signiert ist. Da Signaturen hinzugefügt und gelöscht werden können, nachdem eine UDF erstellt wurde, wird die Entscheidung, ob Inline oder nicht, getroffen, wenn die Abfrage, die auf eine skalare UDF verweist, kompiliert wird. Zum Beispiel werden Systemfunktionen typischerweise mit einem Zertifikat signiert. Sie können sys.crypt_properties verwenden, um herauszufinden, welche Objekte signiert sind.

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

Prüfen, ob Inlining stattgefunden hat oder nicht

Wenn alle Vorbedingungen erfüllt sind und SQL Server entscheidet, Inlining durchzuführen, wandelt er die UDF in einen relationalen Ausdruck um. Aus dem Abfrageplan lässt sich leicht herausfinden, ob Inlining stattgefunden hat oder nicht:

• Die Plan-Xml wird keinen <UserDefinedFunction>-Xml-Knoten für eine UDF haben, die erfolgreich inlined wurde.
• Bestimmte XEvents werden ausgegeben.

Aktivieren von Scalar UDF Inlining

Sie können Arbeitslasten automatisch für Scalar UDF Inlining qualifizieren, indem Sie Kompatibilitätsebene 150 für die Datenbank aktivieren. Sie können dies mit Transact-SQL einstellen. Beispiel:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

Abgesehen davon müssen keine weiteren Änderungen an UDFs oder Abfragen vorgenommen werden, um diese Funktion zu nutzen.

Deaktivieren von Scalar UDF Inlining ohne Änderung der Kompatibilitätsebene

Scalar UDF Inlining kann auf Datenbank-, Anweisungs- oder UDF-Ebene deaktiviert werden, während die Datenbankkompatibilitätsebene 150 und höher beibehalten wird. Um das skalare UDF-Inlining im Datenbankbereich zu deaktivieren, führen Sie die folgende Anweisung im Kontext der betreffenden Datenbank aus:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Um das Scalar-UDF-Inlining für die Datenbank wieder zu aktivieren, führen Sie die folgende Anweisung im Kontext der betreffenden Datenbank aus:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Wenn diese Einstellung aktiviert ist, wird sie in sys.database_scoped_configurations als aktiviert angezeigt.Sie können Scalar UDF Inlining auch für eine bestimmte Abfrage deaktivieren, indem Sie DISABLE_TSQL_SCALAR_UDF_INLINING als USE HINT Abfrage-Hinweis bezeichnen. Beispiel:

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

Tipp

Ein USE HINT Abfragehinweis hat Vorrang vor der datenbankübergreifenden Konfiguration oder der Einstellung der Kompatibilitätsebene.

Scalar UDF Inlining kann auch für eine bestimmte UDF mit der INLINE-Klausel in der CREATE FUNCTION oder ALTER FUNCTION-Anweisung deaktiviert werden.Beispiel:

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;

Nach der Ausführung der obigen Anweisung wird diese UDF in keiner Abfrage, die sie aufruft, mehr inline sein. Um das Inlining für diese UDF wieder zu aktivieren, führen Sie die folgende Anweisung aus:

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

Wichtige Hinweise

Wie in diesem Artikel beschrieben, wandelt das skalare UDF-Inlining eine Abfrage mit skalaren UDFs in eine Abfrage mit einer äquivalenten skalaren Subquery um. Aufgrund dieser Umwandlung können Benutzer in den folgenden Szenarien einige Unterschiede im Verhalten feststellen:

1. Inlining führt zu einem anderen Abfrage-Hash für denselben Abfragetext.
2. Bestimmte Warnungen in Anweisungen innerhalb der UDF (wie z. B. Teilen durch Null usw.), die zuvor verborgen waren, können aufgrund von Inlining auftauchen.
3. Query Level Join Hints sind möglicherweise nicht mehr gültig, da durch Inlining neue Joins eingeführt werden. Stattdessen müssen lokale Join-Hinweise verwendet werden.
4. Views, die auf skalare Inline-UDFs verweisen, können nicht indiziert werden. Wenn Sie einen Index für solche Ansichten erstellen müssen, deaktivieren Sie das Inlining für die referenzierten UDFs.
5. Es kann einige Unterschiede im Verhalten der dynamischen Datenmaskierung mit dem UDF-Inlining geben.

In bestimmten Situationen (abhängig von der Logik in der UDF) kann das Inlining konservativer sein, was das Maskieren von Ausgabespalten betrifft. In Szenarien, in denen die Spalten, auf die in einer UDF verwiesen wird, keine Ausgabespalten sind, werden sie nicht maskiert.

6. Wenn eine UDF auf eingebaute Funktionen wie SCOPE_IDENTITY()@@ROWCOUNT oder @@ERROR verweist, ändert sich der von der eingebauten Funktion zurückgegebene Wert mit Inlining. Diese Änderung im Verhalten ist darauf zurückzuführen, dass das Inlining den Gültigkeitsbereich von Anweisungen innerhalb der UDF ändert. Ab SQL Server 2019 (15.x) CU2 wird das Inlining blockiert, wenn die UDF auf bestimmte intrinsische Funktionen verweist (zum Beispiel @@ROWCOUNT).

See Also