Teil 2: Zusätzliche Optionen und Wartung von Indizes

SQL Server Indizes – Effektivität von Indizes steigern
Kommentare

Im ersten Teil dieser Serie wurden die klassischen gruppierten und nicht gruppierten Indizes sowie abdeckende und gefilterte Indizes und indizierte Sichten behandelt. Die meisten dieser Indexformen bieten zahlreiche Optionen an, mit denen sich die Indizes noch effektiver nutzen lassen. Aber auch die Wartung von Indizes und Indexstatistiken darf nicht außer Acht gelassen werden.

Zwischen den Themen Indexoptionen und Wartung von Indizes besteht ein direkter Zusammenhang, da sich einige Optionen unmittelbar darauf auswirken, wie häufig eine Wartung notwendig wird.

Indexoptionen werden bereits bei der Erstellung von Indizes mit der Zusatzklausel WITH definiert, der dann in runden Klammern die Auflistung der eigentlichen Optionen folgt. Beginnen wir der Einfachheit halber mit einer Option, die unabhängig von der Indexwartung sinnvoll einsetzbar ist.

Artikelkasten
Teil 1: Index-Basics (klassische Indexvarianten)
Teil 2: Zusätzliche Optionen und Wartung von Indizes
Teil 3: Indexformen für spezielle Verwendungszwecke
Teil 4: Spaltenbasierte Indizes und Fazit

Indexkomprimierung

Seit SQL Server 2008 gibt es – zumindest ab der Enterprise-Edition – die Möglichkeit, Indizes zu komprimieren. Das hat unmittelbar zur Folge, dass mehr Daten in eine Speicherseite passen und sie damit auch weniger Platz auf dem Datenträger benötigen. Indirekt bewirkt dies aber auch, dass mehr Daten im Hauptspeicher (Buffer-Cache) zwischengespeichert werden können und somit weniger Plattenzugriffe notwendig sind.

Allerdings bringt die Komprimierung auch einen Nachteil mit sich. Das ständige Komprimieren und Dekomprimieren der Daten kostet zusätzliche Prozessorzeit. In der Praxis erweisen sich jedoch meist die Festplattenzugriffe als Flaschenhals, während die CPUs nur selten voll ausgelastet sind. Somit kann man die zusätzlich erforderliche Prozessorzeit getrost in Kauf nehmen, sofern man über eine Enterprise-Edition verfügt. Die Komprimierung von Indizes wird mit der Option WITH (DATA_COMPRESSION = ) definiert, wobei folgende Komprimierungsarten unterstützt werden:

  • NONE – keine Komprimierung (Default).
  • ROW – zeilenbasierte Komprimierung, bei der die Daten innerhalb einer Zeile komprimiert werden. Dabei werden Spalten mit Datentypen fester Länge intern mit variabler Länge gespeichert, um nicht genutzten Platz freizugeben. Bei alphanumerischen Datentypen variabler Länge (varchar/nvarchar) hat diese Art der Komprimierung keine Auswirkungen.
  • PAGE – seitenbasierte Komprimierung, die zusätzlich zur zeilenbasierten Komprimierung noch zwei weitere Kompressionsalgorithmen anwendet, die zeilenübergreifend komprimieren. So wird mit der Präfixkomprimierung pro Spalte ein Präfix ermittelt, zu dem dann pro Zeile lediglich noch die Unterschiede gespeichert werden müssen. Anschließend wird eine Wörterbuchkomprimierung durchgeführt, die spaltenübergreifend nach gemeinsamen Mustern sucht, um diese nicht mehrfach speichern zu müssen. Es liegt auf der Hand, dass diese Komprimierungsart effektiver ist, als die zeilenbasierte Variante, aber auch mehr Prozessorleistung erfordert.

Vereinfacht reicht es aus, sich zu merken, dass die Komprimierung innerhalb einer Zeile (einfach und schnell) oder zeilenübergreifend innerhalb einer Speicherseite (aufwändiger aber deutlich effektiver) erfolgen kann. Ab SQL Server 2008 R2 greift bei aktivierter Komprimierung (egal ob ROW oder PAGE) zusätzlich die Unicode-Komprimierung, die dafür sorgt, dass bei Unicode-Datentypen (nchar(n) und nvarchar(n)) nur dann zwei Byte pro Zeichen verwendet werden, wenn diese wirklich erforderlich sind.

Um einen Index mit Seitenkomprimierung zu erstellen, verwendet man beispielsweise die folgende Anweisung:

CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName
ON Person.Person (LastName, FirstName)
WITH (DATA_COMPRESSION = PAGE)

Wenn der Index allerdings bereits existiert, kann man diesen neu aufbauen und dabei die Komprimierungseinstellung anpassen:

ALTER INDEX IX_Person_LastName_FirstName
ON Person.Person
REBUILD WITH (DATA_COMPRESSION = PAGE)

Nach demselben Prinzip lässt sich eine bestehende Komprimierung auch wieder entfernen oder sogar eine Kompression für eine Heap-Tabelle erstellen:

ALTER TABLE dbo.HeapTable
REBUILD WITH (DATA_COMPRESSION = PAGE)

Wenn die Tabelle aber über einen gruppierten Index verfügt, reicht es aus, diesen Index zu komprimieren, damit automatisch die Tabellendaten selbst komprimiert sind. Neben Heaps und Indizes unterstützt SQL Server auch die Komprimierung von indizierten Sichten.

Die Sache mit dem Füllfaktor

Bevor wir uns mit der Wartung von Indizes befassen, möchte ich etwas weiter ausholen, um zu erläutern, was an den Indizes überhaupt zu warten ist. Wie im ersten Teil dieser Serie bereits erwähnt, sind Indizes – genau wie die Zeilendaten einer Tabelle selbst – in Speicherseiten zu je 8 KB auf dem Datenträger abgelegt. Wenn neue Daten in solche Speicherseite eingefügt werden (oder bestehende Daten durch „breitere“ Daten aktualisiert werden), funktioniert das so lange, wie genügend Platz auf der Seite vorhanden ist.

Ist die Speicherseite voll, wird diese in zwei Seiten aufgeteilt (Page Split). Dabei müssen aber auch die entsprechenden Verweise auf die Indexeinträge oder Zeilendaten aktualisiert werden, die in die neue Speicherseite verschoben werden. Es sind also zusätzliche Schreiboperationen nötig, was den Page Split relativ aufwändig macht. Dazu liegen diese beiden logisch aufeinanderfolgenden Seiten dann nicht mehr direkt nebeneinander (der Index ist fragmentiert), wodurch Abfragen, die einen Bereich von Daten lesen, der beide Seiten umfasst, ebenfalls langsamer werden.

Damit dies nicht zu häufig geschieht, kann man bereits bei der Anlage eines Index einen sogenannten Füllfaktor angeben, der in Prozent angibt, wie viel Speicher pro Seite initial zu belegen ist [1]. Der übrig bleibende Platz verbleibt als Reserve für später neu eingefügte Daten. Die Angabe des Füllfaktors erfolgt über die Option FILLFACTOR:

CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName
ON Person.Person (LastName, FirstName)
WITH (FILLFACTOR = 80)

Wird kein Füllfaktor angegeben, so wird automatisch ein Wert von 100 Prozent angenommen, den man auch explizit durch 100 (oder alternativ 0) angeben kann. Das bewirkt, dass die Speicherseiten so voll wie möglich geschrieben werden und die nächsten einzufügenden Daten sofort einen Page Split erfordern. Das ist also nur zu empfehlen, wenn in die Tabelle nur sehr selten neue Einträge erfolgen und diese stattdessen meist nur lesend genutzt wird. Dafür wird auf diese Weise der zur Verfügung stehende Platz optimal ausgenutzt.

Es ist jedoch zu beachten, dass die Angabe des Füllfaktors alleine sich nur auf die Blattebene des Indexbaumes auswirkt. Sollen auch die darüber liegenden Ebenen denselben Füllfaktor verwenden, so ist zusätzlich die Option PAD_INDEX zu setzen:

CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName
ON Person.Person (LastName, FirstName)
WITH (FILLFACTOR = 80, PAD_INDEX = ON)

Die Wahl des optimalen Füllfaktors hängt nun primär von zwei Faktoren ab: 1. Wie häufig wird die Tabelle geändert? 2. Wie wird der Index gewartet?

Während die Wartung weiter unten in diesem Text detaillierter beschrieben wird, lässt sich zur Häufigkeit der Änderungen sagen, dass der Füllfaktor bei häufigen Änderungen niedriger sein sollte als bei Tabellen, deren Inhalte selten geändert werden. Im Zweifelsfall sind Werte zwischen 70 Prozent und 90 Prozent eine gute Ausgangsbasis, die für die meisten Datenbanken gut geeignet ist. Durch entsprechendes Monitoring der Page Splits lässt sich später feststellen, ob der Wert zu hoch gewählt war. Im Zuge einer Indexwartung kann der Wert in diesem Fall neu gesetzt werden.

Schnell und überall: Datenzugriff mit Entity Framework Core 2.0

Dr. Holger Schwichtenberg (www.IT-Visions.de/5Minds IT-Solutions)

C# 7.0 – Neues im Detail

Christian Nagel (CN innovation)

Wartung von Indizes

Der explizit definierte Füllfaktor stellt nur einen Ausgangswert dar. Der reale Füllfaktor einer Speicherseite ändert sich durch Änderungen an den Daten laufend. Bei Einfügeoperationen wächst er solange, bis ein Page Split nötig wird, der automatisch einen niedrigeren Füllfaktor für die beiden neu geteilten Seiten zur Folge hat. Um den ursprünglich definierten Füllfaktor für alle Seiten eines Index wieder herzustellen, ist eine Reorganisation notwendig [1].

ALTER INDEX IX_Person_LastName_FirstName
ON Person.Person 
REORGANIZE

Eine solche Reorganisation aktualisiert jedoch nur die Indexseiten auf Blattebene des Indexbaums. Je nach Füllgrad der einzelnen Speicherseiten hat das eventuell weitere Page Splits zur Folge. Häufige Page Splits führen allerdings dazu, dass die physikalische Reihenfolge der Indexseiten nicht mehr mit der logischen übereinstimmt (der Index wird fragmentiert), was den Index weniger effektiv werden lässt und bei starker Fragmentierung sogar dazu führen kann, dass SQL Server den Index nicht mehr verwendet. Um diesem Zustand abzuhelfen, ist es erforderlich, den fragmentierten Index neu aufzubauen, wobei der Füllfaktor aller Indexebenen wieder hergestellt wird.

ALTER INDEX IX_Person_LastName_FirstName
ON Person.Person 
REBUILD

Ein solcher REBUILD ist jedoch deutlich aufwändiger als ein REORGANIZE, weshalb man die Entscheidung, wann welche Variante sinnvoller ist, in der Regel vom aktuellen Fragmentierungsgrad abhängig macht, der im SQL Server Management Studio über die Eigenschaften des Index abrufbar ist.

Abb. 1: Die Anzeige des Fragmentierungsgrads in den Indexeigenschaften

Abb. 1: Die Anzeige des Fragmentierungsgrads in den Indexeigenschaften

Alternativ lässt sich der Fragmentierungsgrad auch über eine Abfrage auf die dynamische Verwaltungssicht sys.dm_db_index_physical_stats herausfinden (Listing 1). Sofern dabei Indizes aufgelistet werden, die komprimiert sind, ist in der Spalte compressed_page_count auch zu sehen, wie viele der Speicherseiten insgesamt (page_count) bereits in komprimierter Form vorliegen.

Listing 1
-- Fragmentierung von Indizes
SELECT sch.name DBSchema, obj.name Tabelle, idx.name [Index],
  ips.avg_fragmentation_in_percent, ips.index_type_desc ,
  ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),
    NULL, NULL, NULL , 'SAMPLED') ips
  INNER JOIN sys.objects obj ON ips.object_id = obj.object_id
  INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
  INNER JOIN sys.indexes idx ON obj.object_id = idx.object_id
    AND ips.index_id = idx.index_id
ORDER BY ips.avg_fragmentation_in_percent desc

Eine gängige Empfehlung besteht darin, den Index bei einem Fragmentierungsgrad zwischen 5 Prozent und 30 Prozent zu reorganisieren und ab einem Fragmentierungsgrad von 30 Prozent neu aufzubauen. Das ist jedoch nur eine allgemeine Empfehlung, die man je nach Häufigkeit der Datenänderungen und Indexwartungen individuell anpassen kann.

Bei einem REBUILD eines Indexes kann auch ein neuer Wert für den Füllfaktor definiert werden, indem man direkt nach dem Schlüsselwort REBUILD eine entsprechende WITH-Klausel platziert:

ALTER INDEX IX_Person_LastName_FirstName
ON Person.Person 
REBUILD WITH (FILLFACTOR = 90)

Lässt man die Option dagegen weg, so wird der zuletzt für diesen Index definierte Füllfaktor wiederverwendet.

Indexstatistiken und deren Wartung

Zu jedem neu erzeugten Index legt SQL Server automatisch auch eine Statistik an, die denselben Namen wie der dazu gehörende Index hat. Diese Indexstatistiken bieten der Datenbank-Engine wertvolle Zusatzinformationen darüber, wie die Daten im Index verteilt sind und geben dadurch eine Hilfe, wann und wie dieser Index am besten zu nutzen ist.

Dies funktioniert allerdings nur, solange die Indexstatistiken aktuell genug sind. Sind diese veraltet, hat das zur Folge, dass der zugehörige Index unabhängig vom Fragmentierungsgrad nicht mehr verwendet wird, da die Datenbank-Engine der Statistik dann nicht mehr traut. Um Indexstatistiken aktuell zu halten, gibt es verschiedene Wege [1]:

  • Wenn ein Index per REBUILD neu aufgebaut wird, wird auch die dazu gehörende Statistik aktualisiert, da ohnehin die gesamten Indexdaten eingelesen werden müssen. Das bedeutet im Umkehrschluss, dass bei regelmäßiger Neuerstellung der Indizes auch automatisch die Statistiken gewartet werden. Allerdings ist es bei den meisten Datenbanken zu aufwändig, regelmäßig alle Indizes mit REBUILD neu zu erstellen.
  • Über die Datenbankoptionen kann man einstellen, dass Statistiken automatisch erstellt und (wahlweise synchron oder asynchron) aktualisiert werden. Dabei bedeutet synchron, dass eine veraltete Statistik bei Bedarf neu erstellt und erst anschließend mit der entsprechenden Abfrageausführung fortgefahren wird. In der Einstellung asynchron wird der Ausführungsplan dagegen noch aufgrund der alten Statistik erstellt, und die Statistik unabhängig davon aktualisiert, damit sie für die nächste Abfrage zur Verfügung steht. (Als veraltet wird eine Statistik angesehen, wenn sich seit der letzten Aktualisierung mehr als 20 Prozent der Daten geändert haben.) Während die automatische Erstellung von Statistiken sicherlich sinnvoll ist, muss bei der automatischen Aktualisierung von Statistiken beachtet werden, dass diese lediglich stichprobenbasiert aktualisiert werden. Ist die Verteilung der Werte nicht gleichmäßig, kann dadurch im Extremfall eine irreführende Statistik entstehen, die für einen ungünstigeren Ausführungsplan einer Abfrage sorgt als der, der mit einer älteren Statistik auf Basis von kompletten Daten erstellt worden wäre.
    Wenn Indexstatistiken explizit per T-SQL-Code aktualisiert werden, lässt sich über einen Parameter steuern, ob diese stichprobenbasiert (SAMPLED) oder unter Berücksichtigung aller Daten (FULLSCAN) aufgebaut werden. Die Anweisung dafür lautet UPDATE STATISTICS Person.Person IX_Person_LastName_FirstName WITH FULLSCAN. Um für die Wartung der Statistiken nicht zu viel Zeit zu benötigen, gilt es herauszufinden, welche Statistiken eine Aktualisierung per FULLSCAN erfordern und wann eine stichprobenbasierte Statistik ausreicht.
Abb. 2: Die Datenbankoptionen zum automatischen Erstellen und Aktualisieren von Statistiken

Abb. 2: Die Datenbankoptionen zum automatischen Erstellen und Aktualisieren von Statistiken

Wartungsjobs

Wie ein Auto müssen auch Indizes und Statistiken regelmäßig gewartet werden. Allerdings gibt es hier keine vom Hersteller vorgeschriebenen Wartungsintervalle, sodass ein wesentliches Problem bei der Wartung von Indizes und Statistiken darin liegt, herauszufinden, was wann gewartet werden muss. Während bei den Indizes der Fragmentierungsgrad das wesentliche Kriterium ist, spielt für die Aktualisierung der Statistiken die Anzahl der seit der letzten Aktualisierung geänderten Zeilen eine entscheidende Rolle. Beides lässt sich mit geschickten Abfragen aus den Systemtabellen auslesen.

Wenn man sich für die Entwicklung eines eigenen Wartungsskripts entscheidet, ist zu beachten, dass zumindest eine stichprobenbasierte Aktualisierung von Indexstatistiken vor eventuellen Index-Rebuilds erfolgen muss, damit die hierbei automatisch per FULLSCAN aktualisierten Statistiken nicht durch ungenauere stichprobenbasierte überschrieben werden.

Index-und-Statistik-Wartungsskript von Ola Hallengren

Wenn Sie das Rad nicht neu erfinden wollen, finden Sie im Internet zahlreiche Varianten von entsprechenden Wartungsskripts. Die meisten erfahrenen Datenbankadministratoren haben im Laufe der Zeit ihre eigenen Varianten entwickelt. Der wahrscheinlich bekannteste Vertreter darunter stammt vom Schweden Ola Hallengren, der neben einigen anderen Admin-Skripten auch ein Skript zur Wartung von Indizes und Statistiken kostenlos auf seiner Website zur Verfügung stellt. Dabei handelt es sich um eine Stored-Procedure mit Name dbo.IndexOptimize, mit der über Parameter die Schwellwerte für mittlere und hohe Fragmentierung angegeben werden können und definiert werden kann, welche Wartungsmaßnahmen für niedrige, mittlere und hohe Fragmentierung durchzuführen sind.

Unabhängig davon, ob Sie ein eigenes Wartungsskript entwickeln oder eine fertige Routine (wie beispielsweise die von Ola Hallengren) nutzen, benötigen Sie einen SQL-Server-Agent-Job, der die regelmäßige Wartung startet. Zumindest bei Datenbanken, die nicht täglich 24 Stunden verfügbar sein müssen, besteht eine gängige Variante darin, täglich (in einem nächtlichen Wartungsfenster) eine „kleine Wartung“ durchzuführen, die sich auf die am stärksten fragmentierten Indizes konzentriert (und je nach Größe des verfügbaren Wartungsfensters eventuell lediglich Indizes reorganisiert und vielleicht einige Statistiken stichprobenbasiert aktualisiert). Dies wird dann durch eine „große Wartung“ ergänzt, die an jedem Wochenende läuft und aufwändigere Wartungsarbeiten (Index Rebuilds, Aktualisierung von Statistiken mit FULLSCAN etc.) durchführt.

Fazit

Mit Indexoptionen, die beispielsweise eine Indexkomprimierung bewirken oder den Füllfaktor setzen, lässt sich die Effektivität von Indizes noch erheblich steigern. Natürlich konnten hier bei Weitem nicht alle verfügbaren Optionen behandelt werden. Eine vollständige Auflistung ist online im MSDN zu finden.

Noch wichtiger als die Wahl der richtigen Optionen ist allerdings die regelmäßige Wartung von Indizes und zugehörigen Statistiken. Ähnlich wie beim Füllfaktor gibt es allerdings keine festen Vorgaben, die für alle Fälle optimal sind, sondern lediglich grobe Empfehlungen, die man als Ausgangswert nutzen kann, um dann durch Anpassung dieser Werte und Beobachtung der Auswirkungen davon nach und nach die optimalen Einstellungen herauszufinden.

Nachdem nun die wichtigsten Indexoptionen sowie die Wartung von Indizes geklärt sind, geht es in der nächsten Folge dieser Artikelserie um Indexformen für spezielle Verwendungszwecke wie Volltextindizes, XML-Indizes und räumliche Indizes.

Links & Literatur

[1] Panther, Robert: „SQL Server Performance Ratgeber“, entwickler.press 2010, ISBN: 978-3-86802-030-4

Windows Developer

Windows DeveloperDieser Artikel ist im Windows Developer erschienen. Windows Developer informiert umfassend und herstellerneutral über neue Trends und Möglichkeiten der Software- und Systementwicklung rund um Microsoft-Technologien.

Natürlich können Sie den Windows Developer über den entwickler.kiosk auch digital im Browser oder auf Ihren Android- und iOS-Devices lesen. In unserem Shop ist der Windows Developer ferner im Abonnement oder als Einzelheft erhältlich.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -