SQL Server Performance – Index

Was ist ein Index?

• Ein Konstrukt mit dem Ziel die Lese-/Schreibvorgänge auf Datenbanktabellen zu reduzieren.
• Effektiv wird Speicherplatz genutzt um I/O-Operationen effizienter zu gestalten.

Welche Index-Typen gibt es?

Gruppierter Index / Clustered Index
Bei einem gruppierten Index werden Daten basierend auf einem Index-Schlüssel organisiert. Umgesetzt wird es als eine B-Tree Struktur die schnelle Abfrage spezifischer Zeilen basierend auf Schlüsselwerten ermöglicht. Es ist nur ein Clustered Index pro Tabelle erlaubt.

Nicht-Gruppierter Index / Nonclustered Index
Ein Nicht-Gruppierter Index kann auf einer Tabelle oder Sicht basierend auf einem gruppierten Index oder dem Heap erzeugt werden. Die Daten werden basierend auf einem Index-Schlüssel und einem sog. Locator organisiert. Mit Hilfe dieses Locators kann die Position einer bestimmten Spalte im Index direkt bestimmt werden.Index-Spalten sind basierend auf dem Index-Schlüssel sortiert, die Daten selbst (außer in Kombination mit einem gruppierten Index) sind nicht zwangsläufig sortiert.

Anforderungen, Auswirkung auf die Performance & Überlegungen

Warum werden Indizes benötigt?

• Beispiel: Eine Bibliothek mit einer sehr großen Menge an Büchern.

1. Man kann jedes Buch mit einer Nummer versehen und die Bücher entsprechend ihrer Nummer
2. Man kann Bücher mit gleichen/ähnlichen Thematiken im gleichen Regal unter bringen.

• Vorteil: Wir müssen nicht mehr die gesamte Bibliothek durchsuchen um ein bestimmtes Buch zu finden!

1. Wir orientieren uns an der zuvor vergebenen Nummer des Buches das wir suchen.
2. Wir orientieren uns an der Thematik des Buches um schnell das Regal zu finden in dem sich das Buch befindet

Einfluss auf den SQL Server
ABFRAGE-PERFORMANZ MIT/OHNE INDEX
Beispiel: Wir möchten Informationen aus der Tabelle [SalesOrderDetails] bezüglich der Bestellung 56958 abrufen.

Select *
FROM [Sales].[SalesOderDetails]
WHERE [SaleOrderID] = 56958

• Eine Tabelle ohne Index wird in SQL Server als eine Heap-Tabelle bezeichnet.
• Um die Abfrage zu verarbeiten, muss bei einer Heap-Tabelle jede einzelne Zeile mit der gewünschten [SalesOrderID] verglichen werden.
  1. Enthält die Ergebnis-Menge wie in diesem Fall nur 1-2 Zeilen, werden unnötig
     viele Zeilen überprüft (enormer Overhead!).
• Was passiert z.B. bei Tabellen mit vielen Millionen/Milliarden Zeilen?
  1. Die gesamte Tabelle zu Scannen ist SEHR uneffizient.
  2. Server Ressourcen (CPU, I/O, Speicher) werden verschwendet.
  3. Performance des gesamten Systems wird beeinflusst.
• Lösung: Index Verwenden.
  1. Definieren wir einen oder mehrere Indizes auf unsere Tabelle, verändert sich
     auch die Arbeitsweise des SLQ Server bezüglich der betrachteten Abfrage.
  2. Statt alle Zeilen einer Tabelle zu SCANNEN, kann SQL Server den Index nutzen um
     gezielt die Ergebnis-Menge zu bestimmen.

Messwerte des Experiments
Experiment und Ergebnisse basierend auf der AventureWorks-Datenbank.

Gute und schlechte Indizes

• Gute Indizes haben positiven Einfluss auf die Abfrage-Performanz und minimieren gleichzeitig die für den Index benötigten Ressourcen.
• Schlechte Indizes haben negativen Einfluss auf die Abfrage-Performanz in der Gesamtheit. Index Ressourcen werden verschwenderisch genutzt.
  • Beispiel: Index auf einer Spalte mit vielen Insert-/Update-Operationen.
  • Mit jedem Veränderung muss auch der Index angepasst werden.
    • Besteht die Workload des Systems hauptsächlich aus Schreibe-Operationen, können Indexe die Performanz in der Gesamtheit verschlechtern.
• Ungenutzte Indexe können sich ebenfalls negativ auf die Performanz auswirken!
  • Abfrage-Pläne werden basierend auf Statistiken konstruiert.
  • Ungenutzte Indexe können bei der Erfassung der Statistiken ebenfalls berücksichtigt werden und sich negativ auf der Ergebnis auswirken.

Was berücksichtigt ein guter Index?
Ein guter Index sollte die folgenden Aspekte berücksichtigen:

• Die Arbeitslast des SQL Server/der Tabelle
• Eine sinnvolle Auswahl der Index-Spalten
• Tabellen-Größe
• Auf- oder Absteigende Sortierung der Spalten-Informationen
• Spalten-Reihenfolge
• Index-Typ

Best-Practice Ansätze zu Indizes in SQL Server

OLTP Datenbank

• Viele Lese-/Schreib-Operationen.
• Viele Modifikationen existierender Daten.

Indexe sollten basierend auf den entsprechend ausgewählten Spalten einer Tabelle entworfen werden.Zu viele Indexe können negativen Einfluss auf Performanz und Systemressourcen haben.Lieber ein minimale Anzahl an Indizes um die Anforderungen zu erfüllen, als zu viele.

OLAP Datenbank

• Hauptsächlich Lese-Operationen um Daten für die Weiterverarbeitung zu identifizieren

Mehrere Indizes mit mehreren Schlüssel-Spalten pro Index sollten verwendet werden. Columnstore Indizes bieten sich besonders im Date-Warehouse Umfeld an.

Indexe auf den Anspruch der Arbeitslast (Workload) anpassen

• Arbeitslast analysieren
• SQL Abfragen (gespeicherte Prozeduren, Funktionen, Sichten und AD-HOC Abfragen) in die Analyse einbeziehen.
• SQL Profiler, Erweiterte Ereignisse und dynamische Verwaltungs-Sichten (dynamic management views) können bei der Analyse helfen.

Indexe für die meist-verwandtesten und teuersten Abfragen

• Arbeitslast gruppieren
• Gute Indizes für diese Abfragen entlastet das gesamte System

SQL Server Index-Schlüssel Empfehlungen berücksichtigen

• Text-Spalten von den Typen text, image, ntext, varchar(max), nvarchar(max) und varbinary(max) können nicht als Index-Schlüssel verwendet werden.
• Es wird empfohlen einen Integer-Datentyp als Index-Schlüssel zu verwenden. Die niedrigen Speicher-Anforderungen ermöglichen effiziente Verarbeitung.
• In einem XML-Index können ausschließlich XML Datentypen verwendet werden.
• Ein primärer Schlüssel sollte auf einer Spalte mit einzigartigen Werten definiert werden. Falls eine solche Spalte nicht existiert, kann eine Identity-Spatel Abhilfe leisten.
• Spalten die als “Unique” oder “Not Null” definier sind, eignen sich besonders gut als Kandidaten für einen Schlüssel.
• Indexe sollten basierend auf den Prädikaten einer WHERE-Klausel entworfen werden.
• Tabellen sollten so zusammengefügt werden, dass möglichst wenig Spalten für die Weiterverarbeitung berücksichtig werden müssen. Dies erleichtert dem Query-Optimizer die Berechnung eines effizienten Ausführungsplans.
• Werden mehrere Spalten für einen Index-Schlüssel verwendet, muss die Position der einzelnen Spalten innerhalb des Schlüssels mit bedacht gewählt werden.
• Inkludierte Spalten sind geeignete Kandidaten für einen Index.

Daten-Verteilung der SQL Server Index-Spalten analysieren

• Spalten mit redundanten Informationen können einen negativen Einfluss auf Datenabfrage haben und den Abfrageprozess verlangsamen.
• Histogramme der Statistik können bei der Analyse helfen.

Sortierreihenfolge der Daten beachten:

• Standardmäßig werden Daten in aufsteigender Reihenfolge in einen Index eingepflegt.
• Für abfragen mit expliziter Sortierreihenfolge (ORDER BY … DESC) gegensätzlich zur Sortierreihenfolge des Index entstehen kosten.

Fremd-Schlüssel für SLQ Server Indizes verwenden

• Ein Gruppierter Index auf dem Fremd-Schlüssel einer Tabelle ist sehr empfehlenswert.

SQL Server Index-Speicher beachten

• Indizes können auf unterschiedliche Datei-Gruppen verteilt werden.
• Performanz von I/O-Operationen und allgemeiner Durchsatz wird erhöht.
• Analog können Partitionierungen verwendet werden um Daten einer Tabelle auf unterschiedlichen Festplatten/Dateigruppen zu verteilen. Partitionierte Indizes können so die Performanz bezüglich gleichzeitiger Zugriffe verbessern.

Fehlende SQL Server Indizes erkennen

• Fehlende Indizes über den Abfrageplan/DMVs identifizieren.
• Empfohlene Indizes sollten nicht blind übernommen, sondern ausgiebig analysiert und bei bedarf angepasst werden.

Immer einen gruppierten vor einem nicht-gruppierten Index erstellen

• Als allgemeine Richtlinie gilt: ein gruppierter Index sollte immer vor einem nicht-gruppierten Index erstellt werden.
• Besitzt eine Tabelle keinen Index, besteht ein nicht-gruppierter Index ausschließlich aus Zeilen-Identifikatoren.

Index-Wartung und Update-Statistiken überwachen

• Folgende Aspekte sollten überwacht werden: