Index Optimierer

Der Index Optimierer analysiert die tatsächliche Nutzung der Datenbankindizes und unterstützt dabei, fehlende Indizes zu erkennen sowie dauerhaft nicht verwendete Indizes zu identifizieren. Ziel ist es, langsame Datenbankzugriffe frühzeitig zu erkennen und die Abfrageleistung dauerhaft zu verbessern.

Der Optimierer kombiniert dazu aktuelle SQL-Server-DMV-Daten, über einen längeren Zeitraum gesammelte Nutzungsstatistiken und – sofern aktiviert – Informationen aus dem SQL Server Query Store.

Der Index Optimierer ist erreichbar über:

Hauptmenü > Systemeinstellungen > Datenbank Index Optimierer

Standardmäßig aktiviert Automatik standardmäßig aktiviert

Übersicht

Nach dem Öffnen wird eine Tabelle mit den aktuell ermittelten Empfehlungen angezeigt.

Die Empfehlungen werden nach Typ gruppiert. Derzeit werden insbesondere folgende Empfehlungstypen verwendet:

CREATE Ein neuer Nonclustered Index wird empfohlen.
DROP Ein vorhandener Index wird als Löschkandidat erkannt.

Zu jeder Empfehlung werden unter anderem folgende Informationen angezeigt:

Spalte	Bedeutung
Datenbank	Datenbank, in der die Optimierung vorgenommen werden soll.
Tabelle	Betroffene Tabelle.
Index Name	Vorgeschlagener beziehungsweise vorhandener Indexname.
Schlüsselspalten	Spalten, aus denen der Schlüssel des Indexes besteht.
Enthaltene Spalten	Zusätzliche `INCLUDE`-Spalten des Indexes.
Confidence	Bewertung der Zuverlässigkeit der Empfehlung in Prozent.
Empfehlung Aktiv	Zeigt an, ob die Empfehlung bei der letzten Bewertung weiterhin gültig war.
Empfehlung aufgehoben UTC	Zeitpunkt, zu dem eine zuvor vorhandene Empfehlung ungültig geworden ist.

Unterhalb einer Empfehlung wird eine ausführliche Begründung angezeigt. Sie enthält abhängig vom Empfehlungstyp beispielsweise:

Beobachtungsdauer
Anzahl der beobachteten Anforderungen
kumulative aktuelle DMV-Anforderungen
Zeitpunkt der letzten DMV-Anforderung
Anzahl aktiver Beobachtungsintervalle
durchschnittlichen SQL-Server-Impact
Improvement Score
Query-Store-Ausführungen
Confidence für die manuelle Empfehlung
Confidence für eine automatische Ausführung

Bedienung

Index Optimierer öffnen

Öffnen Sie den Index Optimierer über Hauptmenü > Systemeinstellungen > Datenbank Index Optimierer.

Beim Öffnen wird eine aktuelle Bewertung der bereits gesammelten Indexstatistiken durchgeführt und anschließend die Empfehlungsliste angezeigt.
Aktuelle Daten neu analysieren

Mit Nach neuen Empfehlungen suchen wird eine vollständige Aktualisierung angestoßen.

Dabei werden zunächst die aktuellen Index- und Missing-Index-Statistiken des SQL Servers gesammelt. Anschließend werden die Daten automatisch neu bewertet.

Dieser Vorgang wird als Hintergrundjob ausgeführt. Besonders bei einer laufenden Query-Store-Nachverarbeitung kann die Analyse einige Zeit benötigen.
Empfehlung prüfen

Prüfen Sie die vorgeschlagene Datenbank, Tabelle, Schlüsselspalten, enthaltenen Spalten und die angezeigte Confidence.

Die Detailzeile unterhalb der Empfehlung erläutert, aufgrund welcher Messwerte die Empfehlung erzeugt wurde.
Empfehlung auswählen

Aktivieren Sie die Checkbox vor einer Empfehlung.

Nur weiterhin aktive Empfehlungen können zur Ausführung ausgewählt werden. Eine inzwischen aufgehobene Empfehlung kann nicht mehr angewendet werden.
Optimierung anwenden

Klicken Sie auf Ausgewählte Optimierungen anwenden.

Vor der eigentlichen Änderung wird die Empfehlung nochmals geprüft. Eine Optimierung wird nicht blind anhand des zuvor angezeigten SQL-Befehls ausgeführt.
Ansicht aktualisieren

Mit Ansicht aktualisieren werden die aktuell in der Datenbank gespeicherten Empfehlungen erneut in die Tabelle geladen.

Schaltflächen

Ansicht aktualisieren

Ansicht aktualisieren lädt die bereits gespeicherten Empfehlungen neu in die Tabelle.

Dabei wird keine neue Datenbankanalyse gestartet.

Nach neuen Empfehlungen suchen

Nach neuen Empfehlungen suchen startet eine neue Datensammlung und anschließend eine neue Bewertung.

Der Ablauf ist:

aktuelle Indexstatistiken sammeln
aktuelle Missing-Index-Daten sammeln
gegebenenfalls Query Store auswerten
Empfehlungen neu bewerten
Empfehlungstabelle aktualisieren

Diese Funktion ist insbesondere sinnvoll, wenn gerade ein langsamer Datenbankzugriff festgestellt wurde und kurzfristig geprüft werden soll, ob SQL Server einen fehlenden Index erkennt.

Ausgewählte Optimierungen anwenden

Mit Ausgewählte Optimierungen anwenden werden die über die Checkboxen markierten Empfehlungen ausgeführt.

Manuell ausgewählte Empfehlungen dürfen auch dann angewendet werden, wenn sie die Schwelle für eine vollautomatische Ausführung noch nicht erreicht haben.

Vor jeder Änderung werden die Empfehlung und der aktuelle Datenbankzustand nochmals geprüft.

Einstellungen

Über Einstellungen werden die Parameter des Index Optimierers geöffnet.

Die Grundeinstellungen sind bereits für einen sicheren automatischen Betrieb vorkonfiguriert.

Hilfe

Hilfe öffnet diese Dokumentation.

Confidence und automatische Freigabe

Bei CREATE-Empfehlungen wird bewusst zwischen einer frühen manuellen Empfehlung und einer automatischen Freigabe unterschieden.

Eine Empfehlung kann deshalb bereits sichtbar sein, obwohl sie noch nicht automatisch angewendet werden darf.

Beispiel:

Confidence:                    73 %
Automatik-Confidence:          59 %
Empfehlung aktiv:              Ja
Automatische Ausführung:       Nein

Dadurch kann ein Administrator bei einem akuten Performanceproblem einen offensichtlich sinnvollen Index bereits früh prüfen und manuell anlegen, ohne auf den vollständigen Beobachtungszeitraum warten zu müssen.

Die automatische Ausführung verwendet dagegen ausschließlich ausreichend lange beobachtete Daten und strengere Sicherheitsgrenzen.

Frühe manuelle CREATE-Empfehlung

Aktuelle Missing-Index-DMV-Werte dürfen die Confidence einer manuellen Empfehlung erhöhen, wenn der letzte Zugriff noch ausreichend aktuell ist.

Dadurch kann ein stark belasteter fehlender Index bereits kurz nach seinem Auftreten sichtbar werden.

Die aktuellen DMV-Zähler sind kumulativ und werden deshalb nicht direkt zur automatischen Freigabe verwendet.

Automatische CREATE-Freigabe

Für die automatische Freigabe müssen unter anderem folgende Bedingungen erfüllt sein:

Mindestbeobachtungszeit erreicht
Mindestanzahl tatsächlich beobachteter Anforderungen erreicht
Mindestanzahl aktiver Beobachtungsintervalle erreicht
Mindest-Impact erreicht
Mindest-Improvement-Score erreicht
erforderliche Automatik-Confidence erreicht
automatische Anwendung aktiviert
automatische CREATE-Ausführung aktiviert

CREATE-Empfehlungen

Eine CREATE-Empfehlung schlägt einen neuen Nonclustered Index vor.

Beispiel:

CREATE NONCLUSTERED INDEX [IX_WWS_AUTO_...]
ON [dbo].[Tabelle]
(
    [Spalte1],
    [Spalte2]
)
INCLUDE
(
    [Spalte3],
    [Spalte4]
);

Vor der Ausführung wird geprüft, ob:

die Zieldatenbank weiterhin gültig ist,
die Tabelle noch vorhanden ist,
der Indexname noch nicht existiert,
inzwischen kein anderer vorhandener Index die Anforderung bereits vollständig abdeckt,
die Tabelle nicht von der Optimierung ausgeschlossen wurde.

Das in der Empfehlung gespeicherte SQL wird dabei nicht blind ausgeführt. Der CREATE-Befehl wird unmittelbar vor der Ausführung erneut aus den geprüften Daten aufgebaut.

DROP-Empfehlungen

Eine DROP-Empfehlung weist auf einen vorhandenen Index hin, der über den konfigurierten Beobachtungszeitraum keine ausreichende Nutzung gezeigt hat.

DROP-Empfehlungen werden deutlich konservativer bewertet als CREATE-Empfehlungen.

Ein Index wird unter anderem nicht automatisch entfernt, wenn er:

ein Primary-Key-Index ist,
Bestandteil eines UNIQUE Constraints ist,
ein UNIQUE Index ist,
kein normaler Nonclustered Index ist,
ein gefilterter Index ist,
inzwischen wieder verwendet wurde,
seit der letzten Messung geänderte Nutzungszähler besitzt,
nach einem SQL-Server-Neustart noch nicht erneut ausreichend geprüft wurde.

Nicht vom Index Optimierer verwaltete Indizes können als DROP-Empfehlung angezeigt werden, werden jedoch niemals automatisch entfernt.

Sicherheitsprüfung direkt vor der Ausführung

Auch eine aktive Empfehlung wird unmittelbar vor einer Änderung nochmals validiert.

Bei einer manuellen oder automatischen Ausführung erfolgt unter anderem folgende Prüfung:

Empfehlung existiert weiterhin.
Empfehlung ist weiterhin aktiv.
Zieldatenbank gehört zur WWS-Datenbank oder zur zugehörigen FileIndex-Datenbank.
Schema und Tabelle sind nicht ausgeschlossen.
Aktueller Zustand des Indexes wird direkt aus SQL Server gelesen.
CREATE- oder DROP-spezifische Sicherheitsregeln werden geprüft.
Erst danach wird die Änderung ausgeführt.

Bei DROP-Empfehlungen wird zusätzlich geprüft, ob der Index zwischen der letzten Datensammlung und der tatsächlichen Ausführung verwendet wurde. Ist dies der Fall, wird der DROP verworfen.

Hintergrundbetrieb

Der Index Optimierer arbeitet zusätzlich zur manuellen Bedienung automatisch im Hintergrund.

In der Standardkonfiguration werden:

die Index- und Missing-Index-Statistiken regelmäßig gesammelt,
die gespeicherten Statistiken täglich neu bewertet,
automatisch freigegebene Empfehlungen automatisch angewendet,
pro automatischem Lauf maximal eine Indexänderung ausgeführt.

Die automatische Ausführung ist in den Grundeinstellungen aktiviert.

Einstellungen

Die folgenden Einstellungen stehen über Einstellungen zur Verfügung.

Allgemein

Einstellung	Standard	Beschreibung
Aktiviert	`Ja`	Aktiviert Datensammlung, Bewertung und Indexoptimierung.

Datensammlung

Einstellung	Standard	Beschreibung
Query Store auswerten	`Ja`	Bezieht Ausführungspläne des SQL Server Query Store ein.
Query Store Intervall in Stunden	`24`	Mindestabstand zwischen Query-Store-Auswertungen, sobald kein historischer Rückstand mehr besteht.
Query Store Batchgröße in Tagen	`1`	Maximale historische Query-Store-Zeitspanne, die pro Sammellauf verarbeitet wird.
Statistik-Aufbewahrungstage	`365`	Aufbewahrungszeit der aggregierten Statistikdaten.
Empfehlungs-Aufbewahrungstage	`365`	Aufbewahrungszeit erledigter beziehungsweise inaktiver Empfehlungen.

Neue Indizes

Einstellung	Standard	Beschreibung
Mindestbeobachtungstage für Automatik	`7`	Mindestanzahl von Tagen für eine automatische CREATE-Freigabe.
Mindestanforderungen für Automatik	`100`	Mindestanzahl tatsächlich beobachteter Missing-Index-Anforderungen.
Mindestbeobachtungen für Automatik	`3`	Mindestanzahl unabhängiger aktiver Beobachtungsintervalle.
Mindest-Impact (%) für Automatik	`50`	Minimaler durchschnittlicher Missing-Index-Impact.
Mindest-Improvement-Score für Automatik	`100000`	Minimaler kumulierter Improvement Score.
Maximale Schlüsselspalten	`5`	Maximale Anzahl von Schlüsselspalten eines vorgeschlagenen Indexes.
Maximale INCLUDE-Spalten	`10`	Maximale Anzahl zusätzlicher INCLUDE-Spalten.
LOB als INCLUDE erlauben	`Nein`	Erlaubt große Datentypen als INCLUDE-Spalten.
Mindest-Confidence (%) für Automatik	`97`	Mindestbewertung für eine automatische CREATE-Ausführung.
Mindest-Confidence (%) für Empfehlung	`50`	Mindestbewertung für eine sichtbare manuell prüfbare CREATE-Empfehlung.
Maximales Alter aktueller DMV-Nutzung	`24 Stunden`	Aktuelle DMV-Daten dürfen eine frühe Empfehlung nur innerhalb dieses Zeitfensters verstärken.

Alte Indizes

Einstellung	Standard	Beschreibung
Mindestbeobachtungstage	`90`	Mindestzeitraum für die Bewertung eines vorhandenen Indexes als DROP-Kandidat.
Maximale Lesezugriffe	`0`	Maximal erlaubte Reads im Beobachtungszeitraum.
Mindest-Updates	`10000`	Mindestanzahl von Schreibvorgängen auf einem ansonsten ungenutzten Index.
Mindest-Tabellenzugriffe	`10000`	Mindestaktivität der betroffenen Tabelle.
Query-Store-Rückblick in Tagen	`180`	Historischer Zeitraum für die Prüfung einer früheren Indexnutzung.
Mindest-Confidence (%)	`98`	Mindestbewertung für eine DROP-Empfehlung.

Automatik

Einstellung	Standard	Beschreibung
Empfehlungen automatisch anwenden	`Ja`	Hauptschalter für automatische Indexänderungen.
Maximale Änderungen pro Lauf	`1`	Begrenzung der automatisch ausgeführten Änderungen pro Lauf.
Indizes automatisch erstellen	`Ja`	Erlaubt automatisch freigegebene CREATE-Empfehlungen.
Verwaltete Indizes automatisch entfernen	`Ja`	Erlaubt das automatische Entfernen sicher bewerteter, verwalteter Indizes.
Bestehende Indizes als DROP empfehlen	`Ja`	Zeigt auch DROP-Empfehlungen für manuell oder anderweitig angelegte Indizes an.
Automatisch verwaltete Indexmuster	siehe unten	Legt fest, welche Indexnamen als vom Optimierer verwaltet gelten.
Ausgeschlossene Schemas	`HangFire`	Schemas, die von Empfehlungen und Änderungen ausgeschlossen sind.
Ausgeschlossene Tabellen	`_History`, `tbl_IndexOptimizer_`	Tabellenmuster, die von Empfehlungen und Änderungen ausgeschlossen sind.

Standardmäßig gelten folgende Indexnamen als automatisch verwaltet:

IX_WWS_AUTO_*
*dta_index*
NonClusteredIndex-*

Damit können vom WWS Index Optimierer selbst erzeugte Indizes sowie bekannte automatisch erzeugte Altindizes bei ausreichender Sicherheit automatisch entfernt werden.

Query Store

Wenn Query Store auswerten aktiviert ist, verwendet der Optimierer historische Ausführungspläne als zusätzliche Evidenz.

Dies ist insbesondere bei DROP-Empfehlungen wichtig. Ein Index, der im aktuellen DMV-Zeitraum nicht verwendet wurde, kann in älteren Query-Store-Plänen trotzdem noch als tatsächlich verwendeter Zugriffsindex vorkommen.

Ein solcher historischer Treffer schützt den Index vor einer DROP-Empfehlung.

Beim ersten Einsatz kann die Query-Store-Historie schrittweise aufgearbeitet werden. Die Einstellung Query Store Batchgröße in Tagen begrenzt den pro Lauf verarbeiteten Zeitraum und verhindert dadurch sehr lange Datenbankabfragen.

Bedeutung der Bewertungswerte

Beobachtete Anforderungen

Anzahl der Missing-Index-Anforderungen, die der Index Optimierer über seine eigenen Sammelläufe als tatsächliche neue Nutzung erfasst hat.

Diese Daten werden für die automatische Freigabe verwendet.

Kumulative DMV-Anforderungen

Aktueller kumulativer Zähler des SQL Servers für eine Missing-Index-Empfehlung.

Dieser Wert kann bereits vor dem ersten Sammellauf des Index Optimierers vorhanden gewesen sein. Deshalb wird er nur zur frühen manuellen Bewertung verwendet.

Aktive Intervalle

Anzahl der Sammelintervalle, in denen tatsächlich neue Missing-Index-Anforderungen aufgetreten sind.

Viele aktive Intervalle sind ein stärkeres Signal als eine einmalige hohe Belastung.

Impact

Von SQL Server geschätzter prozentualer Nutzen des fehlenden Indexes.

Ein hoher Impact allein bedeutet noch nicht automatisch, dass ein Index sinnvoll ist. Eine selten ausgeführte Abfrage kann beispielsweise einen sehr hohen Impact besitzen, ohne insgesamt relevante Laufzeit zu verursachen.

Improvement Score

Gewichtete Bewertung aus Häufigkeit, geschätzten Abfragekosten und Impact.

Der Improvement Score hilft dabei, häufige oder besonders teure Abfragen gegenüber seltenen Einzelfällen stärker zu gewichten.

Query Store Ausführungen

Anzahl historischer Planausführungen, in denen die jeweilige Index- oder Missing-Index-Situation gefunden wurde.

Confidence

Gesamtbewertung der Empfehlung.

Bei CREATE-Empfehlungen wird zusätzlich zwischen der sichtbaren Confidence und der strengeren Automatik-Confidence unterschieden.

Empfehlungen werden automatisch aufgehoben

Eine Empfehlung ist kein statischer Eintrag.

Bei jeder Bewertung werden die Bedingungen erneut geprüft. Ist eine Empfehlung nicht mehr erforderlich, wird sie als inaktiv markiert.

Typische Gründe sind:

ein passender Index wurde inzwischen erstellt,
ein anderer Index deckt die Anforderung bereits ab,
SQL Server meldet den Missing Index nicht mehr,
ein zuvor ungenutzter Index wird wieder verwendet,
die Sicherheitsbedingungen für einen DROP sind nicht mehr erfüllt.

Das Feld Empfehlung Aktiv zeigt deshalb immer an, ob die Empfehlung bei der letzten Bewertung noch gültig war.

Empfohlene Vorgehensweise bei einem akuten Performanceproblem

Öffnen Sie Hauptmenü > Systemeinstellungen > Datenbank Index Optimierer.
Klicken Sie auf Nach neuen Empfehlungen suchen.
Warten Sie, bis Datensammlung und Bewertung abgeschlossen sind.
Prüfen Sie insbesondere neue CREATE-Empfehlungen mit hoher Confidence und hoher aktueller DMV-Aktivität.
Lesen Sie die Detailbegründung der Empfehlung.
Wählen Sie eine plausible Empfehlung über die Checkbox aus.
Klicken Sie auf Ausgewählte Optimierungen anwenden.
Prüfen Sie anschließend, ob sich die betroffene Funktion beziehungsweise Abfrage deutlich beschleunigt hat.

Eine manuelle Optimierung sollte insbesondere bei sehr frühen Empfehlungen weiterhin fachlich geprüft werden. Die automatische Ausführung verwendet bewusst strengere Grenzwerte und eine längere Beobachtungszeit.

Hinweise

CREATE-Empfehlungen können bereits früh sichtbar werden, bevor sie automatisch freigegeben sind.
DROP-Empfehlungen werden deutlich konservativer behandelt.
Primary Keys, UNIQUE Constraints und UNIQUE Indizes werden nicht automatisch entfernt.
Nicht verwaltete vorhandene Indizes werden niemals automatisch gelöscht.
Eine Empfehlung wird unmittelbar vor der tatsächlichen Änderung nochmals gegen den aktuellen Datenbankzustand geprüft.
Pro automatischem Lauf wird standardmäßig höchstens eine Änderung ausgeführt.
Ausgeschlossene Schemas und Tabellen werden weder empfohlen noch automatisch verändert.