Stored Procedures & T-SQL:

Was in der Datenbank passiert — und was besser draußen bleiben sollte

Stored Procedures polarisieren wie kaum ein anderes Thema in der SQL-Server-Welt. Die eine Fraktion schwört auf sie: "Alles in die Datenbank, da gehört die Logik hin." Die andere Fraktion verabscheut sie: "Spaghetti-Code im SQL Server, den niemand testen kann." Beide haben recht — und beide liegen falsch. Die Wahrheit liegt, wie so oft, in der sachlichen Betrachtung dessen, wofür Stored Procedures wirklich gut sind und, wofür nicht.

Was in diesem Kapitel zählt: Performance. Nicht Architektur-Philosophie. Nicht Schichtenmodelle. Sondern die Frage, ob deine T-SQL-Objekte dem SQL Server helfen oder ihn ausbremsen. Und da gibt es einige böse Überraschungen — besonders bei Scalar User-Defined Functions, die sich gerne als harmlose Helfer tarnen, während sie im Hintergrund die CPU-Kerne aufsaugen.

Verweis nach vorne: Die Themen Parameter Sniffing und Plan Cache werden in Kapitel 18 ausführlich behandelt. Dieses Kapitel greift speziell die Stored-Procedure-spezifischen Aspekte heraus. SARGability-Probleme durch UDFs in WHERE-Klauseln sind Thema in Kapitel 21.

Stored Procedures: Wann sie helfen, wann sie schaden

Stored Procedures haben drei echte Vorteile, die nicht wegzudiskutieren sind — und zwei Nachteile, die genauso real sind.

Die echten Vorteile

Plan-Caching: SQL Server kompiliert eine Stored Procedure beim ersten Aufruf und legt den Ausführungsplan im Plan Cache ab. Jeder weitere Aufruf mit gleichen Parametern nutzt diesen Plan direkt — keine Kompilierung, keine CPU-Last durch den Query Optimizer. Das ist kein kleines Detail: Ein komplexer Ausführungsplan kann 50–200 ms Kompilierzeit kosten. Bei 1.000 Aufrufen pro Minute ist das entweder 50–200 ms einmalig (Stored Procedure) oder 833–3.333 ms jede Sekunde (jedes Mal neu kompilieren). Dieser Unterschied bei der SQL Re-Compilations/sec-Metrik ist messbar — und er spielt direkt in die CPU-Gesamtlast ein.

Netzwerk-Effizienz: Statt 500 Zeilen T-SQL über das Netzwerk zu schicken, schickt die Anwendung nur "EXEC usp_GetKundenbestellung @KundeID = 4711". Das reduziert den Netzwerk-Traffic und — noch wichtiger — verhindert, dass die Anwendung komplexe SQL-Texte zusammenbasteln muss, was oft zu Plan Cache Pollution durch nicht-parametrisierten dynamischen SQL führt (dazu gleich mehr).

Security: Eine Stored Procedure kann mit EXECUTE-Recht aufgerufen werden, ohne, dass der Aufrufer SELECT/INSERT/UPDATE/DELETE auf die zugrundeliegenden Tabellen hat. Das Prinzip heißt "Ownership Chaining" und ist für sicherheitskritische Szenarien relevant — aber das ist ein Sicherheitsthema, kein Performance-Thema.

Die echten Nachteile

Enge Kopplung: Wenn die Anwendung und die Stored Procedures gemeinsam entwickelt werden, entsteht eine enge Kopplung zwischen Datenbankschema und Anwendungslogik. Schemaänderungen brechen Prozeduren, Prozedurinterfaces brechen Anwendungen. Das ist kein unlösbares Problem, aber ein echtes.

Testbarkeit und Versionierung: T-SQL in Stored Procedures ist schwer unit-testbar, und "die Prozedur liegt im SQL Server" ist kein Versionskontrollsystem. Wer seine Stored Procedures nicht in Git hat, hat sie eigentlich nirgendwo. Das führt zu der klassischen Situation: "Irgendwer hat da was geändert, aber niemand weiß mehr wann und warum."

 

Tipp: Stored Procedures für das richtige einsetzen
Stored Procedures sind gut für: Performance-kritische, häufig aufgerufene Datenbankoperationen, Operationen die Ownership Chaining für Security brauchen, und komplexe Multi-Statement-Logik die eng mit den Daten verzahnt ist.
Stored Procedures sind schlecht für: Business-Logik die auch in der Anwendung leben könnte, Komplexe Berechnungen die besser in der Anwendungsschicht stattfinden, und " alles muss in die DB " als religiöses Dogma.
Faustregel: Wenn eine Stored Procedure mehr als 200 Zeilen hat und Geschäftsregeln implementiert die im Lastenheft stehen, ist sie wahrscheinlich am falschen Ort.

 

Scalar User-Defined Functions: Der stille CPU-Killer

Scalar User-Defined Functions (Scalar UDFs) sind das vielleicht am häufigsten missverstandene Feature in T-SQL. Sie sehen harmlos aus — eine kleine Funktion, die einen Wert zurückgibt. Sauber gekapselt. Wiederverwendbar. Wartbar. Und in jedem Codebeispiel im Intranet der letzten zwanzig Jahre empfohlen.

Das Problem: SQL Server ruft eine Scalar UDF für jede einzelne Zeile im Ergebnis separat auf. Und damit nicht genug: Eine Scalar UDF verhindert Parallelismus im Ausführungsplan. Keine parallele Ausführung, keine Nutzung mehrerer CPU-Kerne — die Query läuft zwangsweise single-threaded durch. Das ist kein Bug, das ist by Design — und es macht Scalar UDFs in WHERE-Klauseln, JOIN-Bedingungen und SELECT-Listen auf großen Tabellen zur Performancefalle.

Der Sparfuchs-Fall: 360.000 Kontextwechsel pro Query

Bei Sparfuchs & Partner haben wir folgende Funktion gefunden (Kapitel 33 zeigt den vollständigen Befund):

-- Die harmlos aussehende Funktion bei Sparfuchs

-- Sie liefert den Steuersatz für ein Produkt zurück

CREATE FUNCTION dbo.fn_GetSteuersatz(@ProduktID INT)

RETURNS DECIMAL(5,2)

AS BEGIN

    DECLARE @Satz DECIMAL(5,2);

 

    -- Ein einzelner Lookup — was soll da schiefgehen?

    SELECT @Satz = Steuersatz

    FROM dbo.Steuerkategorien sk

    JOIN dbo.Produkte p ON p.SteuerkategorieID = sk.SteuerkategorieID

    WHERE p.ProduktID = @ProduktID;

 

    RETURN ISNULL(@Satz, 0.19);  -- 19% als Fallback

END;

Aufgerufen wurde diese Funktion so:

-- Die eigentliche Abfrage — schaut auf den ersten Blick normal aus

SELECT

    b.BestellungsID,

    b.Menge * p.Preis AS Nettobetrag,

    -- Hier wird die Funktion für JEDE ZEILE einzeln aufgerufen

    b.Menge * p.Preis * dbo.fn_GetSteuersatz(b.ProduktID) AS Steuerbetrag

FROM dbo.Bestellpositionen b

JOIN dbo.Produkte p ON p.ProduktID = b.ProduktID

WHERE b.BestellungsDatum >= DATEADD(MONTH, -3, GETDATE());

Die Tabelle Bestellpositionen hatte 360.000 Zeilen im relevanten Zeitraum. SQL Server rief fn_GetSteuersatz also 360.000 Mal auf — jedes Mal mit einem separaten Kontext-Wechsel zwischen dem Hauptplan und der Funktion. Das Ergebnis: 47 Sekunden Laufzeit. Kein Parallelismus, weil die UDF ihn verhinderte. 100% CPU-Auslastung auf einem Core.

Die Lösung war eine Inline Table-Valued Function (iTVF), die SQL Server als Teil des Hauptplans optimieren kann:

-- Lösung: Inline Table-Valued Function statt Scalar UDF

-- Eine iTVF ist genau das: eine einzelne SELECT-Anweisung, kein BEGIN/END

-- SQL Server behandelt sie wie eine parametrisierte View — vollständig inline

CREATE FUNCTION dbo.fn_GetSteuersatz_iTVF(@ProduktID INT)

RETURNS TABLE

AS

RETURN (

    SELECT sk.Steuersatz

    FROM dbo.Steuerkategorien sk

    JOIN dbo.Produkte p ON p.SteuerkategorieID = sk.SteuerkategorieID

    WHERE p.ProduktID = @ProduktID

);

-- Verwendung der iTVF mit CROSS APPLY

-- SQL Server optimiert das als eine einzige Gesamtabfrage

-- Parallelismus ist jetzt wieder möglich

SELECT

    b.BestellungsID,

    b.Menge * p.Preis AS Nettobetrag,

    b.Menge * p.Preis * st.Steuersatz AS Steuerbetrag

FROM dbo.Bestellpositionen b

JOIN dbo.Produkte p ON p.ProduktID = b.ProduktID

CROSS APPLY dbo.fn_GetSteuersatz_iTVF(b.ProduktID) st

WHERE b.BestellungsDatum >= DATEADD(MONTH, -3, GETDATE());

 

-- Ergebnis bei Sparfuchs: 47 Sekunden → 0,3 Sekunden

-- Faktor 157 — durch eine Funktionsart-Änderung, ohne einen Index anzufassen

In diesem konkreten Fall hätte man den Steuersatz auch direkt in den JOIN integrieren können — ohne jede Funktion. Aber die iTVF-Variante zeigt das Prinzip: SQL Server kann die iTVF in den Hauptplan einfalten (deshalb "inline"), den gesamten Ausführungsplan als Einheit optimieren, und parallele Verarbeitung aktivieren. Das Ergebnis: 0,3 Sekunden statt 47.

Scalar UDF Inlining ab SQL Server 2019

Ab SQL Server 2019 mit Kompatibilitätslevel 150 versucht der Query Processor Scalar UDFs automatisch zu inlinen — also in den Hauptplan einzufalten, ähnlich wie eine iTVF. Das funktioniert für eine Teilmenge von UDFs, die bestimmte Bedingungen erfüllen: keine Seiteneffekte, kein EXECUTE, keine rekursiven Aufrufe, kein Zugriff auf system-interne Objekte. Im Query Store (Kapitel 19) kann man sehen ob Inlining für eine bestimmte UDF greift.

Wichtig: Scalar UDF Inlining ist gut, aber keine Ausrede für neuen Code. Neue UDFs sollten von Anfang an als iTVF geschrieben werden, wenn sie in Abfragen eingesetzt werden.

 

Warnung: UDFs in WHERE-Klauseln — doppelt gefährlich
Ein Scalar UDF in der WHERE-Klausel hat zwei Probleme gleichzeitig: Er verhindert Parallelismus und er ist nicht sargable — SQL Server kann keinen Index nutzen, weil er den Rückgabewert der Funktion nicht vorab kennt. Das erzwingt einen Full Table Scan plus serielle Verarbeitung.
Ausführliche Erklärung zu SARGability-Killern in Kapitel 21. Die Kombination aus "kein Index" und "kein Parallelismus" ist das Schlechteste, was einer Abfrage passieren kann.

 

UDF-Performance überwachen mit sys.dm_exec_function_stats

-- Welche Funktionen kosten am meisten CPU?

-- sys.dm_exec_function_stats aggregiert Ausführungsstatistiken seit dem letzten Neustart

SELECT TOP 20

    OBJECT_NAME(object_id, database_id)         AS FunktionsName,

    DB_NAME(database_id)                         AS Datenbank,

    execution_count                              AS Aufrufe,

    total_worker_time / 1000                     AS CPU_Gesamt_ms,

    total_worker_time / execution_count / 1000   AS CPU_ProAufruf_ms,

    total_elapsed_time / execution_count / 1000  AS Dauer_ProAufruf_ms,

    -- Verhältnis CPU zu Laufzeit: nahe 1 = CPU-gebunden, niedrig = wartet auf IO

    CAST(total_worker_time * 1.0 / NULLIF(total_elapsed_time,0) AS DECIMAL(4,2)) AS CPU_Effizienz

FROM sys.dm_exec_function_stats

ORDER BY total_worker_time DESC;

Wenn eine Funktion sehr hohe CPU-Gesamt-Werte zeigt, ist sie ein Kandidat für die Umschreibung als iTVF oder — falls SQL 2019+ vorhanden — für die Überprüfung ob Scalar UDF Inlining greift.

Parameter Sniffing in Stored Procedures

Parameter Sniffing ist in Kapitel 18 ausführlich beschrieben. Hier die SP-spezifischen Aspekte: Eine Stored Procedure wird beim ersten Aufruf kompiliert — und der generierte Plan ist auf die Parameter des ersten Aufrufs zugeschnitten. Wenn dieser erste Aufruf untypische Parameter hatte, sitzt danach ein schlechter Plan im Cache, den alle folgenden Aufrufe erben.

Das Symptom ist klassisch: "Die Stored Procedure usp_GetBestellungen läuft manchmal in 0,1 Sekunden und manchmal in 45 Sekunden — und wir wissen nicht warum." Die Ursache ist fast immer Parameter Sniffing kombiniert mit einem nicht-repräsentativen Plan im Cache.

WITH RECOMPILE — das falsche Mittel

Die naheliegende Reaktion: WITH RECOMPILE auf die Prozedur setzen. "Dann wird sie immer neu kompiliert, und der Plan passt immer zu den aktuellen Parametern." Stimmt. Kostet aber auch bei jedem einzelnen Aufruf CPU für die Kompilierung — ohne Ausnahme, auch, wenn die Parameter völlig normal sind.

-- FALSCH: WITH RECOMPILE auf Prozedur-Ebene

-- Kompiliert BEI JEDEM AUFRUF die gesamte Prozedur neu

-- Bei 500 Aufrufen/Minute: 500 Kompilierungen/Minute, jede kostet CPU

CREATE PROCEDURE usp_GetBestellungen @KundeID INT

WITH RECOMPILE  -- das hier ist das Problem

AS BEGIN

    SELECT * FROM dbo.Bestellungen WHERE KundeID = @KundeID;

END;

Deutlich besser: OPTION (RECOMPILE) auf dem Statement, das tatsächlich das Problem hat. SQL Server kompiliert dann nur dieses Statement neu — und nur, wenn die Parameter sich signifikant geändert haben. Das reduziert den CPU-Aufwand erheblich.

-- RICHTIG: OPTION (RECOMPILE) auf Statement-Ebene

-- Nur das problematische Statement wird bei veränderten Parametern neu kompiliert

-- Alle anderen Statements in der Prozedur nutzen weiterhin den gecachten Plan

CREATE PROCEDURE usp_GetBestellungen @KundeID INT

AS BEGIN

    SET NOCOUNT ON;

 

    -- Diese Abfrage hat sehr unterschiedliche Datenmenge je nach KundeID

    -- (Großkunde: 50.000 Bestellungen, Kleinkunde: 3 Bestellungen)

    -- OPTION (RECOMPILE) erzeugt jedes Mal einen parameterspezifischen Plan

    SELECT b.BestellungsID, b.BestellungsDatum, b.Gesamtbetrag

    FROM dbo.Bestellungen b

    WHERE b.KundeID = @KundeID

    ORDER BY b.BestellungsDatum DESC

    OPTION (RECOMPILE);  -- nur dieses Statement, nicht die ganze Prozedur

END;

Lokale Variablen als Workaround — mit Vorbehalt

Ein verbreiteter Workaround: Den Parameter in eine lokale Variable kopieren und diese in der Abfrage verwenden. SQL Server kann den Wert einer lokalen Variable zur Kompilierungszeit nicht kennen und erstellt einen "Einheitsplan" basierend auf Statistiken — kein Sniffing mehr.

-- Workaround: Lokale Variablen verhindern Parameter Sniffing

-- Vorteil: Kein Sniffing, Plan basiert auf Statistik-Schätzungen

-- Nachteil: Plan ist für alle Parameterwerte ein Kompromiss — manchmal suboptimal

CREATE PROCEDURE usp_GetBestellungen @KundeID INT

AS BEGIN

    SET NOCOUNT ON;

    -- Parameter in lokale Variable kopieren

    DECLARE @KundeID_Lokal INT = @KundeID;

 

    -- SQL Server snifft @KundeID_Lokal nicht, schätzt stattdessen über Statistiken

    SELECT b.BestellungsID, b.BestellungsDatum

    FROM dbo.Bestellungen b

    WHERE b.KundeID = @KundeID_Lokal;

END;

Dieser Workaround löst Parameter Sniffing, aber er führt auch dazu, dass der Plan nie wirklich optimal ist — er ist der statistische Mittelwert. Bei sehr schiefer Datenverteilung kann das zu einem Plan führen, der für niemanden gut funktioniert. OPTION (RECOMPILE) ist in den meisten Fällen die bessere Wahl. Mehr dazu in Kapitel 18.

 

Praxisbeispiel: Musterwerk: usp_GetAuftragsliste
Bei Musterwerk GmbH (Kapitel 32) hatte die Prozedur usp_GetAuftragsliste ein klassisches Sniffing-Problem: Beim morgendlichen Aufwärmen des Systems rief die Monitoring-Software die Prozedur mit einer internen System-KundeID auf, die 0 Aufträge hat. Der daraus resultierende "leere Ergebnis"-Plan blieb im Cache — und alle echten Kundenaufrufe danach liefen damit in einen schlechten Plan mit Nested-Loop-Join statt Hash-Join auf 80.000 Zeilen.
Lösung: OPTION (RECOMPILE) auf dem problematischen Statement. Laufzeit: von sporadischen 12 Sekunden auf konsistente 0,08 Sekunden.

 

SET-Optionen: Die unsichtbaren Plan-Cache-Killer

SQL Server berücksichtigt bestimmte SET-Optionen beim Plan-Caching. Zwei Stored Procedures mit identischem T-SQL aber unterschiedlichen SET-Optionen beim Aufruf legen separate Pläne im Cache ab. Das klingt abstrakt, ist aber praktisch relevant: Ein .NET-Programm mit SqlConnection setzt standardmäßig andere SET-Optionen als ein SSMS-Fenster. Wenn der DBA eine Prozedur in SSMS testet und sie "schnell" ist, kann sie trotzdem für die Anwendung einen anderen Plan nutzen.

 

SET-Option	Empfehlung	Auswirkung bei Fehler
SET NOCOUNT ON	IMMER setzen	"x rows affected" für jedes Statement → Netzwerk-Traffic
SET ARITHABORT ON	Standard für .NET, explizit setzen	Anderer Plan-Cache-Slot als SSMS
SET ANSI_NULLS ON	Standard ON halten	NULL-Vergleiche verhalten sich anders
SET QUOTED_IDENTIFIER ON	Standard ON halten	Anführungszeichen-Semantik ändert sich
SET TRANSACTION ISOLATION LEVEL	Explizit setzen, wenn nötig	Unerwartetes Locking-Verhalten

SET-Optionen in Stored Procedures — Empfehlungen

 

SET NOCOUNT ON ist dabei die einfachste Optimierung: Ohne es schickt SQL Server nach jedem INSERT, UPDATE, DELETE und SELECT die Nachricht "x rows affected" zum Client zurück. Bei einer Prozedur mit fünf Statements sind das fünf extra Netzwerk-Roundtrips. Bei 10.000 Aufrufen pro Minute summiert sich das zu echtem Traffic — und die Applikation muss diese Nachrichten empfangen und verwerfen. Kein Argument gegen SET NOCOUNT ON.

 

Hinweis: Inkonsistente SET-Optionen → Recompilations → CPU

Wenn SET-Optionen zwischen Aufrufen variieren, findet SQL Server keinen passenden Plan im Cache und kompiliert neu. Das ist eine der unsichtbaren Quellen für erhöhte SQL Re-Compilations/sec in sys.dm_os_performance_counters. Erhöhte Kompilierungsrate bedeutet CPU-Last, die nicht durch teure Abfragen verursacht wird — sie erscheint als diffuse CPU-Erhöhung ohne offensichtliche Einzelverursacher. Das Kapitel 16 behandelt Recompilations als CPU-Thema ausführlicher.

 

Fehlerbehandlung mit TRY/CATCH — das robuste Template

Fehlerbehandlung in Stored Procedures ist keine Kür. Eine Prozedur ohne TRY/CATCH, die einen Fehler wirft, kann eine laufende Transaktion in einem undefinierten Zustand hinterlassen. Das folgende Template zeigt das robuste Grundmuster:

-- Robustes Stored-Procedure-Template mit korrekter Fehlerbehandlung

CREATE OR ALTER PROCEDURE usp_BestellungAbschliessen

    @BestellungsID  INT,

    @BenutzerID     INT

AS BEGIN

    SET NOCOUNT ON;

    SET XACT_ABORT ON;  -- bei Fehler: Transaktion automatisch zurückrollen

 

    BEGIN TRY

        BEGIN TRANSACTION;

 

            -- Bestellung als abgeschlossen markieren

            UPDATE dbo.Bestellungen

            SET    Status = 'Abgeschlossen',

                   AbschlussZeit = GETUTCDATE(),

                   BearbeitetVon = @BenutzerID

            WHERE  BestellungsID = @BestellungsID;

 

            IF @@ROWCOUNT = 0

                THROW 50001, 'Bestellung nicht gefunden.', 1;

 

            -- Historien-Eintrag anlegen

            INSERT INTO dbo.Bestellungshistorie (BestellungsID, Aktion, ZeitStempel)

            VALUES (@BestellungsID, 'Abgeschlossen', GETUTCDATE());

 

        COMMIT TRANSACTION;

 

    END TRY

    BEGIN CATCH

        -- XACT_STATE() prüfen, bevor ROLLBACK aufgerufen wird

        -- -1 = nicht-commitfähige Transaktion, 1 = commitfähig, 0 = keine Transaktion

        IF XACT_STATE() <> 0

            ROLLBACK TRANSACTION;

 

        -- Fehler mit allen relevanten Informationen weitergeben

        THROW;  -- re-wirft den originalen Fehler mit Original-Fehlernummer

    END CATCH;

END;

SET XACT_ABORT ON ist dabei besonders wichtig: Es sorgt dafür, dass bei einem Fehler die gesamte Transaktion automatisch zurückgerollt wird — auch, wenn der Fehler in einer aufgerufenen Prozedur auftritt und dort nicht gefangen wird. Ohne XACT_ABORT kann eine Transaktion im Zustand "offen und nicht-commitfähig" hängen bleiben, was zu Locking-Problemen führt (ausführlich in Kapitel 14).

 

Tipp: THROW statt RAISERROR für neue Prozeduren
THROW (ab SQL Server 2012) wirft den originalen Fehler mit der originalen Fehlernummer weiter — wichtig für Fehler-Tracking. RAISERROR erzeugt immer Fehlernummer 50000 oder eine benutzerdefinierte Nummer, was Fehler-Monitoring erschwert.
RAISERROR mit NOWAIT ist weiterhin sinnvoll für Fortschrittsmeldungen bei langen Batch-Operationen — es liefert die Nachricht sofort an den Client, ohne auf den Batch-Abschluss zu warten. Praktisch bei Wartungsprozeduren die den Fortschritt melden sollen.

 

Dynamisches SQL: sp_executesql statt EXEC

Dynamisches SQL ist manchmal unvermeidlich. Die Wahl zwischen EXEC und sp_executesql hat aber erhebliche Performance-Konsequenzen — und eine Sicherheitskonsequenz, die niemand ignorieren sollte.

-- FALSCH: EXEC mit String-Konkatenation

-- Jeder Aufruf mit anderem @StatusFilter erzeugt einen neuen Plan im Cache

-- Ergebnis: Tausende von Single-Use-Plänen = Plan Cache Pollution

-- Dazu: SQL-Injection möglich, wenn @StatusFilter aus Benutzereingabe kommt

DECLARE @StatusFilter NVARCHAR(50) = 'Aktiv';

DECLARE @SQL NVARCHAR(500);

 

SET @SQL = 'SELECT * FROM dbo.Auftraege WHERE Status = ''' + @StatusFilter + '''';

EXEC (@SQL);  -- jedes Mal ein neuer Plan, kein Cache-Treffer möglich

-- RICHTIG: sp_executesql mit Parametern

-- Der Plan wird gecacht und bei identischer Abfragestruktur wiederverwendet

-- @StatusFilter ist ein echter Parameter — kein SQL-Injection-Risiko

DECLARE @StatusFilter NVARCHAR(50) = 'Aktiv';

DECLARE @SQL NVARCHAR(500);

DECLARE @ParamDef NVARCHAR(200);

 

-- Abfragetext mit Parameter-Platzhalter

SET @SQL = N'SELECT * FROM dbo.Auftraege WHERE Status = @Status';

 

-- Parameterdefinition: Name und Typ

SET @ParamDef = N'@Status NVARCHAR(50)';

 

-- Ausführung mit echtem Parameter — Plan wird gecacht und wiederverwendet

EXEC sp_executesql @SQL, @ParamDef, @Status = @StatusFilter;

Der Unterschied im Plan Cache ist messbar: Mit EXEC und String-Konkatenation landet für "Status = Aktiv", "Status = Inaktiv", "Status = Archiviert" je ein separater Einzel-Plan im Cache. Mit sp_executesql liegt ein einziger Plan im Cache, der für alle drei Aufrufe genutzt wird. Bei einem System mit 500 verschiedenen Filterwerten sind das 500 Pläne statt einem — der Plan Cache füllt sich mit Einwegplänen (Single-Use Plans), der Buffer Pool verliert Speicher an den Plan Cache, und SQL Server muss häufiger kompilieren. Das ist ein direkter CPU-Kostenfaktor.

sp_executesql ist aus Performance-Sicht gleichwertig mit einer Stored Procedure: Beide nutzen parametrisierte Ausführung, beide cachen Pläne effizient. Das räumt mit dem Mythos auf, dass Stored Procedures immer "schneller" seien als dynamisches SQL — korrekt parametrisiertes dynamisches SQL via sp_executesql ist genauso schnell.

T-SQL Anti-Patterns: Die Checkliste

Die folgende Tabelle fasst die häufigsten T-SQL Anti-Patterns zusammen, die in Stored Procedures auftauchen — mit direkten Verweisen auf die Kapitel, wo die jeweilige Lösung detailliert erklärt wird.

 

Anti-Pattern	Problem	Lösung	Kapitel
SELECT * in Stored Procedures	Überträgt nicht benötigte Spalten, verhindert Covering Indexes, kann bei Schema-Änderungen brechen	Explizit benennen welche Spalten gebraucht werden	Kap 23
Scalar UDF in WHERE/SELECT/JOIN	Kein Parallelismus möglich, kein Index nutzbar, Zeilenweise Ausführung	Inline Table-Valued Function (iTVF) oder berechnete Spalte	Kap 21, Kap 26
Cursor für Zeilenweise Verarbeitung	Faktor 10–1000 langsamer als set-basierte Operationen auf großen Mengen	Mengenbasierter Ansatz mit JOINs, CTEs, Window Functions	Kap 23
Tabellenvariablen für große Datenmengen	Keine Statistiken, immer Kardinalitätsschätzung = 1, suboptimale Pläne	Temporäre Tabellen (#tmp) mit Indizes und Statistiken	Kap 13
EXEC mit String-Konkatenation	Plan Cache Pollution, SQL-Injection-Risiko, hohe Kompilierungsrate	sp_executesql mit Parametern	Kap 26
WITH RECOMPILE auf Prozedur-Ebene	Kompiliert bei jedem Aufruf alles neu, CPU-Last ohne Nutzen für normale Aufrufe	OPTION (RECOMPILE) nur auf problematischen Statements	Kap 18, Kap 26
Fehlendes SET NOCOUNT ON	"Rows affected"-Nachrichten pro Statement → Netzwerk-Traffic	SET NOCOUNT ON am Anfang jeder Prozedur	Kap 26
Keine TRY/CATCH-Behandlung	Offene Transaktionen bei Fehlern → Locking-Probleme	TRY/CATCH mit XACT_STATE()-Prüfung vor ROLLBACK	Kap 14, Kap 26

T-SQL Anti-Patterns in Stored Procedures — Übersicht

 

Diagnose: Wenn Stored Procedures und T-SQL zur Last werden

 

Hinweis: Symptome
Hohe CPU-Last ohne erkennbare teure Einzelabfragen: SQL Re-Compilations/sec deutlich erhöht (Baseline aus Kapitel 9 prüfen). Das kann durch Scalar UDFs, dynamisches SQL ohne Parametrierung oder inkonsistente SET-Optionen verursacht sein.
Eine Stored Procedure läuft mal in 0,1 Sekunden, mal in 30 Sekunden: Klassisches Parameter-Sniffing-Symptom. Nutzer berichten über "sporadisch langsame" Operationen, die sich nicht reproduzieren lassen.
Plan Cache füllt sich schnell: sys.dm_exec_cached_plans zeigt viele Einträge mit usecounts = 1 (Single-Use Plans). Das deutet auf nicht-parametrisierten dynamischen SQL hin.
sys.dm_exec_function_stats zeigt hohe total_worker_time für bestimmte Funktionen: Scalar UDFs die für sehr viele Zeilen aufgerufen werden.

 

 

Hinweis: So misst du das
Scalar UDF CPU-Verbrauch ermitteln:

 

-- Top 10 CPU-intensivste Funktionen seit dem letzten Neustart

-- Gibt einen klaren Überblick welche UDFs Optimierungsbedarf haben

SELECT TOP 10

    DB_NAME(database_id)                          AS Datenbank,

    OBJECT_NAME(object_id, database_id)           AS Funktion,

    execution_count                               AS AufrufAnzahl,

    total_worker_time / 1000                      AS CPU_Gesamt_ms,

    total_worker_time / execution_count / 1000    AS CPU_ProAufruf_ms,

    total_elapsed_time / execution_count / 1000   AS Laufzeit_ProAufruf_ms

FROM sys.dm_exec_function_stats

WHERE execution_count > 0

ORDER BY total_worker_time DESC;

 

-- Plan Cache auf Single-Use-Pläne untersuchen

-- Hohe Anzahl: viel dynamisches SQL ohne Parametrierung im Einsatz

SELECT

    COUNT(*)                  AS SingleUsePlaene,

    SUM(size_in_bytes) / 1024 AS Speicher_KB

FROM sys.dm_exec_cached_plans

WHERE usecounts = 1

  AND objtype = 'Adhoc';

 

-- Recompilations pro Sekunde aus Performance Counters

SELECT

    instance_name,

    cntr_value AS RecompilationsProSekunde

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Re-Compilations/sec'

  AND object_name LIKE '%SQL Statistics%';

 

Warnung: Typische Fehlinterpretationen
"Stored Procedures sind immer schneller als dynamisches SQL" — falsch. sp_executesql mit korrekter Parametrierung ist aus Plan-Cache-Sicht identisch mit einer Stored Procedure. Der Vorteil von Stored Procedures liegt im Deployment und Security, nicht in der reinen Ausführungsgeschwindigkeit.
"WITH RECOMPILE auf der Prozedur löst Parameter Sniffing" — stimmt, aber zu einem hohen Preis: Jeder Aufruf kostet CPU für die Kompilierung. Bei 1.000 Aufrufen/Minute und 10 ms Kompilierungszeit sind das 10 Sekunden CPU-Zeit pro Minute — nur für Kompilierungen. OPTION (RECOMPILE) auf dem problematischen Statement ist fast immer besser.
"Scalar UDFs sind sauber und wartbar, also sind sie gut" — sauber ja, wartbar ja, CPU-effizient nein. Sauberkeit im Code und Performance sind unterschiedliche Dimensionen. Eine iTVF kann genauso sauber und wartbar sein — und ist für Abfragen deutlich schneller.
"Hohe SQL Re-Compilations/sec bedeutet schlechte Stored Procedures" — nicht zwingend. Es kann auch nicht-parametrisierter Ad-hoc-SQL aus der Anwendung sein, oder ein Object-Schema das sich häufig ändert. sys.dm_exec_query_stats hilft beim Eingrenzen.

 

 

Tipp: Erste Gegenmaßnahmen
1. sys.dm_exec_function_stats abfragen: Welche Scalar UDFs verbrauchen am meisten CPU? Top-3 identifizieren und prüfen ob sie als iTVF umschreibbar sind.
2. Plan Cache auf Single-Use Plans prüfen: Wenn viele Einträge mit usecounts = 1 vorhanden sind, nach nicht-parametrisiertem dynamischen SQL suchen. sys.dm_exec_sql_text hilft beim Identifizieren der Verursacher.
3. SQL Re-Compilations/sec beobachten: Ist die Rate über dem Baseline-Wert aus Kapitel 9? SET-Optionen prüfen, OPTION (RECOMPILE) auf problematischen Statements evaluieren.
4. Für bekannte Parameter-Sniffing-Probleme: OPTION (RECOMPILE) auf dem spezifischen Statement setzen — nicht WITH RECOMPILE auf die gesamte Prozedur.
5. Query Store (Kapitel 19) aktivieren um Plan-Regressionen automatisch zu erkennen — auch Regressionen die durch Kompilierungsrate-Änderungen entstehen.

 

Zusammenfassung

Stored Procedures sind kein Allheilmittel und kein Teufelszeug. Sie sind ein Werkzeug, das für Performance-kritische, häufig aufgerufene Datenbankoperationen gut geeignet ist — und für Business-Logik-Container weniger.

Der größte Performance-Killer in T-SQL sind Scalar User-Defined Functions in Abfragen. Sie erzwingen zeilenweise Ausführung, verhindern Parallelismus und sind nicht sargable. Die Lösung ist fast immer eine Inline Table-Valued Function — syntaktisch ähnlich, semantisch völlig anders, und in der Praxis um Faktoren schneller. Das Sparfuchs-Beispiel (fn_GetSteuersatz, 360.000 Aufrufe, 47 Sekunden → 0,3 Sekunden) illustriert das eindringlich.

Parameter Sniffing in Stored Procedures löst man mit OPTION (RECOMPILE) auf dem problematischen Statement — nicht mit WITH RECOMPILE auf Prozedur-Ebene, das jeden Aufruf teurer macht. SET NOCOUNT ON gehört in jede Prozedur, TRY/CATCH mit XACT_STATE()-Prüfung und SET XACT_ABORT ON ist das robuste Grundmuster für transaktionale Operationen.

Dynamisches SQL braucht sp_executesql mit echten Parametern — nicht EXEC mit String-Konkatenation. Der Unterschied ist messbar im Plan Cache und in der CPU-Last.

Das nächste Kapitel verlässt die Datenbankebene und schaut auf das, was Transaktionen wirklich kompliziert macht: Isolationslevel. Kapitel 27 erklärt die fünf SQL-Standard-Isolationslevel, ihre Nebenwirkungen, und warum die Wahl des falschen Levels entweder zu Dirty Reads oder zu unerklärlichem Blocking führt — beides gleich unangenehm.

 

Abb. 1: Scalar UDF vs. Inline Table-Valued Function

 

Kapitel 27