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

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

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