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

Index-Pflege: Ein Schlüssel zur Datenbankleistung

In meinem vorherigen Beitrag zum Thema Indexe haben wir bereits über ihre Bedeutung gesprochen. Diesmal tauchen wir tiefer ein, um zu verstehen, warum und wie Indexe gewartet werden sollten.

Index-Pflege ist ein entscheidender Faktor, um die Leistung eurer Datenbanken zu gewährleisten. Die Auswirkungen von fragmentierten Indexen können dramatisch sein. Wenn die Leistung eurer Abfragen nachlässt und eure Anwendungen nicht mehr reibungslos laufen, ist es an der Zeit, sich der Index-Pflege zu widmen.

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

Der Grad der Fragmentierung: Euer Leitfaden

Wie könnt ihr feststellen, wann Index-Pflege erforderlich ist? Der Grad der Fragmentierung ist ein entscheidender Indikator. Verwendet die Systemfunktion sys.dm_db_index_physical_stats, um die Fragmentierung zu analysieren. In Kombination mit sys.indexes erhaltet ihr eine klare Übersicht über die fragmentierten Indexe.
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

Ein Richtwert ist eine durchschnittliche Fragmentierung von über 30%. Indexe, die diesen Schwellenwert überschreiten, erfordern Aufmerksamkeit. Es ist wichtig zu beachten, dass nicht alle Indexe die gleiche Pflege benötigen. Ein differenzierter Ansatz ist notwendig, um Ressourcen effizient zu nutzen.

Index-Pflege-Methoden im Überblick

Es gibt zwei Hauptmethoden zur Index-Pflege: „Rebuild“ und „Reorganisation“.

Index „Rebuild“: Die umfassende Erneuerung

Beim „Rebuild“ wird der Index gelöscht und anschließend vollständig neu erstellt. Dadurch wird die Fragmentierung beseitigt, und zusätzlicher Speicherplatz wird freigegeben. Bedenkt jedoch, dass dies ressourcenintensiv ist. Die Rebuild-Operation erfordert, dass der alte Index gelöscht und dann der Index basierend auf den Daten in der Tabelle neu erstellt wird.

Hier ist ein Beispiel:

ALTER INDEX PK_DatabaseLog_DatabaseLogID ON DatabaseLog REBUILD ; 
GO

Index-Reorganisation (Reorg): Die Sortieroption

Die „Reorganisation“ eines Indexes erfordert minimale Ressourcen. Sie überprüft die Einträge im Index und ordnet sie bei Bedarf neu, um die optimale und logische Reihenfolge wiederherzustellen. Hierbei werden die Einträge nicht neu erstellt, sondern lediglich neu sortiert.

Hier ist ein Beispiel:

ALTER INDEX PK_DatabaseLog_DatabaseLogID ON DatabaseLog REORGANIZE ; 
GO

Best Practices und Entscheidungsfindung

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

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.

Es ist wichtig, sich an bewährte Praktiken zu halten, um die richtige Index-Pflege-Entscheidung zu treffen. Microsoft bietet Empfehlungen basierend auf dem Grad der Fragmentierung. Diese Empfehlungen dienen als Ausgangspunkt, aber ihr könnt sie an eure spezifischen Anforderungen anpassen.

Warum Expertenwissen entscheidend ist

Die Qualität einer Monitoring-Lösung hängt stark von den Experten ab, die sie verwalten. Monitoring-Systeme können nicht „selbstreparieren“. Die Regeln und Anweisungen, die sie befolgen, müssen von erfahrenen Experten definiert werden. Es ist entscheidend, die Regeln so anzupassen, dass sie den spezifischen Anforderungen eurer Umgebung entsprechen.

Automatisierung und Hilfe aus dem Netz

Ihr müsst das Rad nicht neu erfinden. Es gibt bereits umfassende Lösungen zur Index-Pflege im Internet. Ein herausragendes Beispiel ist das Skript zur Wartung von Ola Hallengren. Dieses Skript bietet eine breite Palette von Parametern, die auf eure spezifischen Anforderungen zugeschnitten werden können.

Hier ist ein Beispiel:

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

Analyse-Tools für tiefere Einblicke

Analyse-Skripte wie die von Jason Strate bieten detaillierte Einblicke in den Status eurer Indexe. Diese Tools ermöglichen eine umfassende Überwachung und bieten eine Fülle von Informationen zur perfekten Indexanalyse.

Mittels dieser Analyse Skripte erhält man 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

Die Wartung von Indexen ist ein wesentlicher Bestandteil der Datenbankverwaltung. Verlasst euch auf Fachwissen, Tools und bewährte Verfahren, um eure Datenbanken in Topform zu halten. Index-Pflege ist ein Schlüsselbegriff in der Datenbankadministration, und ihr solltet sicherstellen, dass ihr diesen Aspekt nicht vernachlässigt. Eure Datenbank und eure Benutzer werden es euch danken.