Dass Indizes im SQL Server ihre Daseinsberechtigung haben ist nichts wirklich Neues. Dass diese Indizes hin und wieder auch gepflegt werden müssen, mag vielleicht manche Leute überraschen, die sich durch die ansonstige Pflegeleichtigkeit des SQL Servers haben einlullen lassen. Denkt man jedoch in Ruhe darüber nach, wird man einsehen, dass dies notwendig ist und dass es Sinn macht, dass SQL Server diese Aufgabe nicht automatisch einfach blind nach Schema F erledigt.

Dazu sind die verschiedenen Umgebungen in denen SQL Server eingesetzt wird einfach zu unterschiedlich, um alle in punkto Index-Wartung über einen Kamm scheren zu können. Was in der einen Umgebung vielleicht signifikante Auswirkungen auf die Performance hat, mag in einer anderen Umgebung akzeptabel sein, da dort gänzlich andere Abfragen laufen, denen ein nicht optimal gewarteter Index nicht viel ausmacht.

Doch schon allein das Wort "optimal" im vorherigen Satz kann man kontrovers diskutieren. Was ist "optimal"? Microsoft gibt zum Beispiel die Empfehlung ab, bei einer Fragmentierung zwischen 5% und 30% den Index zu reorganisieren und ab 30% aufwärts, den Index neu zu erstellen (nachzulesen hier). So weit, so gut. Nur, wie bei jeder Empfehlung sollte man sie von Zeit zu Zeit in seiner eigenen Umgebung kritisch hinterfragen, ob sie immer noch Sinn macht oder angepasst werden muss. Erst recht gilt dies, wenn man liest, mit welchen wissenschaftlichen Methoden diese Werte ermittelt wurden: Where do the Books Online index fragmentation thresholds come from? :-)

Wir haben hier eine Tabelle, in die brutto jeden Tag zwischen 10 und 20 Millionen Zeilen eingefügt werden. Nächtens läuft eine Maintenance Prozedur, die mehrere Millionen Zeilen, die älter als x Tage sind wieder löscht. Der Nettozuwachs in der Tabelle liegt so zwischen 3 und 5 Millionen Zeilen pro Tag. Diese Tabelle enthält zur Zeit ca. 975 Millionen Zeilen.

Die Fragmentierung festzustellen ist seit SQL Server 2005 ziemlich einfach geworden durch die neu eingeführte dynamische Managementfunktion sys.dm_db_index_physical_stats. Hier ist ein Teil des Skriptes, mit dem wir irgendwann einmal anfangen haben:

SELECT
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[name],
    FRAG.avg_fragmentation_in_percent,
    FRAG.page_count
FROM
    sys.dm_db_index_physical_stats
    (
        DB_ID(),    --use the currently connected database
        0,          --Parameter for object_id.
        DEFAULT,    --Parameter for index_id.
        0,          --Parameter for partition_number.
        DEFAULT     --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
    JOIN
    sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
    --don't bother with heaps, if we have these anyway outside staging tables.
    FRAG.index_type_desc <> 'HEAP' AND
    (
    --Either consider only those indexes that need treatment
    (FRAG.page_count > @IPageCnt AND FRAG.avg_fragmentation_in_percent > @IMinFragmentation)
    OR
    --or do everything when it is MaintenanceDay
    @IsMaintenanceDay = 1
    )
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;

Die ermittelten Zeilen werden in eine temporäre Tabelle geschrieben und dann nacheinander abgearbeitet. Dabei wird nach jedem Durchlauf geschaut, ob innerhalb des täglichen Zeitfenster noch Zeit ist, eine weitere Operation anzustarten oder nicht.

In der WHERE Klausel filtern wir auf mehrere Kriterien:
@IPageCnt = Standardwert ist 128. Alles darunter ignorieren wir. Das Ergebnis rechtfertigt hier nicht den Aufwand.
@IMinFragmentation = 10%. Alles unter 10% wird ebenfalls ignoriert. Unter 10% konnten wir keinen signifikanten Einfluss auf die Gesamtperformance beobachten.
@IsMaintenanceDay = Einmal pro Woche haben wir ein Zeitfenster, in dem wir alle Indizes warten können. Zumindest haben wir das anfangs mal gedacht...

Abhängig von der ermittelten Fragmentierung haben wir dann entweder ein REORGANIZE oder ein REBUILD gemacht, so wie es die obige Empfehlung nahegelegt hat. Das hat solange funktioniert, bis wir dann mal eines Montag morgens festgestellen mussten, dass unser Index-Job nach 5 Stunden noch nicht fertig war, was den Backup Job, der dann gescheduled war nicht gerade erfreute.

Mehrere Anmerkungen dazu:
ALTER INDEX ... REORGANIZE ist single-threaded, d.h. egal wie viele Prozessoren die Maschine hat, diese Operation verwendet immer nur einen einzigen davon. Das macht das Warten von grossen Indizes über REORGANIZE ziemlich schnell zum Geduldsspiel.
ALTER INDEX...REBUILD kann Parallelismus verwenden, läuft also deutlich schneller verbraucht aber auch mehr Resourcen.
Nachzulesen in Konfigurieren von Parallelindexvorgänge

Okay, das kann man noch durch etwas Research selber herausbekommen und es erklärt natürlich auch zum gewissen Grad, dass man plötzlich zu Laufzeiten kommt, die in die Stunden gehen, wenn man die grössten Indizes aufgrund ihrer Fragmentierung "nur" reorganisiert.

Umso erstaunlicher war es jedoch herauszufinden, dass ein nicht gerade unerheblicher Anteil an dieser Laufzeit auf das Konto von sys.dm_db_index_physical_stats selbst geht, also auf das Konto der reinen Ermittlung der Fragmentierung. Uns war zwar bewusst, dass im LIMITED Modus alle Seiten oberhalb des Leaf Levels gescannt werden müssen, aber irgendwie hätten wir nicht erwartet, dass dies derart lange dauern würde. In unserem Fall benötigt die Funktion schon mal locker ~30 Minuten, um überhaupt ein Ergebnis zu liefern. Das ist fast die Hälfte unseres gesamten täglichen Wartungsfensters, die dafür draufgeht, die Fragmentierung einer einzigen grossen Tabelle zu ermitteln. Für die anderen wesentlich kleineren Tabellen beträgt die gesamte Laufzeit nur ca. 3 - 4 Minuten, was akzeptabel ist. Nur bevor jemand aufmerkt: Ja, wir verwenden den LIMITED Modus bereits...

Nachdem wir das erst einmal herausgefunden hatten, war der nächste naheliegende Schritt, zu überlegen, ob und wie man sys.dm_db_index_physical_stats "schneller" machen kann. Eine der Ueberlegungen die dabei aufkam, war folgende: Wir müssen die grosse Tabelle aus sys.dm_db_index_physical_stats irgendwie auszuschliessen!

Um das Ganze flexibel zu halten, wollten wir eine separate Tabelle verwenden, in der wir die object_id und die index_id zusammen mit einem datetime Wert der letzten Wartung speichern und dann über ein WHERE NOT EXISTS, die grosse Tabelle aus sys.dm_db_index_physical_stats auszuschliessen und separat zu handhaben. Aber auch das war überraschenderweise nicht wirklich von Erfolg gekrönt. Die Laufzeit wurde nicht kürzer wie erwartet. Auf der Suche nach einer Begründung bin ich auf diesen Blog Eintrag von Paul Randal gestossen: Inside sys.dm_db_index_physical_stats. Wie man dort nachlesen kann unterstützt diese DMF kein Predicate Pushdown, was in unserem Fall bedeute, dass unabhängig von unserer smarten WHERE NOT EXISTS Klausel, die Fragmentierung erst ermittelt wird und danach die WHERE Klausel angewendet wird. Eine noch eindeutigere Erklärung für dieses Verhalten findet sich hier:

Most dynamic management views support “predicate push-down,” where the only data processed is that which matches the predicate in the WHERE clause. However, sys.dm_db_index_physical_stats is a function, not a view, so it can’t do this. This means you have to manually filter and only ask the function to process those indexes you know have the potential to be fragmented and may require rebuilding or reorganizing.

Damit war dieses Konstrukt auch wieder hinfällig.

Dann hatten wir diese Idee:

SELECT
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[name],
    FRAG.avg_fragmentation_in_percent,
    FRAG.page_count
FROM
    sys.indexes SIX
    CROSS APPLY
    sys.dm_db_index_physical_stats
    (
        DB_ID(),        --use the currently connected database
        SIX.object_id,  --Parameter for object_id.
        SIX.index_id,   --Parameter for index_id.
        0,              --Parameter for partition_number.
        DEFAULT         --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
WHERE
    SIX.object_id <> OBJECT_ID('...grosse Tabelle...')
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;

Der Hintergedanke hier war, über die sys.indexes in der WHERE Klausel zu filtern und die grosse Tabelle damit auszuschliessen und gleichzeitig die DMF mit den Parametern object_id und index_id aus sys.indexes aufzurufen. Die Idee war leider nur bis zum ersten Test genial:

Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "sys.dm_db_index_physical_stats".
Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function "sys.dm_db_index_physical_stats".

Um diesen Fehler bei der Verwendung von CROSS APPLY kann man den hier vorgeschlagenen Workaround einsetzen: Indexes From Every Angle: Using CROSS APPLY with sys.dm_db_index_physical_stats. Nicht gelöst wird dadurch jedoch das Problem des Predicate Pushdowns.

Wir haben uns jetzt für einen anderen (Hybrid-) Ansatz entschieden, bei dem in einer Tabelle für alle Indizes der Datenbank object_id und index_id zusammen mit page_count und einem Zeitstempel der letzten Wartung gespeichert wird.

Für Indizes mit weniger als 1.000.000 Seiten wird die Funktion sys.dm_db_index_physical_stats mit der object_id und index_id aus der neuen Tabelle als Parameter aufgerufen und dann abhängig vom Grad der Fragmentierung der Index neu erstellt oder nicht. Sind wir noch nicht am Ende unseres Zeitfenster wird eine weitere Zeile aus unserer Tabelle abgerufen, die Fragmentierung ermittelt, und ggfs. neu erstellt, ansonsten endet die Prozedur.

Für Indizes mit mehr als 1.000.000 Seiten wird an jedem Maintenance Day genau ein Index neu erstellt und zwar der, dessen Pflege schon am längsten zurückliegt. Bei diesen Indizes stellen wir vorher nicht den Grad der Fragmentierung fest, aber z. Zt. beträgt der Zyklus 3 Wochen für einen Index, bis er wieder an der Reihe ist. Wir haben diese Indizes über mehrere Wochen beobachtet und können berechtigterweise davon ausgehen, dass sich "genügend" Fragmentierung innerhalb dieser 3 Wochen angesammelt hat, um die Neuerstellung zu rechtfertigen.

Wir haben uns auch dafür entschieden, Indizes stets neu zu erstellen statt sie zu reorganisieren. Das mag zwar manchmal etwas "zu viel" sein, aber wir haben uns wegen der Möglichkeit des Parallelismus dafür entschieden. Um uns aber dadurch keine neuen Probleme zu schaffen, ermitteln wir vorher die Anzahl der Prozessoren und verwenden nur die Hälfte davon im ALTER INDEX Statement. Damit sind immer noch genügend Resourcen für andere Prozesse und Operationen währen unserer Index Maintenance vorhanden.

Hier noch einige weiterführende Links zum Thema:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e53f959d-ddb0-44dc-981c-bcf8131dc8ed

http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12869/Default.aspx

Mich würde interessieren, wie andere ihre Indizes pflegen und würde mich über entsprechende Kommentare freuen.

• 18.02.2011: Link zum Workaround bei Verwendung von CROSS APPLY ergänzt.