Index-Pflege – Wie? Wann? Wie häufig?

Wie in meinen Beitrag zum Thema Indexe bereits erwähnt, möchte ich dieses Thema weiter ausführen.

Indexe müssen in regelmäßigen Abständen eine gewisse Pflege erfahren, aber wann mache ich das? Woran erkenne ich die Notwendigkeit? Solle ich einfach alle Indexe meiner Datenbank jeden Tag “rebuilden” oder reicht ein “Reorg”?

Fragen über Fragen, ich versuche ein wenig Licht ins Dunkel zu bringen.

Je nach dem wie stark die Indexe einer Tabelle fragmentiert sind, kann sich dadurch die Abfrage-Performance auf eine oder mehrere Tabelle sehr verschlechtern. Wenn man Anhaltspunkte gefunden hat, dass die Abfrage-Performance eines oder mehrerer Statements nachlässt, so kann man sich über einige DMVs Zugang zu Analyse-Informationen beschaffen.
Zum Beispiel kann man die Systemfunktion sys.dm_db_index_physical_stats verwenden, um die Indexfragmentierung zu ermitteln. In Verbindung mit sys.indexes kann man eine einfache Übersicht erstellen
Quelle

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Hier kann man anhand der Adventureworks2014 erkennen wie hoch die Fragmentierung in jedem einzelnen Index ist

Resultset Index Fragementation

Nun gibt es auch entsprechend Best-Practise Empfehlungen ab wann man welchen Arbeitsschritt durchführen soll, dazu zeige ich erstmal welche Möglichkeiten es zur Index Pflege gibt.

Index Rebuild:
Hierbei wird der Index gelöscht und komplett neu aufgebaut, so wird die Ordnung, die Fragmentierung wieder bereinigt, da alle Einträge eindeutig passend und hintereinander neu aufgebaut werden. Dadurch wird die Fragmentierung entfernt und freier Speicherplatz geschaffen, welcher im Anschluss wieder freigegeben werden kann. Diese Operation ist relativ kostenintensiv, da erst der alte Index gelöscht werden muss, dann der Index anhand der Daten aus der Tabelle komplett neu erstellt werden muss.

ALTER INDEX PK_DatabaseLog_DatabaseLogID ON DatabaseLog REBUILD ; 
GO

Index Reorganisieren (Reorg):
Das Neuorganisieren eines Indexes beansprucht minimale Systemressourcen, hierbei werden die Einträge überprüft, ob sie noch an der richtigen Position liegen und werden dann ggfs umsortiert, um eine optimale und logisch richtige Reihenfolge wiederherzustellen.

ALTER INDEX PK_DatabaseLog_DatabaseLogID ON DatabaseLog REORGANIZE ; 
GO

Aber zuerst muss man entscheiden, welche Aktion man ausführen sollte… hierzu dient der Grad der Fragmentierung.
Microsoft empfiehlt zum Beispiel in der Knowledge Base

Microsoft Best Practise Indexes

Wenn man sich nun obiges Ergebnis der Abfrage anschaut und die Best-Practises von Microsoft, dann erkennt man, dass wir den Index ‘PK_DatabaseLog_DatabaseLogID’ online neu aufbauen sollten, hierzu verwenden wir also das obere Statement.

Aber vielleicht ist es gar nicht so einfach eine Entscheidung zu treffen (in diesem Beispiel schon, aber in der Realität sieht es vielleicht anders aus), daher könnte es sinnvoll sein für einzelne Indexe mehr Informationen zu ermitteln.

Mit DBCC SHOWCONTIG hat man eine Vielzahl von Optionen zur weiteren Analyse eines oder mehrer Indexes erhält man

DBCC SHOWCONTIG ('HumanResources.Employee');
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1237579447); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 715.1
- Avg. Page Density (full).....................: 91.16%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nun macht das regelmäßige manuelle Überprüfen der Index-Fragmentierung aller Indexe einer Datenbank keinen Sinn, der Aufwand wäre einfach zu groß. Nun kann sich jeder anhand der obigen Beispiele seine eigenen Jobs entsprechend der eigenen Anforderungen und Bedürfnissen zur Index-Pflege erstellen. Aber warum das Rad neu erfinden ;-), es gibt doch zahlreiche großartige Lösungen im Netz.

Zum Beispiel bietet Ola Hallengren eine phantastische Lösung (nicht nur) zur Indexpflege.

In diesem Wartungsskript gibt es eine Vielzahl von Parametern, die die Indexpflege verbessern und/oder weiter auf die vorhandenen Situation anpassen lassen. So gibt es zum Beispiel die Möglichkeit alle Indexe der aktiven User Datenbanken entsprechend des angegebenen Fragmentierungsgrades entweder neu zu erstellen bzw zu reorganisieren, dabei finden die Sort-Operationen in der tempDB statt und das Skript soll alle verfügbaren CPUs zu nutzen.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@MaxDOP = 0

Ebenso kann man sich mit den Skripten von Jason Strate einen Überblick über den Health-Status “seiner” Indexe beschaffen. Auf seiner Index-Ressourcen-Seite findet man zahlreiche Informationen, aktuelle Skripte und Präsentationen rund um Indexe.

Mittels dieser Analyse Skripte erhält eine detaillierte Übersicht mit sage und schreibe 58 Spalten voll mit Informationen für eine perfekte Analyse eines jeden Indexes.

EXEC dbo.sp_IndexAnalysis @Output = 'DETAILED'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Keine Datenbank braucht Indexe – nur die Applikationen

ich weiß, der Spruch ist geklaut aber er passt einfach so gut…
Man kann von Indexen halten was man will, aber man kommt halt nicht um sie herum.

Markus Winand schreibt in seinem Blog eine einfache aber geniale Einleitung zum Thema Indexe:
„Ein Index macht die Abfrage schnell“ ist die einfachste Beschreibung, die ich jemals für einen Datenbank-Index gehört habe. Obwohl sie seine wichtigste Eigenschaft gut erfasst, …

Wenn man sich eine einfache Tabelle ansieht, dann enthält diese von Haus aus keine Indexe.

TSQL - Create Table

Der Ausführungsplan für ein einfaches SELECT sieht dann so aus:

ExecutionPlan SimpleTable

Wie man erkennen kann, wird ein Full Table Scan durchgeführt. Das funktioniert zwar technisch und mag bei einfachen Abfragen oder kleinen Tabellen auch besser sein, aber für größere Tabellen und komplexere Abfragen fehlt hier mindestens ein Index. Also fügen wir der Tabelle zumindest einen Index hinzu, in diesem Fall einen Primary Key.

Create Table mit Primary Key

Daraus wird dann der folgende Ausführungsplan und man erkennt direkt den Unterschied zu vorher. Die “Kosten” fallen zwar immer noch zu 100% auf den Index, aber auch nur weil dieser Testfall zu einfach ist.

ExecutionPlan SimpleTable mit PK

Am besten kann sich das obige Bespiel mit dem alten Telefonbuch verdeutlichen:

Früher (bevor es intelligente Telefone/Handys gab) hatte jeder einen Notizbuch, in dem er die wichtigsten Daten seines Freundeskreis oder seiner Bekannten notierte.

Manfred Müller
 Bürgermeister-Glück-Straße 25
 1245 Beispielhausen 3
 10.08.1955
 04158 / 523 487 956

Mit der Zeit füllte sich dieses Notizbuch,  die Einträge völlig unsortiert, man schrieb einfach hinter einander weg. Irgendwann (nach mehrfachen Suchen) wusste man selber wer wo auf welcher Seite steht und fand so schneller zum Beispiel eine Telefonnummer.

Um beim obigen Beispiel zu bleiben, bei einem Full Tablescan müssen erst alle Seiten einer Tabelle gelesen werden, bis ein Ergebnis geliefert werden kann. Verfügt die Tabelle allerdings über einen Index, so werden die zu lesenden Seiten anhand des Indexes ermittelt und die Abfrage liefert schneller ein Ergebnis zurück, da nun nicht mehr alle Seiten (Pages) gelesen werden müssen.

Nun steigt mit der Zeit die Anzahl der Einträgen und das Führen eine unsortieren Notizbuches wurde immer unübersichtlicher, so dass man sich dann ein optimiertes Notizbuch mit Index (Inhaltsverzeichnis) kaufte… aaahhh ein Index.

Hier konnte man seinen Bekanntenkreis nach Anfangsbuchstaben sortieren, zum Beispiel mit dem ersten Buchstaben des Nachnamens oder auch des Vornamens (je nach Vorlieben oder was man sich besser merken konnte 😉  )

Genauso funktioniert unsere Tabelle im SQL-Server oben auch, ohne (hier Clustered) Index waren es einfach nur unsortierte Einträge, nach dem wir den Primary Key (einen Index) auf die Tabelle gelegt haben, hatten wir sozusagen ein Inhaltsverzeichnis erstellt und das Suchen im Adressbuch ging wesentlich einfacher und schneller. Ebenso verhält sich ein SQL Server mit seinen Indexen, ein Index ist sozusagen eine Extra-Seite, welche die Namen und einen Verweis auf die Seite beinhaltet, so dass nur noch einzelne Seiten beim Suchen nach dem Namen durchsucht werden müssen.
Denn in einer Tabelle werden die Einträge hintereinander weg speichert, es gibt keine Sortierung. Heißt mit einem FullTable stellt der SQL Server sicher, dass auch tatsächlich alle relevanten Einträge gefunden werden, dazu muss er erst alle Seiten lesen und durchsuchen. Bei einem Index Scan wird nur das Inhaltsverzeichnis durchsucht und die dort vermerkten Seiten werden gelesen, was wesentlich schneller geht als ein FullTable Scan.

Hierzu sehen wir uns erstmal die zwei Haupt-Index-Typen an, welche Typen gibt es bzw welche Index-Typen werden am häufigsten verwendet:

  • Clustered Indexes
    • Tabellen ohne Clustered Index werden als HEAP bezeichnet
    • Zeilen werden in einer logischen Reihenfolge gespeichert
    • nur ein Clustered-Index pro Tabelle
    • alle Daten werden in den Leafs gespeichert
  • Non-Clustered Indexes
    • Tabelle als Heap oder Clustered Index strukturiert
      Heap = Index ID 0
      Clustered-Index = Index ID 1
    • Weitere Indizes können erstellt werden, diese werden als Non clustered Indexes (Index ID 2+) bezeichnet
    • Indexe sind einzelne Objekte in der Tabelle
    • Auf Leaf Ebene ist nur einen Zeiger vorhanden, der die Information enthält auf wo der Rest der Spalten zu finden ist.
Darstellung Index Page Root Leaf
Vielen Dank an Microsoft (MVA)
Christian Bolton | Technical Director, Coeo
Graeme Malcolm | Microsoft

Notizbuch und Tabelle haben eine weitere Gemeinsamkeit… es kommen Einträge hinzu, Einträge werden gelöscht oder verändert, also die typischen Insert, Delete oder Update Statements.

Was passiert in solchen Fällen aber mit dem Index?
Der muss doch eigentlich auch angepasst werden?

Ich versuche mal einen Einstieg in dieses Thema anhand des Clustered Indexes:

  • Insert
    Bei einem Insert-Statement muss jede neue Zeile an ihrer logisch richtigen Position untergebracht werden, dabei kann es zu einem Page-Split der Tabelle kommen (da andere Zeilen “verschoben” werden müssen).
  • Update
    Wenn am ClusterKey (also der Spalte des Keys) der veränderten Spalte nichts geändert wird, also die Zeile an der selben Position bleibt, bleibt auch die Position innerhalb der Page identisch.
    Wenn sich die Größe der Zeile durch das Update ändert und die Zeile nicht mehr ganz in die Page passt, kommt es zu einem Page-Split der Tabelle.
    Wenn doch der Wert des ClusterKey verändert wird, so muss die Zeile entfernt werden und an einer neuen Position in einer anderen Page wieder gespeichert werden. Dadurch kommt es zu Leerräumen innerhalb der Pages.
  • Delete
    Ähnlich wie das Neu-Positionieren von Zeilen entstehen auch durch Delete-Statements Leerraum in den jeweiligen Pages.

Nun werden in Datenbanken und den Tabellen täglich eine Vielzahl von Daten neu geschrieben, Daten verändert oder gelöscht, so dass innerhalb der Indexe auch eine gewisse Unordnung entsteht. Diese “Unordnung” – fachlich auch als Fragmentation bezeichnet – sorgt mit zunehmender Zeit (Häufigkeit der Veränderungen und Datenwachstum) für eine nicht optimale Verteilung der Daten innerhalb der Pages, was zu einer Reduzierung der Abfrage-Performance führt und im schlimmsten Fall die Performance der ganze Applikation beeinträchtigen kann.

Daher empfehle eine regelmäßige Pflege aller Indexe!

Dazu mehr in einem weiteren SQL Server Blogpost zum Thema Indexpflege…

Indexe sind außerordentlich wichtig für Applikationen, damit komplexe Abfragen oder auch einfache Queries auf große Datenmengen einen verbesserten Ausführungsplan zeigen.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.