Das Paradox der Abstraktion in SQL Server

Für SQL Server ist der Quelltext aber nur die Beschreibung eines Ergebnisses. Wie er es ermittelt, entscheidet der Optimizer, und der sieht Ihre Kapselung anders als Sie. Daraus entsteht ein Paradox:

• Zu wenig sichtbar: Eine Funktion kann ihre Logik vor dem Optimizer verbergen. Er kann die Arbeit darin weder schätzen noch umstellen, ruft sie im ungünstigen Fall für jede Zeile einzeln auf und verzichtet auf Parallelität.
• Zu viel sichtbar: Eine View wird vor der Optimierung aufgelöst. Drei Zeilen SQL auf einer tief verschachtelten View werden so zu einer Abfrage mit Dutzenden oder Hunderten Tabellenbezügen. Im ungünstigen Fall dauert allein die Suche nach einem Plan länger als seine Ausführung.

Beides hat dieselbe Ursache: Die Struktur Ihres Codes und die Struktur des Ausführungsplans sind zwei verschiedene Dinge. Diese Seite zeigt, woran Sie die beiden Seiten des Paradoxes erkennen, und führt zu den Vertiefungen mit Messungen auf SQL Server 2017 bis 2025. Sie benötigen dafür kein PSG-Werkzeug. Eine Schicht darüber, zwischen Anwendung und Datenbank, stellt sich dieselbe Frage bei Entity Framework; dazu gibt es die Themenseite Entity Framework & SQL Server.

Drei Fragen statt einer

„Ist die Abfrage schnell?“ trennt nicht, wo das Problem liegt. Hilfreicher sind drei Fragen:

Eine Abfrage kann billig auszuführen und trotzdem schwer zu optimieren sein. Umgekehrt findet der Optimizer manchmal schnell einen Plan, der wegen fehlender Information schlecht ist. Die Vertiefungen zeigen beide Fälle am Ausführungsplan.

Die Vertiefungen

Welche Beobachtung passt zu Ihnen?

Inventur: Wo verbirgt oder vervielfacht Ihre Datenbank Arbeit?

Die Abfrage listet in der aktuellen Datenbank drei Dinge: Skalar-Funktionen, die tatsächlich Zeile für Zeile ausgeführt wurden, alle T-SQL-Funktionen mit ihrer Inlining-Eignung und die Views mit ihrer Verschachtelungstiefe. Sie ist ein Startpunkt für die Suche, kein Urteil: Eine tiefe View oder eine Skalar-Funktion ist erst dann ein Problem, wenn der Ausführungsplan es zeigt.

Abfrage: Inventur Funktionen und Views

/* PSG · Das Paradox der Abstraktion · Abfrage 1   Inventur: Welche Funktionen und Views verbergen oder vervielfachen Arbeit?    - Nur lesend. Ändert nichts.   - In der zu prüfenden Benutzerdatenbank ausführen (nicht in master).   - Liefert drei Ergebnisse:       1 Skalar-Funktionen, die tatsächlich zeilenweise ausgeführt wurden (seit dem letzten Neustart bzw. Plan-Cache-Eintrag)       2 Alle T-SQL-Funktionen mit Typ und, ab SQL Server 2019, Inlining-Eignung       3 Views mit Verschachtelungstiefe und Zahl der Tabellenbezüge nach dem Auflösen   - Rechte: VIEW DEFINITION auf die Objekte; für Ergebnis 1 zusätzlich VIEW SERVER STATE     (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).   - Ab SQL Server 2017. Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025.*/ /* 1 · Skalar-Funktionen mit zeilenweiser Ausführung   sys.dm_exec_function_stats zeigt nur Skalar-Funktionen, die als eigener Aufruf liefen.   Inlinierte Skalar-Funktionen und Tabellenwertfunktionen erscheinen hier nicht:   Fehlt eine Funktion, heißt das nicht, dass sie keine Arbeit verursacht. */SELECT TOP (25)    OBJECT_SCHEMA_NAME(fs.object_id, fs.database_id) AS schema_name,    OBJECT_NAME(fs.object_id, fs.database_id)        AS funktion,    fs.execution_count                               AS aufrufe,    fs.total_worker_time / 1000                      AS cpu_ms_gesamt,    fs.total_elapsed_time / 1000                     AS dauer_ms_gesamt,    fs.total_logical_reads                           AS logische_lesevorgaenge,    CAST(fs.total_worker_time / 1000.0 / NULLIF(fs.execution_count, 0) AS decimal(12,3)) AS cpu_ms_je_aufruf,    fs.cached_time                                   AS im_cache_seitFROM sys.dm_exec_function_stats AS fsWHERE fs.database_id = DB_ID()ORDER BY fs.total_worker_time DESCOPTION (MAXDOP 1); /* 2 · T-SQL-Funktionen mit Typ und Inlining-Eignung   FN = Skalar-Funktion, IF = Inline-Tabellenwertfunktion, TF = Multi-Statement-Tabellenwertfunktion.   inlinefaehig gilt nur für FN und erst ab SQL Server 2019 (sonst NULL).   Eignung heißt nicht, dass ein konkreter Aufruf inliniert wird: Das zeigt nur der tatsächliche Plan. */IF COL_LENGTH('sys.sql_modules', 'is_inlineable') IS NOT NULL    EXEC (N'    SELECT  SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS funktion, o.type AS typ, o.type_desc,            CASE WHEN o.type = ''FN'' THEN m.is_inlineable END AS inlinefaehig,            (SELECT CAST(c.value AS int) FROM sys.database_scoped_configurations AS c              WHERE c.name = ''TSQL_SCALAR_UDF_INLINING'') AS inlining_in_db_an    FROM sys.objects AS o JOIN sys.sql_modules AS m ON m.object_id = o.object_id    WHERE o.type IN (''FN'', ''IF'', ''TF'')    ORDER BY o.type, schema_name, funktion    OPTION (MAXDOP 1);');ELSE    SELECT  SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS funktion, o.type AS typ, o.type_desc,            CAST(NULL AS bit) AS inlinefaehig    FROM sys.objects AS o    WHERE o.type IN ('FN', 'IF', 'TF')    ORDER BY o.type, schema_name, funktion    OPTION (MAXDOP 1); /* 3 · Views: Verschachtelungstiefe und was dahintersteht   tiefe                 = längste Kette View -> View -> ... bis zur Tabelle   views_darunter        = wie viele verschiedene Views beim Auflösen mitkommen   verschiedene_tabellen = wie viele unterschiedliche Tabellen dahinterstehen   funktionsbezuege      = verschiedene Funktionen in der Kette   Grenze: Die Metadaten führen jeden Bezug nur einmal. Nutzt eine View eine andere zweimal   (z. B. per Self-Join), sieht man das erst im Ausführungsplan.   Die Rekursion endet nach 32 Ebenen; Funktionen werden als Endpunkt gezählt, nicht aufgelöst. */WITH kanten AS (    SELECT DISTINCT d.referencing_id, d.referenced_id, o.type AS ziel_typ    FROM sys.sql_expression_dependencies AS d    JOIN sys.views   AS v ON v.object_id = d.referencing_id    JOIN sys.objects AS o ON o.object_id = d.referenced_id    WHERE d.referenced_id IS NOT NULL      AND o.type IN ('U', 'V', 'IF', 'TF', 'FN')), aufgeloest AS (    SELECT k.referencing_id AS view_id, k.referenced_id AS ziel_id, k.ziel_typ, 1 AS ebene    FROM kanten AS k    UNION ALL    SELECT a.view_id, k.referenced_id, k.ziel_typ, a.ebene + 1    FROM aufgeloest AS a    JOIN kanten AS k ON k.referencing_id = a.ziel_id    WHERE a.ziel_typ = 'V' AND a.ebene < 32)SELECT TOP (50)    OBJECT_SCHEMA_NAME(a.view_id)                                  AS schema_name,    OBJECT_NAME(a.view_id)                                         AS view_name,    MAX(a.ebene)                                                   AS tiefe,    COUNT(DISTINCT CASE WHEN a.ziel_typ = 'V' THEN a.ziel_id END)  AS views_darunter,    COUNT(DISTINCT CASE WHEN a.ziel_typ = 'U' THEN a.ziel_id END)  AS verschiedene_tabellen,    COUNT(DISTINCT CASE WHEN a.ziel_typ IN ('IF', 'TF', 'FN') THEN a.ziel_id END) AS funktionsbezuegeFROM aufgeloest AS aGROUP BY a.view_idORDER BY tiefe DESC, verschiedene_tabellen DESCOPTION (MAXDOP 1, MAXRECURSION 100);

So lesen Sie das Ergebnis:

• Ergebnis 1 nennt die Skalar-Funktionen, die als eigener Aufruf liefen, mit Aufrufzahl und CPU-Zeit, seit ihr Plan im Cache liegt. Viele Aufrufe mit wenig CPU je Aufruf sind das typische Bild einer Funktion, die je Zeile einer großen Abfrage läuft. Die Aufrufzahl ist nicht immer die Zeilenzahl: Im Lab stand dieselbe Funktion zweimal im Plan, und für 1.000 Bestellungen zählte die Abfrage 2.000 Aufrufe. Inlinierte Funktionen fehlen hier, ebenso Tabellenwertfunktionen. Nach einem Neustart oder wenn der Plan aus dem Cache fällt, ist die Liste leer.
• Ergebnis 2 listet alle T-SQL-Funktionen. inlinefaehig = 1 heißt nur, dass SQL Server die Funktion einbetten kann. Ob ein bestimmter Aufruf es tut, zeigt der tatsächliche Plan. inlining_in_db_an zeigt, ob das Inlining in dieser Datenbank eingeschaltet ist. Auf SQL Server 2017 bleibt inlinefaehig leer, weil es dort kein Inlining gibt.
• Ergebnis 3 zeigt die 50 am tiefsten verschachtelten Views mit den Tabellen dahinter. Eine View mit Tiefe 6 und 20 Tabellen ist ein Kandidat, um ihre Abfragen im Plan anzusehen. Ob eine View eine andere mehrfach verwendet, verraten die Metadaten nicht; das zeigt erst der Plan.

Ein Ausschnitt von Ergebnis 3 aus der Lab-Datenbank der Vertiefung Verschachtelte Views:

vw_L16 und vw_S16 sind Ketten aus 16 Views über 17 bzw. 18 Tabellen; die Inventur zeigt sie richtig als tief und breit. vw_R9 sieht mit zwei Tabellen harmlos aus. Tatsächlich verwendet jede ihrer neun Ebenen die darunterliegende zweimal, und im Plan steht die Bestelltabelle 256-mal. Eine schmale Abfrage darauf kompilierte im Lab über drei Minuten. Genau diesen Fall kann die Inventur nicht sehen.

Vier Annahmen, die nicht stimmen

„Eine Funktion wird einmal ausgeführt.“ Eine Skalar-Funktion ohne Inlining läuft je Zeile, die sie berechnen muss. In einer Abfrage über 100.000 Bestellungen sind das bis zu 100.000 Aufrufe, jeder mit eigenem Aufwand.

„Eine View ist vorberechnet.“ Eine gewöhnliche View speichert nur ihre Definition. Bei jeder Abfrage wird sie aufgelöst und gemeinsam mit allen Views darunter optimiert. Ergebnisse speichert nur eine indizierte View.

„Inlinefähig heißt inliniert.“ Ab SQL Server 2019 kann der Optimizer viele Skalar-Funktionen in die aufrufende Abfrage einbetten. Ob das geschieht, hängt von der Funktion, vom Aufruf, vom Kompatibilitätsgrad und von der Konfiguration ab.

„Kurzer SQL-Text, einfacher Plan.“ Die Länge der Abfrage sagt nichts über die Größe der Aufgabe, die der Optimizer nach dem Auflösen aller Views lösen muss.

Was gekapselten Code schnell hält

• Abstraktion behalten, aber die richtige wählen. Eine Inline-Tabellenwertfunktion kapselt Logik, ohne sie zu verbergen: Der Optimizer behandelt sie wie eine View mit Parametern. Wann sie die bessere Wahl ist, zeigt T-SQL-Funktionen.
• Am Plan prüfen, nicht am Quelltext. Ob eine Funktion eingebettet wurde, ob eine View Joins verloren hat, ob der Optimizer vorzeitig aufgegeben hat: Das alles steht im tatsächlichen Ausführungsplan.
• Views flach halten. Eine Reporting-View, die auf drei anderen aufbaut, ist schnell geschrieben und lange teuer. Wie Fremdschlüssel dem Optimizer helfen, unnötige Joins wieder zu entfernen, zeigt Verschachtelte Views.
• Bei jedem Upgrade neu prüfen. Neue Versionen ändern, was der Optimizer mit Funktionen anfängt. Das hilft oft, aber nicht immer. Gemessen wird am eigenen Code.
• Den Blick weiten. Ist der Server insgesamt langsam, führt der SQL Server Performance Ratgeber durch die passenden Diagnosewege.

Quellen

• Microsoft: Scalar UDF Inlining
• Microsoft: Interleaved Execution für Multi-Statement-Tabellenwertfunktionen
• Microsoft: sys.dm_exec_function_stats
• Microsoft: Optimizer-Timeouts untersuchen
• Microsoft: Views