SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

NOTFALL-KIT – SQL SERVER 2026

SQL Server Indexierung

Der größte Performance-Hebel — vollständig verstanden und beherrscht

1

Index-Typen im Vergleich

Clustered, Non-Clustered, Columnstore, Filtered — wann was

2

Index-Design-Regeln

Spaltenreihenfolge, INCLUDE, Fill Factor — richtig gemacht

3

Fragmentierung & Wartung

REBUILD vs. REORGANIZE — automatisiert und zeitoptimiert

4

Index-Analyse-Toolkit

Missing Indexes, Unused Indexes, Duplicate Indexes — aufgedeckt

5

30-Tage-Index-Audit

Vollständiger Index-Review und Optimierungsplan

WAS SIE IN DIESEM KIT ERWARTEN DÜRFEN

■  Index-Internals — B-Tree-Struktur, Heap vs. Clustered, IAM-Pages — verständlich erklärt.

■  Index-Typen — Wann Clustered, wann Non-Clustered, wann Columnstore, wann Filtered.

■  Design-Regeln — Spaltenreihenfolge, INCLUDE-Spalten, Fill Factor, Covering Indexes.

■  Analyse-Toolkit — 20+ DMV-Abfragen für Missing, Unused, Duplicate, Fragmented Indexes.

■  30-Tage-Audit-Plan — Vollständiger Index-Review mit priorisierten Maßnahmen.

ZIEL DIESES KITS

Nach dem Lesen und Umsetzen dieses Kits verstehen Sie, wie SQL Server Indizes intern verwaltet, können eine fundierte Indexstrategie entwickeln und haben ein automatisiertes Monitoring, das Index-Probleme proaktiv aufdeckt — bevor Benutzer sie bemerken.

B-TREE-AUFBAU (Balanced Tree):

                    [Root Page]

                   /     |     \

          [Inter-    [Inter-    [Inter-

           mediate]   mediate]   mediate]

          /   \       /   \      /   \

       [Leaf] [Leaf][Leaf][Leaf][Leaf][Leaf]

ROOT PAGE:    Enthält Zeiger auf Intermediate Pages

INTERMEDIATE: Enthält Schlüsselwerte + Zeiger auf tiefere Ebenen

LEAF LEVEL:   Enthält Schlüsselwerte + Datenzugriff

CLUSTERED INDEX: Leaf Level = die Datenseiten selbst

NON-CLUSTERED:   Leaf Level = Schlüssel + Row Locator (RID oder Clustering Key)

TIEFE EINES B-TREES:

  10.000 Zeilen:     2-3 Ebenen

  1 Mio. Zeilen:     3-4 Ebenen

  100 Mio. Zeilen:   4-5 Ebenen

→ Selbst riesige Tabellen brauchen maximal 5 Seiten-Reads bis zur Zeile!

Physische Ordnung

Keine — Zeilen zufällig

Sortiert nach Clustered Key

Zeilenlokalisierung

Row ID (File:Page:Slot)

Clustered Key

Table Scan

Alle IAM-Seiten folgen

Alle Leaf-Pages sequenziell

Non-Cl. Index Lookup

2 Lookups (NC → RID → Heap)

1 Lookup (NC → Clustering Key)

INSERT Performance

Schnell (letzte Seite)

Ggf. Seitenteilung (Page Split)

Empfehlung

Nur für temporäre Staging-Tabellen

Standard für alle Produktivtabellen

PROBLEM PAGE SPLIT:

  Neue Zeile soll zwischen zwei bestehende Zeilen

  → Seite ist voll (100% Fill Factor)

  → SQL Server teilt die Seite in zwei hälftig gefüllte Seiten

  → Fragmentierung entsteht sofort

  → Log-Schreibaufwand verdoppelt sich für diese Operation

LÖSUNG — FILL FACTOR:

  Fill Factor = 80 % → Seite wird nur zu 80 % gefüllt angelegt

  → 20 % Reserve für zukünftige Inserts

  → Weniger Page Splits bei INSERT-intensiven Tabellen

  → Mehr Speicher und mehr I/O beim Lesen (Kompromiss!)

FAUSTREGEL FILL FACTOR:

  Primär lesend (BI, Reporting):     95-100 %

  Gemischt (ERP-Tabellen):           80-90 %

  Primär schreibend (Log-Tabellen):  70-80 %

  Sequentielle Inserts (Append):     100 %

-- Ideale Clustered Index Kandidaten:

-- 1. Primärschlüssel mit sequentiellen Werten (INT IDENTITY, BIGINT)

-- 2. Häufig für Range-Scans genutzte Spalten (Datum, Buchungsnummer)

-- 3. Spalten die in vielen Non-Clustered Indexes als Lookup-Key dienen

-- GUT: Sequentiell, schmal, eindeutig

CREATE CLUSTERED INDEX CIX_Bestellungen_ID

ON dbo.Bestellungen (BestellID);   -- INT IDENTITY: kein Page Split

-- SCHLECHT: GUID als Clustered Key

-- NEWID() = zufällige Werte → 100% Page Splits → massiver Overhead

-- Wenn GUID nötig: NEWSEQUENTIALID() verwenden!

CREATE CLUSTERED INDEX CIX_Bad

ON dbo.Tabelle (GuidSpalte);  -- NIEMALS mit NEWID()

-- ALTERNATIV bei GUID-Pflicht:

ALTER TABLE dbo.Tabelle ADD SurrogateKey INT IDENTITY(1,1);

CREATE CLUSTERED INDEX CIX_Surrogate ON dbo.Tabelle (SurrogateKey);

-- Non-Clustered Index Grundstruktur

CREATE NONCLUSTERED INDEX IX_Bestellungen_Datum_Status

ON dbo.Bestellungen (Bestelldatum, Status)       -- Key Columns (Suche + Sort)

INCLUDE (LieferantNr, Nettobetrag, Notiz);       -- Included Columns (Covering)

-- SPALTENREIHENFOLGE-REGEL (kritisch!):

-- 1. Equality-Spalten zuerst  (WHERE Spalte = Wert)

-- 2. Inequality-Spalten danach (WHERE Spalte > Wert)

-- 3. ORDER BY-Spalten danach   (wenn konsistent mit WHERE)

-- BEISPIEL:

-- Query: WHERE KundenNr = 1234 AND Bestelldatum > '2026-01-01' ORDER BY Nettobetrag

-- Richtig:

CREATE INDEX IX_Richtig ON dbo.Bestellungen

    (KundenNr, Bestelldatum)  -- Equality zuerst, dann Inequality

    INCLUDE (Nettobetrag);    -- ORDER BY als INCLUDE (kein Sort-Operator)

-- Filtered Index: Nur aktuelle, nicht stornierte Aufträge

-- Typisch 10-20% der Gesamtzeilen → Index winzig, Abfragen blitzschnell

CREATE NONCLUSTERED INDEX IX_Auftraege_Offen

ON dbo.Verkaufsauftraege (KundenNr, Auftragsdatum)

INCLUDE (Nettobetrag, Status)

WHERE Status IN ('Offen', 'In Bearbeitung')   -- Filter predicate

  AND Storniert = 0;

-- Ideal für:

-- Soft-Delete-Tabellen: WHERE Geloescht = 0

-- Status-basierte Suche: WHERE Status = 'Aktiv'

-- Aktuelle Daten: WHERE Geschaeftsjahr = 2026

-- Nicht-NULL-Felder: WHERE Email IS NOT NULL

-- ACHTUNG: Query muss exakt denselben Filter enthalten

-- damit der Optimizer den Filtered Index nutzt!

-- Non-Clustered Columnstore (empfohlen für KMU — lässt Rowstore-DML zu)

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FactSales

ON dbo.FactSales (DateKey, ProductKey, CustomerKey,

                  SalesAmount, Quantity, UnitPrice);

-- Clustered Columnstore (gesamte Tabelle als Columnstore — reines DWH)

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales

ON dbo.FactSales;

-- → Alle Spalten automatisch im Index

-- → Kein Rowstore-Index mehr möglich (außer Non-Clustered)

-- WANN COLUMNSTORE:

-- ✓ Tabellen > 1 Mio. Zeilen

-- ✓ Analytische Abfragen: SUM, COUNT, AVG, GROUP BY

-- ✓ Power BI, DeltaMaster, SSRS-Reports

-- ✗ OLTP: viele Einzelzeilen-Lookups (schlechter als Rowstore)

-- ✗ Tabellen mit häufigen Updates einzelner Zeilen

Einzelzeilen-Lookup (PK)

Clustered Index

Bereichssuche + wenig Spalten

Non-Clustered

Bereichssuche + viele Spalten

Non-Clustered + INCLUDE

Nur Teilmenge aktiver Zeilen

Filtered Non-Clustered

Aggregate auf Millionen Zeilen

Non-Clustered Columnstore

Volltext-Suche in VARCHAR

Full-Text Index

Gesamte Tabelle analytisch

Clustered Columnstore

-- NICHT SARGABLE (kein Index möglich):

-- Funktionen auf der linken Seite = Index-Kill

SELECT * FROM Bestellungen WHERE YEAR(Bestelldatum) = 2026;

SELECT * FROM Kunden WHERE LEFT(Name, 3) = 'Mül';

SELECT * FROM Artikel WHERE UPPER(ArtikelNr) = 'ART001';

-- SARGABLE (Index wird genutzt):

SELECT * FROM Bestellungen

WHERE Bestelldatum >= '2026-01-01' AND Bestelldatum < '2027-01-01';

SELECT * FROM Kunden WHERE Name LIKE 'Mül%';  -- Präfix-LIKE: ja

-- ABER: SELECT * WHERE Name LIKE '%Mül%';    -- Enthält-LIKE: NEIN!

-- COMPUTED COLUMN als Lösung für Funktions-Filter:

ALTER TABLE dbo.Kunden ADD NameUpper AS UPPER(Name);

CREATE INDEX IX_Kunden_NameUpper ON dbo.Kunden (NameUpper);

SELECT * FROM dbo.Kunden WHERE NameUpper = 'MÜLLER GMBH'; -- nutzt Index!

-- FALSCH: Alle Spalten in den Key

CREATE INDEX IX_Falsch ON dbo.Bestellungen

    (KundenNr, Bestelldatum, Status, Nettobetrag, LieferantNr, Notiz);

-- Problem: Breiter Key → großer Index → langsame Suche → viele Page Splits

-- RICHTIG: Nur Such-/Sort-Spalten in Key, Rest als INCLUDE

CREATE INDEX IX_Richtig ON dbo.Bestellungen

    (KundenNr, Bestelldatum)          -- Key: Suche und Sort

    INCLUDE (Status, Nettobetrag,      -- INCLUDE: nur für Covering

             LieferantNr);            -- Notiz weglassen (zu groß, selten gebraucht)

-- INCLUDE-REGELN:

-- ✓ Spalten die nur in SELECT stehen (nicht WHERE, ORDER BY, JOIN)

-- ✓ Große Spalten (VARCHAR(MAX), NVARCHAR, XML) → immer INCLUDE, nie Key

-- ✓ LOB-Spalten (TEXT, IMAGE, VARBINARY(MAX)) → können nicht in Key

-- ✗ Nicht: Spalten die häufig aktualisiert werden (teures Index-Update)

-- Problem erkennen: Key Lookup im Execution Plan

-- Bedeutet: Non-Clustered Index gefunden, aber fehlende Spalten

-- → Extra Lookup zum Clustered Index je Zeile → teuer bei hoher Zeilenzahl

-- Diagnose: Welche Spalten fehlen?

-- Im Execution Plan: Key Lookup → Properties → Output List

-- Dort stehen die Spalten die zum INCLUDE hinzugefügt werden müssen

-- Beispiel-Fix:

-- Vorher (Key Lookup wegen fehlender Nettobetrag-Spalte):

CREATE INDEX IX_Alt ON dbo.Bestellungen (KundenNr, Bestelldatum);

-- Nachher (Covering Index → kein Key Lookup mehr):

DROP INDEX IX_Alt ON dbo.Bestellungen;

CREATE INDEX IX_Neu ON dbo.Bestellungen (KundenNr, Bestelldatum)

INCLUDE (Nettobetrag, Status, LieferantNr);

GOLDENE REGELN FÜR INDEX-DESIGN

■  Equality-Spalten vor Inequality-Spalten im Key.

■  Maximale Key-Breite: 5-7 Spalten, < 900 Bytes.

■  Covering Index eliminiert Key Lookups — immer prüfen.

■  Filtered Index wenn < 30 % der Zeilen relevant sind.

■  Nie GUID als Clustered Key — NEWSEQUENTIALID() oder INT IDENTITY.

■  INCLUDE statt Key für Spalten die nur im SELECT erscheinen.

■  Fill Factor 80-90 % für schreibintensive ERP-Tabellen.

EXTERNE FRAGMENTIERUNG (Logical Fragmentation):

  Definition: Logische Seitenreihenfolge ≠ physische Reihenfolge

  Ursache:    Page Splits verschieben Seiten im Speicher

  Auswirkung: Read-Ahead (Prefetch) funktioniert schlechter

              → Mehr Random-I/O statt Sequential-I/O

  Messung:    avg_fragmentation_in_percent in DMV

INTERNE FRAGMENTIERUNG (Page Density):

  Definition: Seiten nur teilweise gefüllt (< Fill Factor)

  Ursache:    Viele Deletes, zu niedriger Fill Factor

  Auswirkung: Mehr Seiten nötig für dieselben Daten → mehr I/O

  Messung:    avg_page_space_used_in_percent in DMV

FAUSTREGEL GRENZWERTE:

  < 5 %   → Nichts tun (Overhead > Nutzen)

  5-30 %  → REORGANIZE (online, geringer Log-Aufwand)

  > 30 %  → REBUILD (vollständige Neuerstellung)

  < 1000 Seiten → Generell nichts tun (zu klein für Nutzen)

-- Vollständige Fragmentierungs-Analyse mit Wartungsempfehlung

SELECT

    OBJECT_SCHEMA_NAME(ips.object_id) + '.' +

    OBJECT_NAME(ips.object_id) AS Tabelle,

    i.name AS Index_Name,

    i.type_desc AS Index_Typ,

    ips.index_level AS Ebene,

    ips.page_count AS Seiten,

    ROUND(ips.avg_fragmentation_in_percent, 1) AS Frag_Pct,

    ROUND(ips.avg_page_space_used_in_percent, 1) AS Auslastung_Pct,

    ips.record_count AS Zeilenanzahl,

    CASE

        WHEN ips.page_count < 1000

            THEN 'Zu klein — ignorieren'

        WHEN ips.avg_fragmentation_in_percent < 5

            THEN 'OK'

        WHEN ips.avg_fragmentation_in_percent < 30

            THEN 'REORGANIZE'

        ELSE 'REBUILD'

    END AS Empfehlung,

    -- Geschätzter Zeitaufwand (grob)

    CASE

        WHEN ips.page_count < 1000 THEN '< 1 Sek.'

        WHEN ips.avg_fragmentation_in_percent >= 30

            THEN CAST(ips.page_count / 5000 + 1 AS VARCHAR) + ' Min. (REBUILD)'

        ELSE CAST(ips.page_count / 10000 + 1 AS VARCHAR) + ' Min. (REORG)'

    END AS Zeitschätzung

FROM sys.dm_db_index_physical_stats(

    DB_ID(), NULL, NULL, NULL, 'LIMITED') ips

JOIN sys.indexes i

    ON ips.object_id = i.object_id

    AND ips.index_id = i.index_id

WHERE ips.index_id > 0           -- Keine Heaps

  AND ips.index_level = 0        -- Nur Leaf-Level

  AND ips.page_count > 100       -- Nur relevante Indizes

ORDER BY ips.avg_fragmentation_in_percent DESC;

Algorithmus

Defragmentiert seitenweise (online)

Löscht und erstellt Index neu

Online?

Immer ja

Ja (Enterprise) / Nein (Standard)

Log-Aufwand

Minimal

Vollständig (kann Log-Backup nötig machen)

Unterbrechbar

Ja — setzt fort

Nein — von vorn wenn abgebrochen

Statistiken

Werden NICHT aktualisiert

Werden automatisch aktualisiert

Fill Factor

Wird NICHT angewendet

Wird angewendet (Neuerstellung)

Einsatz

5-30 % Fragmentierung

> 30 % oder nach Massenoperationen

-- REORGANIZE — online, unterbrechbar

ALTER INDEX IX_Bestellungen_Datum ON dbo.Bestellungen

REORGANIZE;

-- REBUILD — vollständig, mit Fill Factor Anwendung

ALTER INDEX IX_Bestellungen_Datum ON dbo.Bestellungen

REBUILD WITH (FILLFACTOR = 85, ONLINE = ON, SORT_IN_TEMPDB = ON);

-- ALLE Indizes einer Tabelle (mit Bedingung)

ALTER INDEX ALL ON dbo.Bestellungen

REBUILD WITH (FILLFACTOR = 85, ONLINE = ON);

-- Intelligente Wartung: passt sich automatisch an Fragmentierungsgrad an

-- Ola Hallengren IndexOptimize — empfohlene Konfiguration für ERP

EXECUTE dbo.IndexOptimize

    @Databases            = 'USER_DATABASES',

    @FragmentationLow     = NULL,              -- < 5%: nichts tun

    @FragmentationMedium  = 'INDEX_REORGANIZE', -- 5-30%: REORGANIZE

    @FragmentationHigh    = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1  = 5,

    @FragmentationLevel2  = 30,

    @MinNumberOfPages     = 1000,              -- Nur Indizes > 1000 Seiten

    @SortInTempdb         = 'Y',

    @FillFactor           = 85,

    @UpdateStatistics     = 'ALL',             -- Statistiken immer aktualisieren

    @OnlyModifiedStatistics = 'Y',

    @LogToTable           = 'Y',               -- Wartungslog in Tabelle

    @Execute              = 'Y';

-- Missing Index DMV: priorisiert nach Verbesserungspotenzial

-- WICHTIG: Werte seit letztem SQL Server Neustart — bei frischem Neustart unrepräsentativ

SELECT TOP 20

    ROUND(migs.avg_total_user_cost *

          (migs.avg_user_impact / 100.0) *

          (migs.user_seeks + migs.user_scans), 0) AS Verbesserungs_Score,

    migs.user_seeks AS Index_Seeks,

    migs.user_scans AS Index_Scans,

    ROUND(migs.avg_user_impact, 1) AS Avg_Verbesserung_Pct,

    ROUND(migs.avg_total_user_cost, 2) AS Avg_Query_Kosten,

    mid.statement AS Tabelle,

    mid.equality_columns AS Equality_Spalten,

    mid.inequality_columns AS Inequality_Spalten,

    mid.included_columns AS Include_Spalten,

    -- Fertiger CREATE INDEX Befehl (Vorlage — Name anpassen!)

    'CREATE NONCLUSTERED INDEX [IX_' +

    REPLACE(REPLACE(OBJECT_NAME(mid.object_id), ' ', '_'), '.', '_') +

    '_' + REPLACE(ISNULL(mid.equality_columns, '') +

    ISNULL('_' + mid.inequality_columns, ''), ', ', '_') + ']' +

    ' ON ' + mid.statement +

    ' (' + ISNULL(mid.equality_columns, '') +

    CASE WHEN mid.inequality_columns IS NOT NULL

         AND mid.equality_columns IS NOT NULL THEN ', ' ELSE '' END +

    ISNULL(mid.inequality_columns, '') + ')' +

    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_group_stats migs

JOIN sys.dm_db_missing_index_groups mig

    ON migs.group_handle = mig.index_group_handle

JOIN sys.dm_db_missing_index_details mid

    ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost *

      (migs.avg_user_impact / 100.0) *

      (migs.user_seeks + migs.user_scans) > 100

ORDER BY Verbesserungs_Score DESC;

-- Ungenutzte Indizes: reads = 0 seit Neustart, aber Schreiblast vorhanden

-- ACHTUNG: Erst nach mindestens 2-4 Wochen Betrieb aussagekräftig!

SELECT

    OBJECT_SCHEMA_NAME(i.object_id) + '.' +

    OBJECT_NAME(i.object_id) AS Tabelle,

    i.name AS Index_Name,

    i.type_desc AS Typ,

    -- Lesezugriffe (0 = nie genutzt)

    ISNULL(ius.user_seeks, 0) AS Seeks,

    ISNULL(ius.user_scans, 0) AS Scans,

    ISNULL(ius.user_lookups, 0) AS Lookups,

    -- Schreiblast (Kosten für das Maintainen des Index)

    ISNULL(ius.user_updates, 0) AS Updates,

    -- Speicherverbrauch

    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,1)) AS Groesse_MB,

    -- Erstellungsdatum

    i.create_date AS Erstellt_Am,

    -- Empfehlung

    CASE

        WHEN ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) +

             ISNULL(ius.user_lookups, 0) = 0

         AND ISNULL(ius.user_updates, 0) > 1000

            THEN 'DROP CANDIDATE — kostet nur Schreibperformance'

        WHEN ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) +

             ISNULL(ius.user_lookups, 0) = 0

            THEN 'Prüfen — seit Neustart nie genutzt'

        ELSE 'Aktiv'

    END AS Empfehlung

FROM sys.indexes i

JOIN sys.objects o ON i.object_id = o.object_id

LEFT JOIN sys.dm_db_index_usage_stats ius

    ON i.object_id = ius.object_id

    AND i.index_id = ius.index_id

    AND ius.database_id = DB_ID()

LEFT JOIN sys.partitions p ON i.object_id = p.object_id

    AND i.index_id = p.index_id

LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE o.type = 'U'                   -- Nur User-Tabellen

  AND i.index_id > 0                 -- Keine Heaps

  AND i.is_primary_key = 0           -- PKs nie droppen

  AND i.is_unique_constraint = 0     -- Unique Constraints nie droppen

  AND o.is_ms_shipped = 0

GROUP BY i.object_id, i.name, i.type_desc, i.create_date,

         ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates

HAVING ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) +

       ISNULL(ius.user_lookups, 0) = 0

ORDER BY ISNULL(ius.user_updates, 0) DESC;

-- Doppelte und überlappende Indizes (gleiche oder enthaltene Schlüsselspalten)

WITH IndexSpalten AS (

    SELECT

        i.object_id,

        i.index_id,

        i.name AS IndexName,

        i.type_desc,

        STRING_AGG(

            CAST(ic.key_ordinal AS VARCHAR) + ':' + c.name +

            CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ASC' END,

            '|'

        ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS Key_Spalten,

        STRING_AGG(

            CASE ic.is_included_column WHEN 1 THEN c.name ELSE NULL END,

            ','

        ) AS Include_Spalten

    FROM sys.indexes i

    JOIN sys.index_columns ic ON i.object_id = ic.object_id

        AND i.index_id = ic.index_id

    JOIN sys.columns c ON ic.object_id = c.object_id

        AND ic.column_id = c.column_id

    WHERE i.type > 0 AND i.is_primary_key = 0

    GROUP BY i.object_id, i.index_id, i.name, i.type_desc

)

SELECT

    OBJECT_SCHEMA_NAME(a.object_id) + '.' +

    OBJECT_NAME(a.object_id) AS Tabelle,

    a.IndexName AS Index_A,

    b.IndexName AS Index_B,

    a.Key_Spalten AS Keys_A,

    b.Key_Spalten AS Keys_B,

    'MÖGLICHER DUPLIKAT — prüfen und ggf. einen droppen' AS Hinweis

FROM IndexSpalten a

JOIN IndexSpalten b

    ON a.object_id = b.object_id

    AND a.index_id < b.index_id       -- Jedes Paar nur einmal

    AND (b.Key_Spalten LIKE a.Key_Spalten + '%'   -- B enthält A als Präfix

      OR a.Key_Spalten LIKE b.Key_Spalten + '%')  -- A enthält B als Präfix

ORDER BY Tabelle, Index_A;

-- Index-Effizienz: Reads vs. Writes Verhältnis

SELECT TOP 30

    OBJECT_SCHEMA_NAME(i.object_id) + '.' +

    OBJECT_NAME(i.object_id) AS Tabelle,

    i.name AS Index_Name,

    ISNULL(ius.user_seeks, 0) +

    ISNULL(ius.user_scans, 0) +

    ISNULL(ius.user_lookups, 0) AS Gesamt_Reads,

    ISNULL(ius.user_updates, 0) AS Gesamt_Writes,

    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,1)) AS MB,

    CASE

        WHEN ISNULL(ius.user_updates, 0) = 0 THEN 'N/A'

        ELSE CAST(

            ROUND((ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) +

                   ISNULL(ius.user_lookups,0)) * 1.0 /

                  NULLIF(ius.user_updates, 0), 1) AS VARCHAR)

    END AS Read_Write_Ratio,

    CASE

        WHEN (ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) +

              ISNULL(ius.user_lookups,0)) /

             NULLIF(ius.user_updates, 0) > 100

            THEN 'Sehr effizient'

        WHEN (ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) +

              ISNULL(ius.user_lookups,0)) /

             NULLIF(ius.user_updates, 0) BETWEEN 10 AND 100

            THEN 'Effizient'

        WHEN (ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) +

              ISNULL(ius.user_lookups,0)) /

             NULLIF(ius.user_updates, 0) < 1

            THEN 'Ineffizient — DROP prüfen'

        ELSE 'Prüfen'

    END AS Bewertung

FROM sys.indexes i

JOIN sys.objects o ON i.object_id = o.object_id

LEFT JOIN sys.dm_db_index_usage_stats ius

    ON i.object_id = ius.object_id AND i.index_id = ius.index_id

    AND ius.database_id = DB_ID()

LEFT JOIN sys.partitions p ON i.object_id = p.object_id

    AND i.index_id = p.index_id

LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE o.type = 'U' AND i.index_id > 0

  AND i.is_primary_key = 0 AND i.is_unique_constraint = 0

GROUP BY i.object_id, i.name, ius.user_seeks, ius.user_scans,

         ius.user_lookups, ius.user_updates

ORDER BY Gesamt_Writes DESC;

COLUMNSTORE-ARCHITEKTUR:

COLUMN SEGMENT:

  Jede Spalte wird separat gespeichert.

  Jedes Segment enthält ~1 Mio. Zeilen (Row Group).

  Kompressionsverhältnis: 5-10× gegenüber Rowstore.

  Minimum/Maximum je Segment → ermöglicht Segment Elimination.

ROW GROUP:

  Gruppe von bis zu 1.048.576 Zeilen.

  Compressed Row Group: permanent gespeichert, hoch komprimiert.

  Delta Store: temporärer B-Tree für neue Zeilen (< 1 Mio.)

  Deleted Bitmap: markiert gelöschte Zeilen (kein sofortiges Remove).

TUPLE MOVER:

  Hintergrundprozess: schiebt Delta Store → Compressed Row Group.

  Läuft automatisch alle 5 Minuten.

  Manuell: ALTER INDEX ... REORGANIZE (komprimiert Delta Stores sofort).

BATCH MODE EXECUTION:

  SQL Server verarbeitet 64-900 Zeilen gleichzeitig (statt 1 bei Row Mode).

  Bis zu 10× CPU-Effizienz bei analytischen Abfragen.

  Aktiviert automatisch wenn Columnstore-Index vorhanden.

  Ab SQL Server 2019: Batch Mode on Rowstore (ohne Columnstore-Index).

-- Segment Elimination prüfen: Wie viele Segments werden übersprungen?

SELECT

    OBJECT_NAME(i.object_id) AS Tabelle,

    i.name AS Index_Name,

    SUM(css.row_count) AS Gesamt_Zeilen,

    COUNT(css.segment_id) AS Anzahl_Segments,

    SUM(CASE WHEN css.has_nulls = 1 THEN 1 ELSE 0 END) AS Segments_Mit_Nulls,

    MIN(css.min_data_id) AS Min_Wert,

    MAX(css.max_data_id) AS Max_Wert

FROM sys.column_store_segments css

JOIN sys.partitions p ON css.partition_id = p.partition_id

JOIN sys.indexes i ON p.object_id = i.object_id

    AND p.index_id = i.index_id

JOIN sys.columns c ON i.object_id = c.object_id

    AND css.column_id = c.column_id

WHERE i.type IN (5, 6)             -- Columnstore-Typen

  AND c.name IN ('DateKey', 'OrderDate', 'Bestelldatum') -- Datum-Spalten

GROUP BY i.object_id, i.name, c.name

ORDER BY Tabelle, Index_Name;

-- Tipp: Daten chronologisch laden → Segmente haben enge Datum-Ranges

-- → Mehr Segment Elimination bei Date-Filtern → dramatisch schneller

-- Row Group Qualität überwachen (Delta Stores und kleine Row Groups)

SELECT

    OBJECT_NAME(i.object_id) AS Tabelle,

    i.name AS Index_Name,

    rg.state_description AS Status,

    rg.total_rows AS Zeilen,

    rg.deleted_rows AS Geloeschte_Zeilen,

    ROUND(100.0 * rg.deleted_rows /

          NULLIF(rg.total_rows, 0), 1) AS Delete_Pct,

    rg.size_in_bytes / 1024 / 1024 AS Groesse_MB,

    CASE

        WHEN rg.state_description = 'OPEN'

            THEN 'Delta Store — wartet auf Komprimierung'

        WHEN rg.deleted_rows * 1.0 / NULLIF(rg.total_rows,0) > 0.2

            THEN 'Viele Deletes → REBUILD empfohlen'

        WHEN rg.total_rows < 100000

            THEN 'Kleine Row Group → REBUILD für bessere Kompression'

        ELSE 'OK'

    END AS Empfehlung

FROM sys.dm_db_column_store_row_group_physical_stats rg

JOIN sys.indexes i ON rg.object_id = i.object_id

    AND rg.index_id = i.index_id

WHERE rg.state_description IN ('OPEN', 'COMPRESSED', 'TOMBSTONE')

ORDER BY Tabelle, rg.state_description;

HÄUFIGE GRÜNDE WARUM DER OPTIMIZER EINEN INDEX NICHT NUTZT:

1. STATISTIKEN VERALTET

   Optimizer schätzt 100 Zeilen → Index lohnt sich.

   Tatsächlich: 1 Mio. Zeilen → Table Scan wäre besser.

   Fix: UPDATE STATISTICS TableName WITH FULLSCAN

2. PARAMETER SNIFFING

   Erster Aufruf mit Wert der 1 Zeile trifft → Nested Loops Plan gecacht.

   Nächster Aufruf mit Wert der 500.000 Zeilen trifft → Plan falsch!

   Fix: OPTION (OPTIMIZE FOR UNKNOWN) oder RECOMPILE

3. KARDINALITÄTSSCHÄTZUNG (CE) FALSCH

   Mehrere korrelierte Filter: WHERE Jahr = 2026 AND Monat = 1

   CE nimmt Unabhängigkeit an → unterschätzt Selektivität.

   Fix: Compatibility Level 170 + Query Store

4. IMPLICIT CONVERSION

   Index auf INT-Spalte, Query vergleicht mit VARCHAR.

   SQL Server konvertiert implizit → Index kann nicht genutzt werden.

   Fix: Datentypen angleichen oder CAST in Query ergänzen

5. INDEX ZU KLEIN / STATISTICS THRESHOLD

   Auto Update Statistics bei kleinen Tabellen zu selten.

   Fix: AUTO_UPDATE_STATISTICS_ASYNC ON + manuelles Update nach Bulk-Load

-- Tatsächlichen Execution Plan einschalten

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

-- Plan nach diesen Warnsignalen absuchen:

-- ⚠ Table Scan / Clustered Index Scan: fehlender oder nicht nutzbarer Index

-- ⚠ Key Lookup: Non-Clustered Index unvollständig (INCLUDE fehlt)

-- ⚠ Estimated vs. Actual Rows > 10× Differenz: Statistiken veraltet

-- ⚠ Sort-Operator mit hohem Memory Grant: Index mit passendem ORDER BY fehlt

-- ⚠ Hash Match (Join): Statistiken schlecht oder Join-Spalten nicht indiziert

-- ⚠ Parallelism (CXPACKET-Waits): MAXDOP zu hoch oder Cost Threshold zu niedrig

-- Query Hints zur Diagnose (NIE in Produktion dauerhaft!):

SELECT * FROM dbo.Bestellungen WITH (INDEX(IX_Bestellungen_Datum))

WHERE Bestelldatum > '2026-01-01'; -- Erzwingt bestimmten Index

SELECT * FROM dbo.Bestellungen WITH (NOLOCK)  -- Liest ohne Sperren

WHERE Bestelldatum > '2026-01-01'; -- Für Diagnose-Abfragen

-- Veraltete Statistiken identifizieren (> 7 Tage oder > 20% Zeilenänderung)

SELECT

    OBJECT_NAME(s.object_id) AS Tabelle,

    s.name AS Statistik_Name,

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

    DATEDIFF(DAY, STATS_DATE(s.object_id, s.stats_id), GETDATE()) AS Tage_Alt,

    sp.rows AS Zeilenanzahl,

    sp.rows_sampled AS Gesampled,

    ROUND(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0), 1) AS Sample_Pct,

    sp.modification_counter AS Aenderungen_Seit_Update

FROM sys.stats s

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

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

  AND sp.rows > 10000

  AND (sp.modification_counter > sp.rows * 0.20  -- > 20% geändert

    OR DATEDIFF(DAY, STATS_DATE(s.object_id, s.stats_id), GETDATE()) > 30)

ORDER BY sp.modification_counter DESC;

-- Statistiken mit FULLSCAN aktualisieren (kritische Tabellen)

-- Für alle Tabellen: Ola Hallengren UpdateStatistics Job

UPDATE STATISTICS dbo.Bestellungen WITH FULLSCAN;

UPDATE STATISTICS dbo.Verkaufsbelege WITH FULLSCAN;

-- Täglicher Index-Health-Score je Datenbank

-- Kombiniert Fragmentierung, ungenutzte Indizes und fehlende Indizes

DECLARE @DB NVARCHAR(100) = DB_NAME();

SELECT

    @DB AS Datenbank,

    CAST(GETDATE() AS DATE) AS Messdatum,

    -- Fragmentierungs-Score (100 = perfekt, sinkt mit Fragmentierung)

    100 - ROUND(AVG(CASE WHEN page_count > 1000

                    THEN avg_fragmentation_in_percent ELSE 0 END), 1)

        AS Frag_Score,

    -- Unused Index Count (0 = gut)

    (SELECT COUNT(*) FROM sys.dm_db_index_usage_stats ius

     JOIN sys.indexes i ON ius.object_id = i.object_id

         AND ius.index_id = i.index_id

     WHERE ius.database_id = DB_ID()

       AND ius.user_seeks + ius.user_scans + ius.user_lookups = 0

       AND ius.user_updates > 1000

       AND i.is_primary_key = 0) AS Unused_Index_Count,

    -- Missing Index Count (0 = gut)

    (SELECT COUNT(*) FROM sys.dm_db_missing_index_group_stats migs

     JOIN sys.dm_db_missing_index_groups mig

         ON migs.group_handle = mig.index_group_handle

     JOIN sys.dm_db_missing_index_details mid

         ON mig.index_handle = mid.index_handle

     WHERE migs.avg_total_user_cost *

           (migs.avg_user_impact / 100.0) *

           (migs.user_seeks + migs.user_scans) > 1000)

        AS Missing_High_Impact

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')

WHERE index_level = 0;

-- Abfragen die nach einem bestimmten Datum langsamer wurden

-- (z.B. nach Index-Drop oder ERP-Update)

SELECT TOP 20

    qsq.query_id,

    SUBSTRING(qsqt.query_sql_text, 1, 200) AS Query_Text,

    qsrs.avg_duration / 1000.0 AS Avg_Ms_Aktuell,

    qsrs_prev.avg_duration / 1000.0 AS Avg_Ms_Vorher,

    ROUND((qsrs.avg_duration - qsrs_prev.avg_duration) /

          NULLIF(qsrs_prev.avg_duration, 0) * 100, 1) AS Verschlechterung_Pct,

    qsrs.avg_logical_io_reads AS IO_Reads_Aktuell,

    qsrs.count_executions AS Ausfuehrungen

FROM sys.query_store_query qsq

JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id

JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id

JOIN sys.query_store_runtime_stats qsrs

    ON qsp.plan_id = qsrs.plan_id

JOIN sys.query_store_runtime_stats_interval qsrsi

    ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id

JOIN sys.query_store_runtime_stats qsrs_prev

    ON qsp.plan_id = qsrs_prev.plan_id

JOIN sys.query_store_runtime_stats_interval qsrsi_prev

    ON qsrs_prev.runtime_stats_interval_id = qsrsi_prev.runtime_stats_interval_id

WHERE qsrsi.start_time >= DATEADD(DAY, -3, GETDATE())   -- Aktuell: letzte 3 Tage

  AND qsrsi_prev.start_time BETWEEN DATEADD(DAY, -14, GETDATE())

      AND DATEADD(DAY, -3, GETDATE())                    -- Vorher: 14-3 Tage

  AND qsrs.avg_duration > qsrs_prev.avg_duration * 1.5   -- > 50% langsamer

  AND qsrs.count_executions > 10

ORDER BY Verschlechterung_Pct DESC;

-- SQL Agent Job: Tägliche Index-Health-Prüfung mit Alert

DECLARE @Alerts NVARCHAR(MAX) = '';

-- Alert 1: Hoch fragmentierte Indizes

IF EXISTS (

    SELECT 1 FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED')

    WHERE avg_fragmentation_in_percent > 50 AND page_count > 10000

)

SET @Alerts = @Alerts + '⚠ KRITISCH: Indizes mit > 50% Fragmentierung vorhanden' + CHAR(13);

-- Alert 2: Fehlende Indizes mit hohem Impact

IF (SELECT COUNT(*) FROM sys.dm_db_missing_index_group_stats migs

    JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle

    WHERE migs.avg_total_user_cost * (migs.avg_user_impact/100.0) *

          (migs.user_seeks + migs.user_scans) > 10000) > 5

SET @Alerts = @Alerts + '⚠ WARNUNG: > 5 fehlende Indizes mit hohem Impact' + CHAR(13);

IF LEN(@Alerts) > 0

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'DBA-Alerts',

        @recipients   = 'dba@firma.de',

        @subject      = 'Index-Health-Alert: ' + DB_NAME(),

        @body         = @Alerts;

VOR DEM START

Vollständiges Datenbankbackup erstellen. Query Store auf allen produktiven Datenbanken aktivieren — mindestens 2 Wochen Daten sammeln bevor Entscheidungen getroffen werden. Alle DMV-Auswertungen brauchen repräsentative Laufzeiten seit letztem Neustart.

ERGEBNIS NACH 30 TAGEN

Nach 30 Tagen haben Sie ein bereinigtes Index-Inventar ohne Ballast, die wichtigsten Missing Indexes angelegt, eine automatische Wartungsstrategie und ein Monitoring das Index-Probleme proaktiv meldet. Typisches Ergebnis: 20-50 % weniger Schreiblast durch entfernte ungenutzte Indizes, 30-80 % schnellere kritische Abfragen durch neue oder optimierte Indizes.

SQL Server DB Health Check

Professioneller Audit inkl. vollständigem Index-Review — Managementreport, Risikobewertung, priorisierter Maßnahmenplan. Scope: 3-5 Tage.

SQL Server Performance-Tuning

Tiefgehende Analyse und Optimierung von Indexstrategie, Query-Plänen und Serverkonfiguration — messbare Ergebnisse in Tagen.

ERP-Datenbankoptimierung

Spezialisiertes Tuning für ERP-Datenbanken (MACH, APplus, Dynamics NAV/BC) — Indexanalyse, Statistiken, Wartungsautomatisierung.

BI-Dashboard-Aufbau

Power BI / DeltaMaster inkl. Columnstore-Optimierung für maximale Query-Performance auf großen Datenmengen.

Interim IT-Management

Übernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT-Ausrichtung.