Statistiken, Cardinality Estimator & Plan Regression:

Wenn der Optimizer in der Vergangenheit lebt

Stell dir vor, du planst eine Autofahrt von Köln nach München. Du tippst die Route ins Navi, das dir selbstsicher sagt: "In 4 Stunden 12 Minuten am Ziel." Nur, dass das Navi mit Kartenmaterial von 2009 arbeitet. Die A8 war damals eine zweispurige Bundesstraße, die Raststätte bei Augsburg gab es nicht, und der neue Tunnel ist schlicht nicht eingezeichnet. Das Navi optimiert — aber auf Basis von Daten, die seit Jahren nicht mehr stimmen.

Genau das passiert, wenn SQL Servers Query Optimizer mit veralteten oder fehlerhaften Statistiken arbeitet. Der Optimizer ist technisch gesehen ein erstklassiges Stück Software. Er bewertet Zehntausende von möglichen Ausführungsplänen in Millisekunden, rechnet Kosten durch, wählt Join-Strategien — alles korrekt. Aber all diese brillante Arbeit baut auf einer einzigen Annahme: dass die Statistiken stimmen. Stimmen sie nicht, ist der schönste Ausführungsplan nichts wert.

Dieses Kapitel behandelt drei eng verwandte Themen: erstens Statistiken selbst — was sie enthalten, wie SQL Server sie pflegt und wo sie versagen. Zweitens den Cardinality Estimator (CE), das Modul das Statistiken in Zeilenzahlschätzungen übersetzt. Und drittens Plan Regression — den Moment, in dem ein Update alles kaputt macht, das gestern noch funktioniert hat. Als Bonus: Warum Recompilations eine CPU-Falle sind, die in keinem Wait Statistics Dashboard fehlt.

Erinnerst du dich an Kapitel 6, wo wir Auto-Update Statistics und seine Grenzen besprochen haben? Hier holen wir das Thema in voller Tiefe nach. Und für Kapitel 17 (Index-Strategie) ist dieses Kapitel essentiell — weil Indizes ohne aktuelle Statistiken kaum ihren vollen Nutzen entfalten können.

Was Statistiken eigentlich sind — und was nicht

SQL Server speichert für jede Spalte (und jeden Index) ein statistisches Objekt, das dem Optimizer sagt, wie die Daten in dieser Spalte verteilt sind. Kein vollständiger Scan, keine Analyse jedes einzelnen Datensatzes — sondern eine komprimierte Zusammenfassung der Werteverteilung. Dieses statistische Objekt besteht aus drei Teilen:

STAT_HEADER — Metadaten: Name, Tabelle, Spalten, letztes Update, Stichprobengröße

DENSITY_VECTOR — Dichtevektoren: Selektivität von Spalten und Spaltenkombinationen

HISTOGRAM — Histogramm: Werteverteilung in bis zu 200 Schritten (RANGE_HI_KEY)

 

DBCC SHOW_STATISTICS — so liest du eine Statistik

Das Werkzeug der Wahl ist DBCC SHOW_STATISTICS. Es gibt alle drei Komponenten auf einmal aus und ist das erste, was du aufrufst, wenn du dem Optimizer nicht traust:

-- Statistik für den Clustered Index der Tabelle Bestellungen anzeigen

-- WICHTIG: Tabellenname und Statistikname korrekt angeben

DBCC SHOW_STATISTICS ('dbo.Bestellungen', 'PK_Bestellungen');

 

-- Alternativ für eine bestimmte Spalte:

DBCC SHOW_STATISTICS ('dbo.Bestellungen', '_WA_Sys_00000003_123456');

 

-- Nur das Histogramm (oft das Interessanteste):

DBCC SHOW_STATISTICS ('dbo.Bestellungen', 'PK_Bestellungen') WITH HISTOGRAM;

Das Histogramm ist der interessanteste Teil. Jeder STEP (Schritt) enthält: RANGE_HI_KEY (obere Grenze des Bereichs), RANGE_ROWS (geschätzte Zeilen in diesem Bereich), EQ_ROWS (geschätzte Zeilen exakt gleich RANGE_HI_KEY), DISTINCT_RANGE_ROWS (eindeutige Werte im Bereich) und AVG_RANGE_ROWS (Durchschnitt Zeilen pro eindeutigen Wert).

Das Limit von 200 Steps ist entscheidend: SQL Server darf maximal 200 Histogramm-Schritte pro Statistikobjekt anlegen. Bei einer Spalte mit 10 Millionen verschiedenen Werten muss der Optimizer also zwischen den Steps interpolieren — und diese Interpolation geht bei schiefer Datenverteilung gründlich schief.

 

Hintergrund: Interpolation zwischen Histogram-Steps
Wenn der gesuchte Wert zwischen zwei RANGE_HI_KEY-Werten liegt, verwendet SQL Server AVG_RANGE_ROWS als Schätzung — den Durchschnitt aller Werte in diesem Bereich. Wenn ein Bereich 1.000 Zeilen mit 50 verschiedenen Werten hat, schätzt der Optimizer 20 Zeilen für jeden Einzelwert.
Das Problem: "Durchschnitt" und "Realität" sind bei schiefer Verteilung zwei verschiedene Welten. 19 seltene Werte mit je 1 Zeile und ein häufiger Wert mit 981 Zeilen — Durchschnitt 20, Realität 1 oder 981. Der Optimizer schätzt 20 und liegt in beiden Fällen komplett daneben.

 

Statistiken im Überblick — sys.stats

Eine Übersicht aller Statistiken mit Alter und Stichprobengröße liefert diese Query:

-- Alle Statistiken mit Aktualität und Stichprobengröße

-- sp_helpstats gibt nur einen Bruchteil — diese Query ist vollständiger

SELECT

    t.name                          AS tabelle,

    s.name                          AS statistik,

    s.auto_created                  AS automatisch_erstellt,

    s.is_incremental                AS inkrementell,

    STATS_DATE(s.object_id, s.stats_id) AS letztes_update,

    DATEDIFF(DAY,

        STATS_DATE(s.object_id, s.stats_id),

        GETDATE())                  AS tage_alt,

    sp.rows                         AS zeilen_gesamt,

    sp.rows_sampled                 AS zeilen_in_stichprobe,

    -- Stichprobenprozentsatz — unter 10% bei großen Tabellen ist gefährlich

    CONVERT(DECIMAL(5,2),

        100.0 * sp.rows_sampled / NULLIF(sp.rows, 0)) AS stichprobe_pct,

    sp.modification_counter         AS [änderungen_seit_update]

FROM sys.stats s

JOIN sys.tables t

    ON t.object_id = s.object_id

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp

WHERE t.is_ms_shipped = 0          -- nur eigene Tabellen

ORDER BY sp.modification_counter DESC, tage_alt DESC;

Auto-Update Statistics — wann SQL Server von selbst handelt

SQL Server kann Statistiken automatisch aktualisieren — und tut das standardmäßig auch. Aber "automatisch" bedeutet nicht "rechtzeitig". Es gibt zwei verschiedene Schwellenwerte, je nach SQL Server Version und Kompatibilitätslevel:

 

Version / Einstellung	Schwellenwert	Beispiel: 100 Mio. Zeilen
SQL 2014 und älter (oder KL < 130)	500 + 20% der Zeilen	20.000.000 Änderungen nötig
SQL 2016+ mit KL 130+ (oder TF 2371)	sqrt(1000 × N)	~316.000 Änderungen nötig
Kleine Tabellen (N ≤ 500)	Feste Grenze: 500 Änderungen	500 Änderungen

Tabelle 16-1: Auto-Update Statistics Schwellenwerte im Vergleich

 

Der alte 20%-Schwellenwert war für die 1990er Jahre gedacht, als 10 Millionen Zeilen eine große Tabelle waren. Bei modernen Datenbanken ist er schlicht nicht mehr praxistauglich: Eine Tabelle mit 1 Milliarde Zeilen braucht 200 Millionen Änderungen, bis SQL Server automatisch eine Statistik aktualisiert. In dieser Zeit hat der Optimizer Hunderte von schlechten Plänen produziert.

Ab SQL Server 2016 mit Kompatibilitätslevel 130 (oder früher mit TF 2371) verwendet SQL Server den dynamischen Schwellenwert: sqrt(1000 × N). Bei 100 Millionen Zeilen sind das ~316.000 Änderungen statt 20 Millionen — ein Faktor 63 schneller. Diese Verbesserung ist einer der Hauptgründe, das Kompatibilitätslevel aktuell zu halten.

 

Warnung: Der Sparfuchs-Fall: CE 70 auf SQL Server 2019
Bei Sparfuchs & Partner läuft SQL Server 2019 mit Kompatibilitätslevel 110 — das entspricht SQL Server 2012. Ergebnis: Der alte 20%-Schwellenwert ist aktiv, der alte Cardinality Estimator (CE 70) ist aktiv, und alle Verbesserungen der letzten acht Jahre sind wirkungslos.
Konkret: Die Haupttabelle mit 89 Millionen Buchungssätzen bräuchte 17,8 Millionen Änderungen für ein automatisches Statistik-Update. Tatsächlich werden täglich ~2 Millionen Sätze neu geschrieben — der Schwellenwert wird also nie erreicht. Statistiken sind dauerhaft veraltet, und der Optimizer rechnet mit Phantomzahlen.
In Kapitel 33 (Fallstudie Sparfuchs) schauen wir uns an, was das in der Praxis bedeutet.

 

AUTO_UPDATE_STATISTICS_ASYNC — die verkannte Option

Standardmäßig aktualisiert SQL Server Statistiken synchron: Wenn eine Query veraltete Statistiken auslöst, wird sie gebremst, bis das Update fertig ist. Das kann je nach Tabellengröße und Stichprobengröße Sekunden dauern. Die Alternative ist AUTO_UPDATE_STATISTICS_ASYNC:

-- Asynchrone Statistikaktualisierung aktivieren

-- Vorteil: keine Latenzspitze durch Statistik-Update

-- Nachteil: erste Query nach veralteter Statistik bekommt noch den alten Plan

ALTER DATABASE [Produktionsdatenbank]

SET AUTO_UPDATE_STATISTICS_ASYNC ON;

 

-- Status prüfen:

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on

FROM sys.databases

WHERE name = 'Produktionsdatenbank';

Async klingt besser, hat aber einen Haken: Die erste Query nach der Auslösung bekommt noch den alten Plan. Die Statistik wird im Hintergrund aktualisiert, aber die laufende Query nutzt bereits veraltete Informationen. Für OLTP-Systeme mit vielen kurzen Queries ist Async meistens die bessere Wahl — für analytische Systeme mit wenigen langen Queries kann Sync sinnvoller sein, damit die Query von Anfang an den besten Plan bekommt. Mehr zur Datenbankeinstellungs-Grundlage in Kapitel 6.

Der Cardinality Estimator — das Herzstück des Optimizers

Der Cardinality Estimator (CE) ist das Modul im SQL Server Query Optimizer, das aus Statistiken Zeilenzahlschätzungen macht. "Wie viele Zeilen gibt diese WHERE-Klausel zurück?" — das ist die zentrale Frage, die der CE beantwortet. Falsche Antwort → falscher Plan → schlechte Performance.

SQL Server 2014 hat den CE grundlegend überarbeitet. Der neue CE (intern CE 120, nach SQL Server-Version 12.0) verwendet andere Algorithmen für Joins, Multi-Prädikat-Schätzungen und Korrelationsannahmen. Der alte CE (CE 70, nach SQL Server 7.0 — ja, wirklich) war der Standard von 1998 bis 2014.

 

SQL Server Version	Standard-CE	Kompatibilitätslevel	CE aktiviert
SQL Server 7.0 bis 2012	CE 70	70–110	CE 70 (alt)
SQL Server 2014	CE 120	120	CE 120 (neu)
SQL Server 2016	CE 130	130	CE 130 (Standard ab hier)
SQL Server 2017	CE 140	140	CE 140
SQL Server 2019	CE 150	150	CE 150 (mit Intelligent QP)
SQL Server 2022	CE 160	160	CE 160 (mit Cardinality Feedback)

Tabelle 16-2: Kompatibilitätslevel, CE-Version und SQL Server Version

 

Wichtig: Das Kompatibilitätslevel der Datenbank bestimmt den CE — nicht die SQL Server Version. Eine Datenbank mit Kompatibilitätslevel 110 auf SQL Server 2022 verwendet CE 70. Das ist wie ein Formel-1-Auto mit Vergasermotor von 1998 zu fahren.

Die wichtigsten Unterschiede: CE 70 vs. CE 120+

Drei Bereiche, in denen der neue CE systematisch besser arbeitet:

Korrelationsannahmen: CE 70 behandelt alle Prädikate als unabhängig (multiplicative model). Zwei Prädikate mit je 10% Selektivität → 1% Gesamtselektivität. CE 120+ erkennt Korrelationen zwischen Spalten derselben Tabelle und schätzt realistischer.

Join-Schätzung: CE 70 verwendet für Multi-Join-Abfragen einen exponentiellen Reduktionsfaktor der schnell in unrealistische Schätzungen abdriftet. CE 120+ verwendet ein Containment-Modell das stabiler ist.

Multi-Prädikat-Schätzung: Bei Abfragen mit mehreren WHERE-Bedingungen auf derselben Spalte (z.B. BETWEEN-Abfragen) ist CE 120+ deutlich präziser.

Ein konkretes Beispiel für den Korrelations-Effekt: Eine Abfrage auf eine Bestelltabelle mit WHERE Bundesland = 'NRW' AND Stadt = 'Köln'. CE 70 behandelt die Prädikate als unabhängig. Selektivität Bundesland: 5%, Selektivität Stadt: 0,8%. CE 70 multipliziert: 5% × 0,8% = 0,04% = ~400 Zeilen bei 1 Mio. Zeilen. CE 120+ erkennt, dass Bundesland und Stadt korreliert sind (alle Kölner wohnen in NRW), und schätzt ~8.000 Zeilen. Die echte Antwort: 7.843. CE 120+ liegt 2% daneben, CE 70 liegt Faktor 20 daneben.

Ab SQL Server 2022 (CE 160) kommt noch Cardinality Feedback hinzu: Der CE lernt aus Fehlern. Wenn eine Schätzung dauerhaft daneben liegt, passt SQL Server die Schätzung für zukünftige Ausführungen automatisch an — ohne Statistik-Update. Das ist ein echter Paradigmenwechsel, aber noch auf SQL Server 2022 beschränkt.

 

Praxisbeispiel: Sparfuchs: CE 70 und eine Abfrage mit vier Joins
Die Hauptabfrage in BUCHSQL01 verbindet fünf Tabellen: Buchungen, Mandanten, Steuersätze, Kostenstellen und Konten. Mit CE 70 schätzt der Optimizer nach dem dritten Join noch 12 Zeilen im Resultset — tatsächlich sind es 847.000.
Ergebnis: Nested Loop statt Hash Join, kein Parallelismus, weil der Optimizer "kleine" Resultsets nicht parallelisiert. Laufzeit: 4 Minuten 23 Sekunden. Nach UPDATE STATISTICS WITH FULLSCAN und Kompatibilitätslevel 150: 11 Sekunden. Faktor 24 — nur durch korrekte Schätzung.

 

Kompatibilitätslevel anpassen — mit Vorsicht

Das Kompatibilitätslevel auf den neuesten Stand bringen ist sinnvoll — aber nicht ohne Test. Manche Abfragen sind historisch auf CE 70 optimiert worden und profitieren nicht von CE 120+. Microsoft empfiehlt einen stufenweisen Ansatz mit dem Query Store:

-- Aktuelles Kompatibilitätslevel prüfen

SELECT name, compatibility_level

FROM sys.databases

WHERE database_id = DB_ID();

 

-- Schritt 1: Query Store einschalten (vor der Änderung!)

-- So können wir Plan Regressions nach dem Upgrade erkennen

ALTER DATABASE [Produktionsdatenbank]

SET QUERY_STORE = ON

WITH (OPERATION_MODE = READ_WRITE,

      MAX_STORAGE_SIZE_MB = 2048,

      INTERVAL_LENGTH_MINUTES = 30);

 

-- Schritt 2: Kompatibilitätslevel erhöhen

ALTER DATABASE [Produktionsdatenbank]

SET COMPATIBILITY_LEVEL = 150; -- SQL Server 2019

 

-- Schritt 3: Nach 1-2 Wochen im Query Store nach Plan Regressions suchen

-- (dazu mehr in Kapitel 19 — Query Store)

Plan Regression — wenn ein Update alles kaputt macht

Plan Regression ist der Begriff für das Phänomen, dass eine Abfrage nach einem scheinbar harmlosen Ereignis plötzlich einen deutlich schlechteren Ausführungsplan verwendet. "Scheinbar harmlos" kann viel bedeuten: ein Statistik-Update, ein SQL Server Patch, eine neue Indexwartung, eine geänderte Parameterverteilung.

Das Tückische an Plan Regressions: Sie treten oft mit Verzögerung auf. Der neue Plan wird nach dem auslösenden Ereignis aus dem Cache verdrängt und neu kompiliert — das kann Stunden oder Tage nach dem eigentlichen Auslöser sein. Dann sagt jemand: "Seit gestern Abend ist alles langsam", und du blätterst verwirrt in den Change-Logs.

Auslöser von Plan Regressions

Statistik-Update: Neues Histogramm → andere Kardinalitätsschätzung → anderer Plan

SQL Server Upgrade / Patch: CE-Version kann sich ändern, Optimizer-Fixes können Pläne kippen

Kompatibilitätslevel-Änderung: CE 70 auf CE 120+ ist der häufigste Auslöser

Index-Änderung: Neuer Index verfügbar → Optimizer wählt ihn; alter Index gelöscht → Optimizer fällt auf Fallback

Parameterverteilung: Neue häufige Parameterwerte für die kein passender Plan im Cache ist (→ Kapitel 18)

Datenwachstum: Tabelle überschreitet Schwellenwert → Optimizer wechselt Strategie

Plan Regression erkennen mit sys.dm_exec_query_stats

Ohne Query Store (mehr dazu in Kapitel 19) ist sys.dm_exec_query_stats das wichtigste Werkzeug. Die Spalte plan_generation_num zeigt, wie oft ein Plan für dieselbe Query generiert wurde — jeder Wert über 1 bedeutet Recompile:

-- Abfragen mit hoher Recompile-Rate und gestieger Laufzeit finden

-- plan_generation_num > 1 bedeutet: dieser Plan wurde schon recompiliert

SELECT TOP 20

    qs.execution_count,

    qs.plan_generation_num,          -- wie oft wurde der Plan neu generiert?

    qs.total_elapsed_time / 1000     AS total_laufzeit_ms,

    qs.total_elapsed_time

        / NULLIF(qs.execution_count, 0) / 1000 AS avg_laufzeit_ms,

    qs.total_logical_reads

        / NULLIF(qs.execution_count, 0) AS avg_logical_reads,

    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,

        ((CASE qs.statement_end_offset

            WHEN -1 THEN DATALENGTH(st.text)

            ELSE qs.statement_end_offset END

            - qs.statement_start_offset) / 2) + 1) AS sql_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE qs.plan_generation_num > 1    -- nur recompilierte Pläne

   OR qs.total_elapsed_time / NULLIF(qs.execution_count, 0) > 1000000 -- >1 Sek.

ORDER BY avg_laufzeit_ms DESC;

Plan Guides — das Pflaster für den Notfall

Plan Guides erlauben es, einem Ausführungsplan manuell zu erzwingen. Das ist ein scharfes Werkzeug und sollte nur im Notfall eingesetzt werden — aber manchmal ist "Notfall" eben Freitag um 17:30 Uhr, kurz, bevor das Release-Wochenende beginnt:

-- Plan Guide erstellen — erzwingt einen bestimmten Query Hint

-- Besser: Query Store mit Force Plan verwenden (Kapitel 19)

-- Plan Guides sind schwer zu warten und zu debuggen

EXEC sp_create_plan_guide

    @name = N'PG_Bestellungen_Monatsbericht',

    @stmt = N'SELECT * FROM dbo.Bestellungen WHERE Datum >= @start AND Datum < @ende',

    @type = N'SQL',

    @hints = N'OPTION (OPTIMIZE FOR (@start = ''2024-01-01'', @ende = ''2024-02-01''))';

 

-- Plan Guide prüfen:

SELECT * FROM sys.plan_guides WHERE is_disabled = 0;

 

-- Plan Guide entfernen, wenn das eigentliche Problem gelöst ist:

EXEC sp_control_plan_guide N'DROP', N'PG_Bestellungen_Monatsbericht';

Eine elegantere Lösung bietet der Query Store mit "Force Plan" (Kapitel 19) — dasselbe Ergebnis, aber mit Monitoring, Versionierung und automatischem Fallback, wenn der gezwungene Plan nicht mehr funktioniert.

 

Praxisbeispiel: Musterwerk GmbH: Plan Regression nach Index-Wartung
Bei Musterwerk GmbH (MWSQL01) trat eine klassische Plan Regression auf: Nach dem sonntäglichen Index-Rebuild-Job war am Montagmorgen der Monatsbericht plötzlich 8 Minuten statt 45 Sekunden langsam. Ursache: Der Index-Rebuild hatte die Statistiken mit FULLSCAN aktualisiert — korrekt. Dabei hatte sich die Kardinalitätsschätzung für eine der Zwischentabellen von 12.000 auf 847.000 Zeilen korrigiert.
Der Optimizer wählte daraufhin einen anderen Join-Typ und anderen Index. Der neue Plan war theoretisch besser — in der Praxis aber schlechter, weil ein anderer Index gefehlt hat der als Covering Index für die veränderte Join-Reihenfolge gebraucht wurde.
Lösung: Query Store hatte den alten Plan gespeichert, Force Plan für die Übergangszeit, dann fehlenden Covering Index erstellt. Nach 3 Tagen Force Plan aufgehoben — der neue Plan mit neuem Index war jetzt schneller als der alte. Typisches Muster: Nicht der Optimizer hat versagt, sondern die Index-Strategie war unvollständig.

 

USE HINT für CE-Steuerung

Wenn CE 120+ bei einer bestimmten Query schlechtere Ergebnisse liefert als CE 70, gibt es einen gezielten Hint statt das gesamte Kompatibilitätslevel zurückzudrehen:

-- Nur für diese eine Abfrage den alten CE verwenden

-- Sinnvoll als temporäre Maßnahme bis das eigentliche Problem analysiert ist

SELECT k.KundenID, SUM(b.Betrag) AS Gesamtumsatz

FROM dbo.Kunden k

JOIN dbo.Bestellungen b ON b.KundenID = k.KundenID

WHERE b.Datum >= '2024-01-01'

GROUP BY k.KundenID

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

 

-- Alternativ: Minimalen Statistik-Stichprobensatz erzwingen

-- Wenn der Plan durch eine zu kleine Stichprobe fehlerhaft ist

SELECT * FROM dbo.Bestellungen

WHERE Kundennummer = @KNr

OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'));

Statistiken manuell pflegen — wann und wie

Auto-Update Statistics ist kein Ersatz für eine aktive Statistikpflege. Besonders nach Index-Rebuilds, Bulk-Inserts oder unregelmäßigen Datenladevorgängen kann eine manuelle Aktualisierung den Unterschied zwischen einer Sekunde und einer Minute Abfragezeit bedeuten.

UPDATE STATISTICS — Syntax und Stichprobengröße

-- Alle Statistiken einer Tabelle aktualisieren (mit automatischer Stichprobe)

-- Schnell, aber bei sehr schiefer Verteilung reicht die Stichprobe nicht

UPDATE STATISTICS dbo.Bestellungen;

 

-- Nur eine bestimmte Statistik aktualisieren:

UPDATE STATISTICS dbo.Bestellungen PK_Bestellungen;

 

-- FULLSCAN — scannt jeden einzelnen Satz, keine Stichprobe

-- Kostet mehr CPU und IO, aber liefert perfekte Histogramme

-- Pflicht nach Bulk-Inserts und bei schiefer Datenverteilung

UPDATE STATISTICS dbo.Bestellungen WITH FULLSCAN;

 

-- Stichprobengröße explizit setzen (hier: 30%)

-- Kompromiss zwischen Genauigkeit und Performance

UPDATE STATISTICS dbo.Bestellungen WITH SAMPLE 30 PERCENT;

 

-- Alle Statistiken in der gesamten Datenbank aktualisieren

-- sp_updatestats aktualisiert nur Tabellen die sich seit dem letzten Update geändert haben

-- UPDATE STATISTICS WITH FULLSCAN macht ALLE, auch unveränderte

EXEC sp_updatestats;

 

Tipp: Index-Rebuild vs. Index-Reorganize und Statistiken
ALTER INDEX … REBUILD aktualisiert automatisch alle zugehörigen Statistiken mit FULLSCAN — kein separater UPDATE STATISTICS nötig.
ALTER INDEX … REORGANIZE aktualisiert keine Statistiken. Wenn dein Wartungsplan nur REORGANIZE macht (z.B. weil die Fragmentierung unter 30% liegt), musst du Statistiken separat aktualisieren.
Das ist eine häufige Falle: nach Index-Reorganize denkt man, alles sei gewartet — aber die Statistiken sind noch so alt wie vorher.

 

Filternde Statistiken für schiefe Verteilungen

Filternde Statistiken (Filtered Statistics) erlauben Histogramme für eine Teilmenge der Daten. Das ist besonders wertvoll, wenn eine Spalte extrem schiefe Verteilung hat — zum Beispiel eine Status-Spalte mit 99% "Abgeschlossen" und 1% "Offen":

-- Gefilterte Statistik nur für offene Aufträge

-- Der Optimizer bekommt ein viel präziseres Histogramm für den interessanten Bereich

CREATE STATISTICS stat_offene_auftraege

ON dbo.Auftraege (Status, ErstellDatum)

WHERE Status = 'Offen'

WITH FULLSCAN;

 

-- Gefilterte Statistik zeigt nur Zeilen mit Status = 'Offen'

-- Bei einer Abfrage WHERE Status = 'Offen' AND ErstellDatum > @datum

-- verwendet der Optimizer die gefilterte Statistik, wenn der Filter passt

Gefilterte Statistiken arbeiten Hand in Hand mit gefilterten Indizes (Kapitel 17). Beide zusammen sind ein mächtiges Werkzeug für Tabellen mit stark schiefer Verteilung — vorausgesetzt, die WHERE-Klausel der Abfrage passt exakt zum Filter. Mehr dazu beim Thema SARGability in Kapitel 21.

Recompilations — die unsichtbare CPU-Falle

Jede Kompilierung eines Ausführungsplans kostet CPU — nicht wenig, sondern messbar. Ein einfaches SELECT auf eine mittelgroße Tabelle mit zwei Joins und ein paar Prädikaten kann 5–20 ms Kompilierungszeit kosten. Bei 10.000 solcher Kompilierungen pro Minute wären das 50–200 Sekunden CPU-Arbeit — nur für das Kompilieren, nicht für die Ausführung.

Recompilations (Neukompilierungen für bereits bekannte Abfragen) sind ein CPU-Kostenfaktor der in Wait Statistics oft als SOS_SCHEDULER_YIELD auftaucht (Kapitel 9). Hohe Kompilierungsraten zeigen sich im Task Manager als kontinuierlich hohe CPU-Last ohne erkennbare "Schuld-Abfrage" — weil die Last nicht durch eine teure Query entsteht, sondern durch Tausende von Neukompilierungen kurzer Queries.

Warum Recompiles entstehen

Statistik-Update während einer Transaktion: SQL Server erkennt, dass sich die zugrunde liegenden Statistiken geändert haben und wirft den Plan weg

SET-Option-Änderung: Wenn eine Verbindung ANSI_NULLS, QUOTED_IDENTIFIER oder andere SET-Optionen ändert, sind alle gecachten Pläne für diese Verbindung ungültig

Schema-Änderung: ALTER TABLE, DROP INDEX — alle betroffenen Pläne werden invalidiert

sp_recompile: Manuell erzwungen, markiert alle Pläne für eine Tabelle als ungültig

WITH RECOMPILE: In der Query oder Prozedur-Definition angegeben — Pflicht-Recompile bei jeder Ausführung

Tabellenvariable (DECLARE @t TABLE): Immer Recompile, weil Tabellenvariablen keine Statistiken haben — der Optimizer rechnet immer mit 1 Zeile, was fast immer falsch ist

Temporäre Tabellen (CREATE TABLE #t): Recompile, wenn sich die Zeilenzahl ändert (Threshold-basiert)

Recompile-Rate messen

Die schnellste Methode ist sys.dm_os_performance_counters — SQL Server pflegt diese Zähler kontinuierlich:

-- SQL Compilations/sec und Recompilations/sec auslesen

-- Richtwerte: Compilations/sec > 100 prüfen, Recompilations/sec > 10 ist auffällig

SELECT

    counter_name,

    cntr_value AS aktueller_wert

FROM sys.dm_os_performance_counters

WHERE object_name LIKE '%SQL Statistics%'

  AND counter_name IN (

      'SQL Compilations/sec',

      'SQL Re-Compilations/sec',

      'Batch Requests/sec'

  );

 

-- Verhältnis: Recompilations zu Compilations berechnen

-- Über 10% Recompilations an Compilations ist ein Warnsignal

SELECT

    SUM(CASE WHEN counter_name = 'SQL Re-Compilations/sec'

        THEN cntr_value ELSE 0 END) * 100.0

    / NULLIF(SUM(CASE WHEN counter_name = 'SQL Compilations/sec'

        THEN cntr_value ELSE 0 END), 0) AS recompile_anteil_pct

FROM sys.dm_os_performance_counters

WHERE object_name LIKE '%SQL Statistics%'

  AND counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec');

Richtwerte aus der Praxis: SQL Compilations/sec über 100 ist auffällig und sollte untersucht werden. SQL Re-Compilations/sec über 10% der Compilations-Rate ist ein klares Warnsignal. Diese Zahlen müssen im Kontext der Baseline bewertet werden — ein System mit 5.000 Batch Requests/sec kann hohe Compilation-Raten haben ohne, dass es ein Problem ist. Ohne Baseline bleibt jede Zahl sinnlos (Kapitel 9 zur Baseline-Erhebung).

Recompiles mit Extended Events diagnostizieren

-- Extended Event Session für Recompile-Diagnose

-- Zeichnet jeden Recompile mit Grund auf — nicht dauerhaft laufen lassen!

CREATE EVENT SESSION [Recompile_Diagnose] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(

    -- recompile_cause erklärt den Grund des Recompile

    -- 1=Schema changed, 2=Stats changed, 4=Deferred compile, etc.

    ACTION(sqlserver.sql_text, sqlserver.database_name,

           sqlserver.username, sqlserver.client_app_name)

    WHERE sqlserver.database_id = DB_ID()

)

ADD TARGET package0.ring_buffer(SET max_memory = 51200) -- 50 MB

WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

 

ALTER EVENT SESSION [Recompile_Diagnose] ON SERVER STATE = START;

 

-- Nach Diagnose wieder stoppen und löschen!

ALTER EVENT SESSION [Recompile_Diagnose] ON SERVER STATE = STOP;

DROP EVENT SESSION [Recompile_Diagnose] ON SERVER;

 

Hinweis: Tabellenvariablen und der 1-Zeilen-Irrtum
DECLARE @t TABLE (…) hat ein fundamentales Problem: Tabellenvariablen haben keine Statistiken. Der Optimizer rechnet immer mit exakt 1 Zeile — egal ob die Variable 10 oder 10 Millionen Zeilen enthält.
Ergebnis: Bei einem Join mit einer Tabellenvariable mit 100.000 Zeilen wählt der Optimizer fast immer Nested Loop statt Hash Join, weil er mit 1 Zeile rechnet. Das ist kein Bug — das ist By Design, und es ist einer der häufigsten Performance-Killer in T-SQL-Code.
Alternative: Temporäre Tabellen (#temp) haben Statistiken und lösen bei Bedarf Recompile aus. In Kapitel 26 (Stored Procedures & T-SQL) behandeln wir das ausführlich.

 

Das Recompile-Thema ist direkt mit Parameter Sniffing verknüpft, das wir in Kapitel 18 ausführlich behandeln. Kurze Vorschau: Parameter Sniffing ist das Gegenstück zu Recompiles — statt jeden Plan neu zu compilieren, cached SQL Server einen Plan für den ersten Parameterwert und wiederverwendet ihn für alle folgenden. Wenn der erste Parameterwert atypisch war, haben alle folgenden Abfragen einen schlechten Plan. Die Kunst liegt darin, den richtigen Mittelweg zu finden: nicht jede Abfrage recompilieren, aber auch nicht blind an einem einzigen Plan festhalten.

Für die CPU-Diagnose gilt: SOS_SCHEDULER_YIELD als dominanter Wait Type (Kapitel 9) in Kombination mit hoher SQL Compilations/sec in Performance Counters ist das Warnsignal für compilierungsinduzierte CPU-Last. In diesem Fall hilft keine Index-Optimierung und kein Statistik-Update — das Problem ist architektonischer Natur: zu viele Ad-hoc-Abfragen, zu wenige gecachte Pläne, oder zu viele Recompile-Auslöser im Code.

Das Ascending Key Problem — wenn neue Daten unsichtbar sind

Stell dir eine Bestelltabelle vor mit einer IDENTITY-Spalte als Primary Key. Das Histogramm wurde letzte Nacht aktualisiert — höchster Key-Wert war 9.847.231. Heute Morgen wurden 250.000 neue Bestellungen eingefügt, Keys 9.847.232 bis 10.097.231.

Wenn jetzt eine Abfrage nach aktuellen Bestellungen sucht — WHERE BestellID > 9.900.000 — zeigt das Histogramm: "Keine Daten in diesem Bereich". SQL Server schätzt 0 oder 1 Zeile. Tatsächlich sind es 197.231. Der Optimizer wählt Nested Loop statt Table Scan, und die Abfrage läuft 40 Sekunden statt 0,3 Sekunden.

Das ist das Ascending Key Problem — eines der häufigsten Probleme bei Tabellen mit monoton steigenden Schlüsseln (IDENTITY, Timestamp, NEWSEQUENTIALID).

Lösungsstrategien für das Ascending Key Problem

Häufigere Statistik-Updates: Nicht nächlich, sondern mehrfach täglich für betroffene Tabellen. Kein FULLSCAN nötig — auch eine Stichprobe zeigt, dass neue Werte existieren.

Incremental Statistics (SQL 2014+): Statistiken pro Partition, nicht über die gesamte Tabelle. Neue Partitionen bekommen eigene Histogramme. Einschränkung: Das globale Histogramm wird trotzdem aus allen Partitionen zusammengefasst.

TF 4139 (SQL 2012 SP2+): Aktiviert erweiterte Schätzungslogik für Ascending Keys. Kein Serverflag mehr ab SQL 2016 — dort standardmäßig aktiv mit KL 130.

Gefilterte Statistiken: Für bekannte "heiße" Wertebereiche separate Statistikobjekte erstellen.

-- Incremental Statistics aktivieren (SQL 2014+, nur für partitionierte Tabellen)

-- Erstellt Statistiken pro Partition statt über alle Daten

ALTER TABLE dbo.Bestellungen

REBUILD PARTITION = ALL

WITH (STATISTICS_INCREMENTAL = ON);

 

-- Statistik mit Incremental erstellen

CREATE STATISTICS stat_Bestellungen_BestellID

ON dbo.Bestellungen (BestellID)

WITH INCREMENTAL = ON;

 

-- Nur eine Partition aktualisieren statt der gesamten Tabelle

-- Spart bei großen Tabellen erheblich Zeit und IO

UPDATE STATISTICS dbo.Bestellungen stat_Bestellungen_BestellID

WITH RESAMPLE ON PARTITIONS(42); -- nur Partition 42 neu berechnen

Diagnose-Queries für den Alltag

Zwei Queries die in keiner DBA-Toolbox fehlen sollten — beide sind direkt einsetzbar:

Veraltete Statistiken finden

-- Statistiken finden die älter als 7 Tage sind UND viele Änderungen haben

-- Beide Bedingungen müssen zutreffen — viele Änderungen bei frischen Statistiken = kein Problem

SELECT

    t.name                          AS tabelle,

    s.name                          AS statistik,

    STATS_DATE(s.object_id, s.stats_id) AS letztes_update,

    DATEDIFF(DAY,

        STATS_DATE(s.object_id, s.stats_id),

        GETDATE())                  AS tage_alt,

    sp.modification_counter         AS [änderungen],

    sp.rows                         AS zeilen_gesamt,

    -- Prozentsatz der geänderten Zeilen — über 20% bei alter Statistik: dringend

    CONVERT(DECIMAL(5,1),

        100.0 * sp.modification_counter / NULLIF(sp.rows, 0)) AS [änderung_pct]

FROM sys.stats s

JOIN sys.tables t ON t.object_id = s.object_id

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp

WHERE t.is_ms_shipped = 0

  AND STATS_DATE(s.object_id, s.stats_id) < DATEADD(DAY, -7, GETDATE())

  AND sp.modification_counter > 1000  -- mindestens 1000 Änderungen

ORDER BY [änderung_pct] DESC;

Tabellen mit zu kleiner Stichprobe finden

-- Große Tabellen mit unzureichender Stichprobengröße aufspüren

-- Unter 10% Stichprobe bei Tabellen über 1 Mio. Zeilen ist ein Risiko

SELECT

    t.name                          AS tabelle,

    s.name                          AS statistik,

    sp.rows                         AS zeilen_gesamt,

    sp.rows_sampled                 AS zeilen_gesamplet,

    CONVERT(DECIMAL(5,2),

        100.0 * sp.rows_sampled / NULLIF(sp.rows, 0)) AS stichprobe_pct,

    STATS_DATE(s.object_id, s.stats_id) AS letztes_update

FROM sys.stats s

JOIN sys.tables t ON t.object_id = s.object_id

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp

WHERE t.is_ms_shipped = 0

  AND sp.rows > 1000000             -- nur Tabellen ab 1 Mio. Zeilen

  AND 100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) < 10.0 -- unter 10% Stichprobe

ORDER BY sp.rows DESC;

 

-- Lösung: Statistiken mit höherem Stichprobenprozentsatz oder FULLSCAN neu erstellen

-- UPDATE STATISTICS dbo.Bestellungen WITH SAMPLE 30 PERCENT;

Diagnose-Kästen: Statistiken und Plan Regression

 

Warnung: Symptome: Statistik- und CE-Probleme
Symptom 1: Abfrage die gestern 0,5 Sekunden lief braucht heute 3 Minuten — ohne Datenänderung. Klassische Plan Regression nach Statistik-Update oder SQL Server Patch.
Symptom 2: CPU-Last kontinuierlich bei 70–90% ohne erkennbare "Schuld-Abfrage". SOS_SCHEDULER_YIELD in den Top-3 der Wait Statistics (Kapitel 9). Hohe SQL Compilations/sec oder SQL Re-Compilations/sec in Performance Counters.
Symptom 3: Ausführungsplan zeigt dramatisch falsche Kardinalitätsschätzungen — z.B. "Estimated Rows: 1" bei tatsächlich 500.000 Zeilen. Typisch für Ascending Key Problem oder veraltete Statistiken.
Symptom 4: Nach Upgrade oder Kompatibilitätslevel-Änderung werden mehrere Abfragen gleichzeitig langsamer. CE-Wechsel als Ursache.
Symptom 5: Index-Fragmentierung liegt unter 10%, Statistiken wurden heute Nacht aktualisiert — aber Performance ist trotzdem schlecht. Stichprobengröße prüfen: war es FULLSCAN oder 1% Stichprobe?

 

 

Tipp: So misst du das: Statistik-Diagnose
Schritt 1 — Ausführungsplan prüfen: In SSMS Query → Include Actual Execution Plan. Gelbe Ausrufezeichen auf Operatoren zeigen dramatische Schätzfehler (Estimated vs. Actual Rows).
Schritt 2 — Histogramm prüfen: DBCC SHOW_STATISTICS ('dbo.Tabelle', 'IndexName') WITH HISTOGRAM. STATS_DATE zeigt das Alter, rows_sampled / rows zeigt die Stichprobengröße.
Schritt 3 — Recompile-Rate: SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec').
Schritt 4 — Plan Regression im Query Store: sys.query_store_query + sys.query_store_plan + sys.query_store_runtime_stats im Zeitvergleich. In Kapitel 19 im Detail erklärt.
Schritt 5 — Kompatibilitätslevel: SELECT name, compatibility_level FROM sys.databases. Unter 130 auf SQL Server 2016+ = alter CE aktiv.

 

 

Hinweis: Typische Fehlinterpretationen
Fehlinterpretation 1: "Statistiken sind aktuell — also kann das nicht das Problem sein." Stimmt nicht. Statistiken können aktuell aber ungenau sein. Ein Update mit 0,01% Stichprobe zählt als "aktuell", ist aber bei schiefer Datenverteilung wertlos. rows_sampled in sys.dm_db_stats_properties prüfen!
Fehlinterpretation 2: "CE 120+ ist immer besser als CE 70." Falsch. Manche Workloads wurden jahrelang implizit auf CE 70-Verhalten optimiert — Pläne, Indizes, Parameter. Ein CE-Wechsel kann bei solchen Systemen für einzelne Abfragen einen Rückschritt bedeuten. Immer mit Query Store testen.
Fehlinterpretation 3: "Hohe CXPACKET-Waits bedeuten schlechte Parallelismus-Performance." CXPACKET ist oft harmloser Parallelismus-Overhead. Schlechte Kardinalitätsschätzungen die zu falschen Join-Strategien führen sind gefährlicher — aber unsichtbarer. Kapitel 15 erklärt den Unterschied.
Fehlinterpretation 4: "sp_updatestats ist equivalent zu UPDATE STATISTICS WITH FULLSCAN." Nein. sp_updatestats aktualisiert nur Statistiken, bei denen sich seit dem letzten Update etwas geändert hat — und mit Standardstichprobe. UPDATE STATISTICS WITH FULLSCAN scannt wirklich jeden Satz.

 

 

Tipp: Erste Gegenmaßnahmen bei Statistik-Problemen
Maßnahme 1 — UPDATE STATISTICS WITH FULLSCAN auf die betroffene Tabelle. Danach Ausführungsplan neu laden (CTRL+M in SSMS für Actual Plan). In 60% der Fälle löst das das Problem sofort.
Maßnahme 2 — Kompatibilitätslevel prüfen und ggf. auf 150+ anheben (nach Test!). Vorher Query Store einschalten — dann kannst du Plan Regressions nach dem Upgrade sofort erkennen und gezwungene Pläne setzen.
Maßnahme 3 — Bei Plan Regression nach Statistik-Update: Im Query Store den letzten bekannt guten Plan erzwingen (Force Plan). Das gibt Zeit für die eigentliche Analyse, ohne, dass die Produktion leidet.
Maßnahme 4 — Bei hoher Recompile-Rate: Extended Event Session sql_statement_recompile starten, 15 Minuten laufen lassen, auswerten. recompile_cause gibt den genauen Grund. Tabellenvariablen in Stored Procedures sind oft der Hauptschuldige.
Maßnahme 5 — Ascending Key Problem: Statistik-Update-Job für die betroffene Tabelle auf stündlich setzen. Sofortmaßnahme — keine dauerhafte Lösung.

 

Zusammenfassung

Statistiken sind die Grundlage, auf der der gesamte Query Optimizer arbeitet. Kein Ausführungsplan, keine Join-Strategie, keine Parallelismus-Entscheidung passiert ohne den Cardinality Estimator — und der CE ist nur so gut wie die Statistiken die er liest. Das macht Statistikpflege zu einer der wichtigsten und am häufigsten vernachlässigten DBA-Aufgaben.

Die wichtigsten Erkenntnisse aus diesem Kapitel:

Statistiken bestehen aus Histogramm (bis 200 Steps), Dichtevektoren und Metadaten. DBCC SHOW_STATISTICS ist das primäre Diagnosewerkzeug.

Der 20%-Schwellenwert für Auto-Update Statistics versagt bei großen Tabellen komplett. SQL Server 2016+ mit Kompatibilitätslevel 130 verwendet den deutlich besseren sqrt(1000 × N)-Schwellenwert.

Der Cardinality Estimator CE 70 (aktiv bei Kompatibilitätslevel unter 120) ist technisch veraltet. Das Upgrade auf CE 120+ bringt bei den meisten Workloads messbare Verbesserungen — aber nicht ohne vorherigen Test mit dem Query Store.

Plan Regression ist tückisch, weil sie zeitverzögert auftritt. Query Store (Kapitel 19) ist das richtige Werkzeug zur Diagnose und Behandlung.

Recompilations sind eine versteckte CPU-Falle. Hohe SQL Re-Compilations/sec in Performance Counters mit SOS_SCHEDULER_YIELD in den Wait Statistics (Kapitel 9) ist ein klares Signal.

Das Ascending Key Problem trifft jede Tabelle mit IDENTITY oder Timestamp-Spalten. Häufigere Statistik-Updates und Incremental Statistics (SQL 2014+) sind die Lösungen.

Filternde Statistiken und Stichprobenkontrolle sind Werkzeuge für den fortgeschrittenen DBA — aber manchmal der einzige Weg, dem Optimizer bei stark schiefen Datenverteilungen präzise Schätzungen zu geben.

Ein Hinweis für die Praxis: Statistikprobleme sind häufig die zweite Ursache hinter einem langsamen System — die erste ist meistens fehlende Indizes. Und für beides gilt: Ohne Baseline weißt du nicht, ob dein Problem neu ist oder schon immer da war. Die Baseline-Erhebung aus Kapitel 9 ist hier Voraussetzung.

Ausblick: Kapitel 17 — Index-Strategie & Wartung

Kapitel 17 baut direkt auf diesem Kapitel auf. Indizes und Statistiken sind untrennbar: Jeder Index bringt automatisch Statistiken mit, Index-Rebuild aktualisiert Statistiken, und die Entscheidung welchen Index der Optimizer nutzt basiert auf Kardinalitätsschätzungen. Kapitel 17 zeigt, wie man Indizes strategisch auswählt, Covering Indexes für maximale Effizienz baut und eine Wartungsstrategie entwickelt, die sowohl Fragmentierung als auch Statistikaktualität im Griff behält. Und natürlich: wann ein Index mehr schadet als nützt — was überraschend häufig vorkommt.

 

Abb. 1: Statistik-Histogramm

 

Abb. 2: Cardinality Estimator: CE70 vs. CE120+

 

Kapitel 17