SQL Server Indexoptimierung: den passenden Index auswählen

Kurz gesagt

Beginnen Sie mit der Aufgabe und einem tatsächlichen Ausführungsplan. Prüfen Sie Filter, Verknüpfungen, Sortierung und die gelesene Datenmenge. Daraus entsteht ein Kandidat für die Schlüsselspalten und gegebenenfalls für eingeschlossene Spalten (INCLUDE). Bewerten Sie den Kandidaten danach nicht nur an der Abfrage, für die er gedacht ist. Messen Sie alle Abfragen, die dieselbe Tabelle verwenden, und die Datenänderungen. Ein neuer Index muss sich im ganzen Workload bewähren.

Welche Abfrage soll schneller werden?

Unser Beispiel ist eine Bestelltabelle mit gut 2 Millionen Zeilen, rund 700 MB groß. Zehn große Kunden haben je 100.000 Bestellungen, 20.000 kleine Kunden je 1 bis 99. Diese ungleiche Verteilung ist in vielen Anwendungen normal: wenige Großkunden, Mandanten oder Filialen tragen einen großen Teil der Daten.

Die Anwendung zeigt die Bestellungen eines Kunden für einen Monat:

SELECT COUNT(*) AS Anzahl, MAX(BestellId) AS LetzteFROM dbo.BestellungenWHERE KundeId = @k  AND Bestelldatum >= @von AND Bestelldatum < @bis;

Die Tabelle hat nur ihren gruppierten Primärschlüssel auf BestellId. Diese Abfrage nennen wir im Folgenden Abfrage A. Sie braucht auf SQL Server 2022 mit Kompatibilitätsgrad 150 rund 70 ms, für kleine wie für große Kunden.

Was der Ausführungsplan über den Zugriff zeigt

Der Plan zeigt einen parallelen Clustered Index Scan mit 87.661 Lesevorgängen je Aufruf. Ein kleiner Kunde hat in einem Monat höchstens eine Handvoll Bestellungen, ein großer etwa 2.700. Um diese wenigen Zeilen zu finden, liest die Abfrage jedes Mal alle Seiten der Tabelle.

Dieses Verhältnis ist das eigentliche Signal: gelesene Seiten gegenüber gelieferten Zeilen. Die gelesenen Seiten zeigt SET STATISTICS IO ON oder der tatsächliche Plan, die gelieferten Zeilen stehen im Plan als tatsächliche Zeilenzahl. Ein Scan ist nicht von sich aus schlecht. Er wird zum Problem, wenn er für ein kleines Ergebnis die ganze Tabelle liest und die Abfrage oft läuft.

Bevor Sie einen Index anlegen, prüfen Sie zwei andere Ursachen:

• Schätzfehler. Liegen geschätzte und tatsächliche Zeilen weit auseinander, kann der Optimizer einen vorhandenen Index für ungeeignet halten. Dann hilft zuerst eine Statistik oder eine andere Formulierung, nicht ein weiterer Index.
• Nicht durchsuchbare Bedingungen. Eine Funktion auf der Spalte, etwa YEAR(Bestelldatum) = 2026, oder ein abweichender Datentyp kann einen passenden Index unbrauchbar machen. Dann ist die Abfrage der Hebel.

Wie Sie Pläne lesen und Schätzungen beurteilen, zeigt der Optimizer-Einstieg.

Den Indexschlüssel aus den Zugriffen ableiten

Ein nicht gruppierter Index ist eine eigene, sortierte Struktur. Die Schlüsselspalten bestimmen die Reihenfolge, in der SQL Server darin suchen kann. Leiten Sie sie aus dem ab, was die Abfrage tut: Welche Spalten werden mit = verglichen, welche mit einem Bereich, welche sortiert oder verknüpft?

Gleichheit, Bereich und Sortierung gemeinsam betrachten

Abfrage A vergleicht KundeId auf Gleichheit und Bestelldatum auf einen Bereich. Daraus folgt der Kandidat:

CREATE INDEX IX_Kunde_Datum ON dbo.Bestellungen (KundeId, Bestelldatum);

Mit dieser Reihenfolge springt SQL Server direkt zum Kunden und liest darin nur den Monat. Andersherum, mit Bestelldatum vorn, müsste er den ganzen Monat aller Kunden lesen und dort nach dem Kunden filtern. Die Faustregel „Gleichheit vor Bereich“ ist ein guter Start, aber keine feste Regel. Sortierung, Verknüpfungen und andere Abfragen auf derselben Tabelle können eine andere Reihenfolge nahelegen. Auch „die selektivste Spalte zuerst“ ist unter mehreren Gleichheitsspalten ein sinnvoller Ausgangspunkt, aber kein Gesetz.

Mit dem Kandidaten brauchte Abfrage A im selben Lauf 0,2 bis 0,6 ms statt rund 70 ms, mit 3 bis 10 statt 87.661 Lesevorgängen. Über alle Versionen und Kompatibilitätsgrade lag sie bei 0 bis 2,3 ms mit 3 bis 13 Lesevorgängen.

Welche Spalten in INCLUDE gehören

Abfrage A gibt COUNT(*) und MAX(BestellId) aus. BestellId steht in keinem Indexschlüssel, und trotzdem muss SQL Server nicht in der Tabelle nachschlagen: Der gruppierte Schlüssel steckt in jedem nicht gruppierten Index, weil er auf die Zeile verweist. Bei einer Tabelle ohne gruppierten Index, einem Heap, steht dort stattdessen ein Zeilenverweis. Der Kandidat deckt Abfrage A damit vollständig ab.

Braucht eine Abfrage weitere Spalten, die nicht im Index stehen, holt SQL Server sie je Zeile aus der Tabelle. Im Plan heißt das Key Lookup. Spalten, die nur ausgegeben oder summiert werden, können Sie mit INCLUDE an die unterste Ebene des Index hängen. Sie verbreitern ihn, ohne die Sortierung zu ändern. Das spart Lookups, kostet aber Platz und Schreibarbeit. Wie viel, zeigt das Beispiel weiter unten.

Wann ein gefilterter Index infrage kommt

Für eine kleine, klar umrissene Teilmenge, etwa nur die offenen Bestellungen, kann ein gefilterter Index (WHERE Status = 1) deutlich kleiner und billiger in der Pflege sein. Er hat eine Einschränkung, die oft übersehen wird: Die Bedingung der Abfrage muss in den Filter des Index fallen. Bei einer parametrisierten Abfrage muss der zwischengespeicherte Plan für jeden möglichen Parameterwert gelten. Kann der Parameter auch Werte außerhalb des Filters annehmen, verwendet der Optimizer den gefilterten Index deshalb meist nicht, außer mit OPTION (RECOMPILE). Außerdem verlangen Änderungen an Tabellen mit gefiltertem Index bestimmte SET-Optionen, etwa QUOTED_IDENTIFIER ON. Ältere Anwendungen mit abweichenden Einstellungen können dann beim Schreiben scheitern. Prüfen Sie einen gefilterten Index deshalb mit dem tatsächlichen Aufruf der Anwendung. Diesen Fall haben wir in diesem Lab nicht gemessen.

Vorhandene Indizes und Missing-Index-Hinweise prüfen

Bevor ein neuer Index entsteht, lohnt der Blick auf die vorhandenen. Oft gibt es schon einen Index mit derselben ersten Spalte, der sich erweitern lässt, statt einen zweiten fast gleichen anzulegen. sys.dm_db_index_usage_stats zeigt, wie oft ein Index gelesen und geändert wurde. Die Zähler beginnen beim Neustart der Instanz von vorn, ebenso wenn die Datenbank getrennt oder geschlossen wird. Ein Index erscheint dort erst nach seiner ersten Verwendung. Ein seltener Monatsabschluss kann deshalb fehlen.

Die Missing-Index-Hinweise im Plan und in sys.dm_db_missing_index_details sind ein guter Ausgangspunkt, aber kein Bauauftrag. Microsoft beschreibt ihre Grenzen selbst:

• Sie entstehen bei der Optimierung einer einzelnen Abfrage und beruhen auf Schätzungen, nicht auf gemessener Laufzeit.
• Sie legen keine Reihenfolge der Schlüsselspalten fest. Gefilterte und eindeutige Indizes schlagen sie nicht vor.
• Die gesammelten Hinweise einer Tabelle verschwinden, sobald sich ihre Definition ändert, also auch nach einem CREATE INDEX, und beim Neustart der Instanz.
• Ähnliche Vorschläge überschneiden sich häufig und sollten zusammengeführt werden.

Der wichtigste Punkt fehlt in dieser Liste: Ein Hinweis sieht die anderen Abfragen auf der Tabelle nicht. Er sagt, was dieser einen Abfrage helfen würde. Was derselbe Index mit allen anderen macht, sagt er nicht. Wie wir diese Lücke in unseren Analysen schließen, steht weiter unten.

Ein Index ist kein lokales Objekt

Sie legen einen Index vielleicht für eine einzige Abfrage an und messen dort eine deutliche Verbesserung. Ab diesem Moment steht er aber jeder Abfrage zur Verfügung, die dieselbe Tabelle verwendet. Der Optimizer prüft ihn bei jeder neuen Kompilierung als mögliche Alternative. Für manche Abfragen ist das ein Gewinn. Andere erhalten einen Plan, der nur für bestimmte Parameterwerte gut ist und bei der Mehrheit der Aufrufe mehr kostet als vorher. Mit jedem zusätzlichen Index wächst die Zahl solcher Entscheidungen. Deshalb prüfen wir eine Indexänderung nie nur an der Abfrage, für die sie gedacht war.

Leicht übersehen wird das, weil die üblichen Werkzeuge auf eine Abfrage schauen: der Missing-Index-Hinweis, der einzelne Ausführungsplan, der Vorher-nachher-Test der Zielabfrage. Im Lab haben wir es deshalb an einer zweiten Abfrage gemessen, die von dem neuen Index nichts wissen sollte.

Jede Abfrage auf der Tabelle sieht den neuen Index

Neben Abfrage A läuft in der Anwendung eine Umsatzauswertung je Kunde, Abfrage B:

SELECT SUM(Betrag) AS Summe, COUNT(*) AS AnzahlFROM dbo.BestellungenWHERE KundeId = @k;

Ohne zusätzlichen Index hat B genau einen Weg: den Scan der ganzen Tabelle. Das ist nicht schnell, aber stabil. Jeder Aufruf kostet rund 88.000 Lesevorgänge, egal für welchen Kunden.

Nach CREATE INDEX IX_Kunde_Datum für Abfrage A hatte B einen zweiten Weg. Sie konnte über den neuen Index die Zeilen des Kunden finden und Betrag je Zeile per Key Lookup aus der Tabelle holen. Das hat sie in allen Läufen sofort getan.

Der Wechsel kommt sofort

Das Anlegen eines Index ändert die Definition der Tabelle. Zwischengespeicherte Pläne, die die Tabelle verwenden, werden bei ihrer nächsten Ausführung neu kompiliert. Microsoft nennt Änderungen an einer verwendeten Tabelle als Grund für eine Neukompilierung. Im Lab meldete das Ereignis sql_statement_recompile für Abfrage B nach dem Anlegen des Index den Grund Schema changed, auf SQL Server 2019, 2022 und 2025. Die erste Ausführung danach lief bereits mit dem neuen Index und Key Lookup.

Der Effekt zeigt sich also nicht schleichend, sondern direkt nach dem Deployment. Und er trifft Abfragen, die niemand getestet hat, weil niemand sie mit der Änderung in Verbindung gebracht hat.

Neue Planalternativen bedeuten neue Kipppunkte

Für einen kleinen Kunden mit 50 Bestellungen ist der Weg über den Index ideal: 50 Lookups statt 88.000 Seiten. Für einen großen Kunden mit 100.000 Bestellungen sind es 100.000 Lookups, und die dauern länger als der Scan. Irgendwo dazwischen liegt ein Kipppunkt: die Zeilenzahl, ab der der Scan günstiger ist als Seek plus Lookup.

Welcher Plan im Cache liegt, entscheidet der erste Aufruf nach der Kompilierung. Alle folgenden Aufrufe verwenden ihn, bis er wieder neu kompiliert wird, etwa nach einem Neustart, einer Statistikaktualisierung oder wenn der Plan aus dem Cache verdrängt wurde. Jedes Mal entscheidet wieder der erste Aufruf. Wir haben B im Lab nach dem Index zuerst mit einem kleinen und in einem zweiten Durchgang zuerst mit einem großen Kunden kompiliert und danach jeweils 100 Aufrufe gemessen, 80 für große und 20 für kleine Kunden:

-- Abfrage B, in allen Reitern unverändertSELECT SUM(Betrag) AS Summe, COUNT(*) AS AnzahlFROM dbo.BestellungenWHERE KundeId = @k;-- Plan für alle Kunden: Clustered Index Scan

CREATE INDEX IX_Kunde_Datum    ON dbo.Bestellungen (KundeId, Bestelldatum); -- erster Aufruf von B nach dem Index: kleiner KundeEXEC sp_executesql @B, N'@k int', @k = 11;  -- Seek + Lookup, bleibt im Cache-- alle weiteren Aufrufe nutzen diesen Plan, auch große Kunden

CREATE INDEX IX_Kunde_Datum    ON dbo.Bestellungen (KundeId, Bestelldatum); -- erster Aufruf von B nach dem Index: großer KundeEXEC sp_executesql @B, N'@k int', @k = 1;   -- Scan, bleibt im Cache-- alle weiteren Aufrufe nutzen diesen Plan, auch kleine Kunden

CREATE INDEX IX_Kunde_Datum_Betrag    ON dbo.Bestellungen (KundeId, Bestelldatum)    INCLUDE (Betrag);                        -- kein Lookup mehr nötig -- Plan für alle Kunden, in jeder Reihenfolge: Index Seek

Über alle Versionen bis Kompatibilitätsgrad 150 ergab sich dasselbe Bild:

• Zuerst ein kleiner Kunde: Große Kunden lasen 3,6-mal so viele Seiten und liefen 2,4- bis 3,3-mal so lange wie ohne Index. Die Rechenzeit war dabei sogar etwas geringer als beim parallelen Scan, der Lookup-Plan lief aber seriell und las viel mehr Seiten. Die Summe aller 100 Aufrufe stieg auf das 2,0- bis 2,8-Fache. Ein Index, der Abfrage A von 70 ms auf unter 1 ms gebracht hat, hat die Kosten von Abfrage B im selben Moment mindestens verdoppelt.
• Zuerst ein großer Kunde: B blieb beim Scan, auch für die kleinen Kunden. Schlechter als ohne Index war das nicht, aber der mögliche Gewinn ging an ihnen vorbei. Rufen überwiegend kleine Kunden B auf, ist das der Plan, der für die Mehrheit der Ausführungen ungünstig ist. Rechnerisch, bei 95 % kleinen Aufrufen, läge der Mittelwert bei 61 bis 77 ms statt 11 bis 13 ms im anderen Cachezustand.

Welcher der beiden Fälle eintritt, hängt nicht vom Index ab, sondern davon, welcher Benutzer nach dem Deployment und nach jeder späteren Neukompilierung als Erster klickt. Ohne den Index gab es für B keinen Kipppunkt. Der Index für A hat ihn erst erzeugt. Wie Sie solche parameterabhängigen Pläne erkennen, das klassische Parameter Sniffing, beschreibt der Ratgeber Mal schnell, mal langsam.

Neue Statistik, andere Schätzungen

Mit jedem Index entsteht eine Statistik auf seinen Schlüsselspalten, beim Anlegen aus allen Zeilen berechnet. Der Optimizer verwendet sie auch für Abfragen, die den Index gar nicht lesen. Im Lab las eine dritte Abfrage per Hinweis nur den gruppierten Index. Welche Statistik der Optimizer dabei lädt, haben wir mit dem nicht dokumentierten Trace Flag 2363 sichtbar gemacht, das nur in Testumgebungen gehört. Vor dem neuen Index nutzte er für KundeId das Histogramm der automatisch erstellten Stichprobenstatistik, danach in allen Läufen das Histogramm der Indexstatistik.

Ob sich die Schätzung dadurch merklich ändert, hängt vom Stand beider Statistiken ab. In einem Probelauf mit frischer, vollständiger Indexstatistik ergab deren Histogramm für vier kleine Kunden 34 bis 50 Zeilen, das der Stichprobenstatistik 102 bis 118; tatsächlich waren es 10 bis 52. Genauer war keines der beiden durchgehend, aber die Grundlage der Schätzung war eine andere. Nachdem genügend Zeilen geändert worden waren, wurde auch die Indexstatistik automatisch neu berechnet, diesmal aus einer Stichprobe. Danach schätzten beide fast gleich. Auch Abfragen, die einen Index nie verwenden, können ihn also über die Statistik spüren.

Leser und Schreiber auf verschiedenen Wegen

Ein Lookup-Plan liest zuerst den neuen Index und dann die Tabelle. Eine Änderung geht oft den umgekehrten Weg: erst die Tabelle, dann der Index. Laufen beide gleichzeitig, entsteht ein bekanntes Deadlockmuster. Das haben wir in diesem Lab nicht gemessen. Es ist ein weiterer Grund, nach einer Indexänderung auch auf Deadlocks zu achten. Ein abdeckender Index wie im nächsten Abschnitt nimmt dem Muster den Lookup und damit die Grundlage. Wann ein Blockieren nur wartet und wann es zum Deadlock wird, erklärt der Beitrag Blocking und Timeouts.

Wann ein Index Pläne stabilisiert

Der vierte Reiter oben zeigt die Gegenrichtung. Mit INCLUDE (Betrag) braucht B keinen Lookup mehr. Der Seek ist für kleine und große Kunden der beste Weg, und der Kipppunkt verschwindet. B lief in jeder Reihenfolge und auf jeder Version stabil und kostete etwa ein Viertel des Zustands ohne Index.

Daraus folgt nicht „immer alle Spalten einschließen“. Hier fehlte genau eine Spalte. Bei breiten Ausgaben wird der Index entsprechend breiter, und ein Seek ist auch mit INCLUDE nicht garantiert. Der Preis stand im Lab beim Schreiben:

Jede Änderung an Betrag muss jetzt auch den Index pflegen: dreimal so viele Logeinträge und ein Mehrfaches an Laufzeit. Auch beim Einfügen schreibt jeder der beiden Indizes zusätzliche Logeinträge je Zeile. Diese Kosten haben wir gesehen, aber nicht belastbar genug gemessen, um eine Zahl zu nennen. Ob sich das lohnt, hängt davon ab, wie oft Betrag geändert und wie oft B gelesen wird. Diese Abwägung gehört zu jeder Indexentscheidung. Ein Index ist dann gut, wenn er für den ganzen Workload besser ist, nicht für eine Abfrage.

Den Gewinn und die Nebenwirkungen im ganzen Workload messen

Für die Entscheidung brauchen Sie dieselben Zahlen wie im Lab, aber aus Ihrem System: alle Abfragen auf der Tabelle, vorher und nachher, gewichtet danach, wie oft sie laufen. Der Query Store liefert das, wenn er für die Datenbank eingeschaltet ist.

Suchen Sie die betroffenen Abfragen im Plan, nicht im Abfragetext. Greift eine Abfrage über eine View, eine Funktion oder ein Synonym auf die Tabelle zu, steht der Tabellenname nicht in ihrem Text, wohl aber in ihrem Plan. Abfrage 1 listet alle Abfragen im Query Store, deren Plan die Tabelle verwendet, und markiert Pläne, die seit der Indexänderung neu entstanden sind:

/* PSG · Indexoptimierung · Abfrage 1   Welche Abfragen verwenden diese Tabelle, und welche haben seit einer Indexänderung einen neuen Plan?   Nur lesend. Liest den Query Store der aktuellen Datenbank.   Sucht im Plan statt im Abfragetext, damit Zugriffe über Views, Prozeduren und Funktionen mitzählen.   Auf sehr großen Query Stores kann die Suche in den Plänen einige Zeit dauern.   Berechtigung: VIEW DATABASE STATE, ab SQL Server 2022 VIEW DATABASE PERFORMANCE STATE.   Zeiten in UTC. Mittelwerte enthalten auch abgebrochene Ausführungen (Timeouts).   Die Suche prüft Schema und Tabelle, nicht die Datenbank: gleichnamige Tabellen   anderer Datenbanken, die über dreiteilige Namen gelesen werden, zählen mit.   MAXDOP 1: Die Abfrage läuft seriell und belegt keine weiteren Kerne der Anwendung. */DECLARE @schema  sysname        = N'dbo',        @tabelle sysname        = N'Bestellungen',        @seit    datetimeoffset = DATEADD(DAY, -1, SYSDATETIMEOFFSET());  -- Indexänderung, z. B. '2026-11-05 14:00 +01:00' DECLARE @muster nvarchar(600) = N'Schema="' + QUOTENAME(@schema) + N'" Table="' + QUOTENAME(@tabelle) + N'"'; WITH plaene AS (    SELECT p.query_id, p.plan_id, p.initial_compile_start_time, p.last_execution_time, p.is_forced_plan    FROM sys.query_store_plan AS p    WHERE CHARINDEX(@muster, p.query_plan) > 0),laufzeit AS (    SELECT rs.plan_id,           SUM(rs.count_executions) AS ausfuehrungen,           SUM(rs.avg_duration * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS mittel_ms,           SUM(rs.avg_logical_io_reads * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) AS mittel_reads    FROM sys.query_store_runtime_stats AS rs    WHERE rs.plan_id IN (SELECT plan_id FROM plaene)    GROUP BY rs.plan_id)SELECT pl.query_id,       pl.plan_id,       CASE WHEN pl.initial_compile_start_time >= @seit THEN N'neuer Plan' ELSE N'' END AS seit_aenderung,       pl.is_forced_plan,       CAST(pl.initial_compile_start_time AS datetime2(0)) AS erster_plan,       CAST(pl.last_execution_time AS datetime2(0))        AS zuletzt,       l.ausfuehrungen,       CAST(l.mittel_ms AS decimal(12,2))    AS mittel_ms,       CAST(l.mittel_reads AS decimal(14,0)) AS mittel_reads,       OBJECT_NAME(q.object_id) AS objekt,   -- Prozedur oder Trigger; leer bei Ad-hoc, View und Inline-Funktion       LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), N' '), CHAR(10), N' '), 120) AS abfrageFROM plaene AS plJOIN sys.query_store_query      AS q  ON q.query_id = pl.query_idJOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_idLEFT JOIN laufzeit              AS l  ON l.plan_id = pl.plan_idORDER BY pl.query_id, pl.initial_compile_start_timeOPTION (MAXDOP 1);

Im Prüflauf haben wir dieselbe Tabelle direkt, über eine View, aus einer Prozedur und über eine Inline-Tabellenwertfunktion abgefragt, dann einen Index angelegt und alles wiederholt. So sah das Ergebnis auf SQL Server 2022 aus, gekürzt um einige Spalten:

query_id plan_id seit_aenderung ausfuehrungen mittel_ms mittel_reads objekt   abfrage
3        4                      5             15.37     2870         NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.Bestellungen WHERE KundeId = @k
3        11      neuer Plan     5             .12       62           NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.Bestellungen WHERE KundeId = @k
4        5                      5             14.64     2870         NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.v_Bestellungen WHERE KundeId = @k
4        12      neuer Plan     5             .14       62           NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.v_Bestellungen WHERE KundeId = @k
5        6                      5             15.80     2870         p_Umsatz (@k int)SELECT KundeId, SUM(Betrag) … GROUP BY KundeId
5        13      neuer Plan     5             .12       62           p_Umsatz (@k int)SELECT KundeId, SUM(Betrag) … GROUP BY KundeId
6        7                      5             14.61     2870         NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.f_Bestellungen(@k)
6        14      neuer Plan     5             .14       62           NULL     (@k int)SELECT SUM(Betrag) … FROM dbo.f_Bestellungen(@k)
9        10      neuer Plan     1             114.62    4684         NULL     CREATE INDEX IX_Kunde_Datum ON dbo.Bestellungen (KundeId, Bestelldatum)

Die Abfrage lief auf SQL Server 2017, 2019, 2022 und 2025 ohne Fehler und mit demselben Ergebnis. Eine Abfrage, die nur eine andere Tabelle liest, erschien nicht.

So lesen Sie das Ergebnis:

• Eine Abfrage mit „neuer Plan“ hat nach dem Zeitpunkt eine Planform bekommen, die es vorher nicht gab. Das kann die Indexänderung sein, aber auch eine Statistikaktualisierung oder eine ganz neue Abfrage. Vergleichen Sie Ausführungen, mittlere Dauer und Lesevorgänge ihres alten und neuen Plans.
• Mehrere Pläne für dieselbe Abfrage nach der Änderung sind ein Hinweis auf einen Kipppunkt wie bei Abfrage B. Prüfen Sie dann, für welche Parameterwerte welcher Plan entstanden ist.
• Ab SQL Server 2022 mit Kompatibilitätsgrad 160 können für eine Abfrage mehrere Planvarianten mit eigener Abfrage-ID erscheinen. Das ist die Parameter Sensitive Plan Optimization aus dem nächsten Abschnitt. Welche Varianten zusammengehören, zeigt sys.query_store_query_variant.
• Die Spalte objekt nennt die Prozedur oder den Trigger, aus dem eine Abfrage stammt. Views und Inline-Funktionen löst SQL Server in der aufrufenden Abfrage auf. Diese erscheint dann mit ihrem eigenen Text, im Beispiel mit dbo.v_Bestellungen und dbo.f_Bestellungen.
• Das CREATE INDEX selbst erscheint ebenfalls, denn sein Plan liest die Tabelle. Diese Zeile können Sie übergehen.
• Bleibt die Planform gleich, zeigt der Query Store keinen neuen Plan, obwohl die Abfrage neu kompiliert wurde. Das heißt nur, dass der erste Aufruf diesmal denselben Weg gewählt hat. Wie Abfrage B im Fall „1. Aufruf groß“ zeigt, kann der Kipppunkt trotzdem bestehen und bei der nächsten Neukompilierung kippen. Bei Abfragen mit ungleich verteilten Parametern lohnt deshalb ein Test mit kleinem und großem Wert.

Abfrage 1 sieht nur, was der Query Store erfasst hat. Im Standardmodus AUTO können seltene und sehr billige Abfragen fehlen, und ältere Daten werden nach der eingestellten Aufbewahrung gelöscht. Zeiten zeigt sie in UTC. Für einen sauberen Vergleich legen Sie den Zeitpunkt der Änderung fest, lassen ein repräsentatives Zeitfenster vorher und nachher laufen und vergleichen gleich lange Fenster. Ein Wochenabschluss, der nur freitags läuft, braucht ein Fenster, das einen Freitag enthält.

Den ganzen Workload im Blick: PSG QX

Abfrage 1 beantwortet die Frage für eine Tabelle und einen Zeitpunkt. Im echten Betrieb ändern sich selten nur ein Index und eine Tabelle. Mit demselben Deployment kommen neuer Code, aktualisierte Statistiken und manchmal eine neue Version. Die eigentlichen Fragen lauten dann: Braucht diese Tabelle den Index überhaupt? Und was hat sich seit der Änderung für welche Abfrage verändert, und worauf stützt sich die Aussage?

Für beide Fragen setzen wir in unseren Analysen PSG QX ein. QX wertet die vorhandene Ausführungshistorie im Query Store aus, lokal und ohne zusätzliche Aufzeichnung. QX legt selbst keine Indizes an; es belegt und grenzt ein, die Entscheidung treffen wir gemeinsam mit Ihnen.

Vor der Änderung: von der Tabelle aus denken. Das Zugriffsprofil einer Tabelle in QX dreht die übliche Blickrichtung um. Statt einer Abfrage und ihres Plans zeigt es alle Abfragen, die diese Tabelle verwenden, gruppiert nach ihrem Zugriffsmuster: Suche auf Gleichheit, Gleichheit plus Bereich, Zugriffe ganz ohne Suchbedingung. Jedes Muster ist nach der Rechenzeit der beteiligten Abfragen gewichtet und mit den Index- und Planzugriffen belegt, die tatsächlich im Plan stehen. Daneben stellt QX die Rollen der Spalten in Filtern, Verknüpfungen und Ausgabe den vorhandenen Indizes und dem gruppierten Schlüssel gegenüber und fasst überlappende Missing-Index-Hinweise zusammen. So sehen Sie, bevor Sie etwas anlegen, ob ein vorhandener Index die Muster schon bedient, sich erweitern lässt oder ob ein neuer Index wirklich eine Lücke schließt. Für das Beispiel dieses Beitrags ist das genau die Frage: A und B suchen beide über KundeId, B braucht zusätzlich Betrag. Ein Index, der nur A betrachtet, übersieht das.

Nach der Änderung: vorher und nachher vergleichen. QX vergleicht die Zeit vor und nach einer Änderung über den ganzen Workload: neue Abfragen, neue und verschlechterte Pläne, Abfragen, die häufiger laufen oder teurer werden. Eine Abfrage mit mehreren Plänen wie Abfrage B fällt dabei als solche auf, zusammen mit der Stelle im Quelltext der Prozedur, an der die Pläne entstanden sind. Jeder Vergleich wird eingestuft, als vergleichbar, eingeschränkt oder nicht möglich, jeweils mit Grund. So wird ein kurzes Zeitfenster nach dem Deployment nicht vorschnell zum Beleg, in die eine wie in die andere Richtung.

Mit jeder neuen Version das Indexdesign auf den Prüfstand

Ein Indexdesign ist auch zeitlich kein festes Objekt. Mit jeder neuen SQL-Server-Version und jedem höheren Kompatibilitätsgrad bewertet der Optimizer Ihre Indizes anders. Er schätzt anders, nutzt neue Ausführungsverfahren und wählt dadurch andere Pläne. Ein Index, der früher unverzichtbar war, kann danach nur noch Schreibarbeit kosten. Ein anderer wird plötzlich von Abfragen gewählt, für die er nie gedacht war. Diese beiden Fälle kennen wir aus der Praxis, im Lab haben wir sie nicht nachgestellt.

Im Lab zeigte sich etwas anderes, ebenso Wichtiges, an genau demselben Fall:

Ab Kompatibilitätsgrad 160 erkannte die Parameter Sensitive Plan Optimization die ungleiche Verteilung der Kunden und hielt für kleine und große Kunden je eine eigene Planvariante bereit. Der Rückschritt aus dem vorigen Abschnitt trat nicht mehr auf. Derselbe Index war auf Kompatibilitätsgrad 150 ein Risiko für Abfrage B und auf 160 eine Verbesserung.

Verlassen Sie sich trotzdem nicht darauf. Die Funktion greift nur für bestimmte Gleichheitsbedingungen mit ungleich verteilten Werten und nicht für jede Form einer Abfrage. In einem Vorlauf, in dem Abfrage B ihr Ergebnis über Ausgabeparameter zurückgab, bildete sie keine Varianten. Ob sie bei Ihren Abfragen greift, zeigt erst die Messung.

Zwei Dinge helfen bei der Planung:

• Der Auslöser ist meist der Kompatibilitätsgrad, nicht die installierte Version allein. Viele Änderungen am Optimizer hängen am Kompatibilitätsgrad der Datenbank. Eine Datenbank behält bei Upgrade oder Wiederherstellung ihren bisherigen Grad, eine neu angelegte erhält den Standard der Version, auf SQL Server 2022 also 160. Wer nach dem Upgrade beim alten Grad bleibt, verschiebt die Prüfung auf den Tag der Anhebung. Ein Teil der Optimizer-Korrekturen kommt zudem mit kumulativen Updates und ist an eigene Schalter gebunden. Microsoft empfiehlt, mit dem bisherigen Grad zu wechseln, im Query Store eine Ausgangsbasis zu sammeln und erst dann anzuheben (Query Store Usage Scenarios). Das ist auch der richtige Zeitpunkt für die Indexprüfung. In unseren Projekten übernimmt PSG QX den Vergleich vor und nach der Anhebung.
• „Auf den Prüfstand“ heißt nicht „neu entwerfen“. Gemeint ist eine geordnete Prüfung nach einem repräsentativen Zeitraum: Welche Abfragen haben andere Pläne, welche Indizes werden nicht mehr oder anders genutzt, welche neuen Missing-Index-Hinweise tauchen auf, und welche Indizes kosten nur noch Schreibarbeit? Geändert wird danach einzeln und mit Rückweg.

Was ein Versionswechsel sonst noch verändert, zeigt unsere Entscheidungshilfe zum Upgrade.

Einen Kandidaten auswählen und nachvollziehbar einführen

Am Ende steht eine Entscheidung, die Sie begründen können. Diese Tabelle fasst die Fragen zusammen:

Führen Sie eine Indexänderung einzeln ein, nicht zusammen mit anderen Änderungen. Sonst lässt sich eine Verschlechterung nicht zuordnen. Auch der Rückweg ist eine Indexänderung: Das Entfernen löst wieder Neukompilierungen aus. Einen bestehenden Index zu ersetzen ist eine eigene Änderung: Prüfen Sie vorher, ob er eine Eindeutigkeit sichert, ob Abfragen ihn per Hinweis namentlich verwenden und ob im Query Store erzwungene Pläne auf ihn angewiesen sind. Ein Hinweis auf einen entfernten Index führt zu einem Fehler; ein erzwungener Plan, der ihn braucht, lässt sich nicht mehr anwenden, und die Abfrage wird normal kompiliert.

Einen Plan im Query Store zu erzwingen kann eine Notbremse sein, wenn eine Abfrage nach der Änderung kippt. Es ersetzt nicht die Entscheidung, ob der Index bleibt.

Wie Indexauswahl und Indexwartung zusammenhängen

Dieser Beitrag behandelt die Frage, welcher Index sinnvoll ist. Wie ein Index danach gepflegt wird, mit Füllfaktor, Reorganisieren und Neuaufbau, ist eine eigene Aufgabe. Ein Neuaufbau baut die vorhandene Definition neu auf. Einen ungeeigneten Schlüssel macht er nicht passend. Umgekehrt braucht jeder neue Index eine Wartungsentscheidung, die zu seinem Änderungsmuster passt. Die Reihe Indexwartung verstehen erklärt diesen Teil.

Häufige Fragen

Soll ich jeden vorgeschlagenen Missing Index erstellen? Nein. Die Hinweise entstehen aus Schätzungen für einzelne Abfragen. Prüfen Sie vorhandene und überlappende Indizes, die Wirkung auf die übrigen Abfragen der Tabelle und die zusätzlichen Schreibkosten.

Warum ist nach einem neuen Index eine andere Abfrage langsamer? Weil der Index nicht nur der Abfrage gehört, für die er angelegt wurde. Abfragen auf der Tabelle werden bei ihrer nächsten Ausführung neu kompiliert und können ihn verwenden. Dabei kann ein Plan entstehen, der nur für einen Teil der Parameterwerte passt. Abfrage 1 zeigt, welche Abfragen betroffen sind.

Ist ein Index Seek immer schneller als ein Scan? Nein. Ein Seek mit 100.000 Lookups dauerte im Lab 2,4- bis 3,3-mal so lange wie der Scan der ganzen Tabelle. Entscheidend sind die gelesene Datenmenge und der vollständige Plan, nicht der Name des Operators.

Hilft ein Index Rebuild bei einem ungeeigneten Indexschlüssel? Nein. Ein Neuaufbau baut die vorhandene Definition neu auf. Eine andere Schlüsselreihenfolge oder andere eingeschlossene Spalten entstehen dadurch nicht.

Muss ich nach einem Upgrade alle Indizes neu entwerfen? Nein, aber prüfen. Nach der Anhebung des Kompatibilitätsgrads zeigt ein Vergleich im Query Store, welche Abfragen andere Pläne haben. Daraus folgt, welche Indizes Sie gezielt ändern, entfernen oder behalten.

Quellen

• Microsoft: SQL Server index architecture and design guide
• Microsoft: Create indexes with included columns
• Microsoft: Create filtered indexes
• Microsoft: Tune nonclustered indexes with missing index suggestions
• Microsoft: Parameter Sensitive Plan Optimization
• Microsoft: Query Store Usage Scenarios
• Microsoft: ALTER INDEX, Abschnitt zum Deaktivieren
• Microsoft: Change the database compatibility level and use the Query Store
• Microsoft: Query processing architecture guide, Abschnitt zur Neukompilierung von Plänen
• Microsoft: sys.query_store_plan
• Microsoft: sys.dm_db_index_usage_stats

Weiterlesen: Indexwartung verstehen · Mal schnell, mal langsam · SQL Server Performance Optimierung