T-Lift: Dynamisches SQL, ohne es von Hand zu schreiben.

T-Lift rendert eine Catch-all-Prozedur

Das Problem: eine Abfrage für alle Fälle

Suchmasken mit vielen optionalen Filtern landen in SQL Server oft in diesem Muster:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerID INT = NULL,
    @Status NVARCHAR(20) = NULL
AS
SELECT o.OrderID, o.CustomerID, o.OrderDate, o.Status
FROM dbo.Orders o
WHERE (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
  AND (@Status IS NULL OR o.Status = @Status)

Das Ergebnis stimmt immer. Der Optimierer muss aber jede Kombination aus gesetzten und leeren Parametern gleichzeitig abdecken. Welcher Plan im Cache landet, entscheidet der erste Aufruf. Ein Plan für die Suche nach einem Kunden wird dann auch für die Abfrage ohne Filter wiederverwendet, und die Lesezugriffe können sich um Größenordnungen unterscheiden. Das ist Parameter Sniffing. Es ist kein Fehler, sondern die Arbeitsweise des Plan-Caches. Einen Index, der das strukturell behebt, gibt es nicht; das Problem liegt in der Formulierung der Abfrage.

Wie Parameter Sniffing im Plan aussieht, mit echten Messwerten →

Die bekannten Auswege und ihr Preis

Wie T-Lift arbeitet

Sie schreiben Ihre Prozedur wie gewohnt und ergänzen Direktiven als Kommentare (--#). Weil es nur Kommentare sind, bleibt die Prozedur voll lauffähig: Sie entwickeln, testen und debuggen wie immer in SSMS. Wenn alles passt, rendert T-Lift daraus die dynamische Fassung.

                                        --#[
SELECT o.OrderID, o.CustomerID, o.OrderDate, o.Status
FROM dbo.Orders o
WHERE                                   --#if @CustomerID IS NOT NULL OR @Status IS NOT NULL
(                                       --#-
@CustomerID IS NULL OR                  --#-
o.CustomerID = @CustomerID              --#if @CustomerID IS NOT NULL
)                                       --#-
AND                                     --#if @CustomerID IS NOT NULL AND @Status IS NOT NULL
(                                       --#-
@Status IS NULL OR                      --#-
o.Status = @Status                      --#if @Status IS NOT NULL
)                                       --#-
                                        --#]

Das Ergebnis enthält nur noch die Bedingungen, die im jeweiligen Aufruf tatsächlich gebraucht werden. Jede Parameterkombination bekommt ihren eigenen, wiederverwendbaren Plan:

IF @CustomerID IS NOT NULL
    SET @sql = @sql + 'o.CustomerID = @CustomerID' + CHAR(13)+CHAR(10)
...
EXEC sp_executesql @sql,
     N'@CustomerID int, @Status nvarchar(20)', @CustomerID, @Status

Was darüber hinaus bemerkenswert ist

• Immer parametrisiert.
  T-Lift erzeugt sp_executesql mit einer korrekt typisierten Parameterliste aus sys.parameters. Parameterwerte werden nie in die SQL-Zeichenkette eingebaut. Das ist sicherer als viel handgeschriebenes dynamisches SQL.
• Buckets für den Plan-Cache.
  Mit --#buckets bekommt jeder Wertebereich eines Parameters einen eigenen Plan, etwa kleine gegenüber großen Beträgen.
• Sicheres dynamisches ORDER BY.
  --#sort erlaubt nur freigegebene Spalten. Die klassische Einfallstür für SQL-Injection beim Sortieren bleibt zu.
• Hilfe für Altbestände.
  @suggest = 1 durchsucht bestehende Prozeduren nach Catch-all-Mustern und schlägt passende Annotationen vor.
• Kein vergessenes Rendering.
  Jede gerenderte Prozedur trägt einen Stempel mit Quell-Hash. @checkDrift meldet, wenn die Quelle sich geändert hat und die gerenderte Fassung veraltet ist.
• Prüfen vor dem Rendern.
  @validateOnly findet offene Klammern, Tippfehler in Direktiven und Bedingungen außerhalb einer Sektion.

EXEC dbo.sp_tlift
    @DatabaseName  = 'IhreDatenbank',
    @ProcedureName = 'AlteProzedur',
    @suggest       = 1;   -- findet Catch-all-Muster und schlägt Annotationen vor

Grenzen, die Sie kennen sollten

• Berechtigungen: Wie jedes dynamische SQL unterbricht T-Lift die Besitzverkettung. Benutzer brauchen Leserechte auf die Tabellen, oder die Prozedur läuft mit EXECUTE AS OWNER oder per Modulsignatur.
• Nicht immer schneller: T-Lift lohnt sich vor allem bei wiederkehrenden, häufig ausgeführten Abfrageformen, bei denen die Kompilierkosten von OPTION (RECOMPILE) ins Gewicht fallen. Es ist kein Allheilmittel.
• Mindestversion: SQL Server 2017, ohne CLR und ohne externe Abhängigkeiten.

Ausprobieren

Laden Sie sp_tlift.sql aus dem Repository und installieren Sie es in einer eigenen Hilfsdatenbank. EXEC dbo.sp_tlift @help = 1; erklärt alle Optionen. Das Repository enthält außerdem Demos mit realistischen Geschäftsabfragen und ein Skript, das T-Lift und OPTION (RECOMPILE) anhand von Query-Store-Daten vergleicht.

Als Nächstes geplant sind unter anderem Bucket-Grenzen aus Statistik-Histogrammen und eine Rückkopplung mit dem Query Store, die Plan-Regressionen gerenderter Prozeduren meldet. Fragen, Ideen und Beiträge sind über GitHub-Issues willkommen. Eine ausführlichere englische Einführung mit Hintergründen steht auf Medium.