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
Björn arbeitet in Hamburg als Senior Consultant – Microsoft Data Platform und Cloud bei Kramer&Crew. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und DataGrillen teil und 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.