Skip to content

Tipps & Tricks: Bedeutung und Wartung von Indexstatistiken

28. August 2015

„Traue keiner Statistik, die Du nicht selbst aktualisiert hast!“

Eigentlich jedem, der sich etwas intensiver mit SQL Server Datenbanken beschäftigt, ist die hohe Bedeutung von Indizes für performante Datenbankabfragen bewusst. Was aber oft und gerne vernachlässigt wird, sind die Indexstatistiken.
Diese speichern Informationen über die Werteverteilung der indizierten Spalten und müssen – genau wie die Indizes selbst – regelmäßig gewartet werden. Eine veraltete Indexstatistik kann nämlich leicht dazu führen, dass der dazu gehörende Index einfach nicht verwendet wird.

Ähnlich wie bei der Indexwartung, die entweder per Reorganize oder aber per Rebuild durchgeführt werden kann, stehen auch für Indexstatistiken verschiedene Varianten zur Wartung zur Verfügung. Die gründlichere aber auch zeitaufwändigste Variante ist die Statistikerstellung per FULLSCAN. Hier werden die gesamten Indexdaten eingelesen und daraufhin die Statistik aktualisiert. In den meisten Fällen reicht jedoch auch eine Statistikaktualisierung auf Basis von Stichproben (SAMPLED) aus. Hierbei wird nur ein Teil der Indexdaten gelesen und der Rest quasi hochgerechnet.

(Wenn ein Index mittels Rebuild neu aufgebaut wird, werden auch die dazu gehörenden Indextstatistiken quasi im Vorbeimarsch per Fullscan aktualisiert, da die Indexdaten ohnehin komplett gelesen werden müssen.)

Bei ungleichmäßiger Verteilung der Indexwerte kann eine stichprobenbasierte Indexstatistik jedoch auch dazu führen, dass der Index nicht optimal oder unter Umständen auch gar nicht genutzt wird. Denn wenn die gewählte Stichprobe nicht repräsentativ für die Mehrzahl der Daten ist, wird von einer falschen Verteilung der Daten ausgegangen.
Das ist auch einer der Gründe, warum die Datenbankoption „Statistiken automatisch aktualisieren“ nicht zu empfehlen ist. Hier werden nämlich – sobald bestimmte Grenzwerte bzgl. Alter der Statistik und Änderungen in der Tabelle überschritten sind – die Indexstatistik generell stichprobenbasiert aktualisiert. Stattdessen macht es mehr Sinn, auch Indexstatistiken gezielt mit Hilfe von SQL Server Agent Jobs zu aktualisieren, wodurch man diese Wartungsaufgaben auch in Zeiten mit geringer Systemlast (Nachts oder am Wochenende) verlagern kann. Nun kann man bei kleineren Datenbanken leicht eine Routine entwickeln, die anhand der Systemtabelle sys.objects die Statistiken jeder Tabelle mit der FULLSCAN-Option aktualisert. Spätestens bei etwas größeren Datenbanken kann dieses Vorgehen aber leicht das nächtliche Wartungsfenster sprengen.

Daher empfehle ich folgende Strategie für die Wartung von Indexstatistiken:

  1. Statistiken zu Indizes auf Identity-Spalten können täglich (genaue: jede Nacht) strichprobenbasiert erstellt werden, da Identity-Werte automatisch gleichmäßig verteilt sind (sofern nicht übermäßig viele Zeilen in einem Bereich der Tabelle gelöscht werden).
  2. Alle anderen Indexstatistiken sollten generell mit FULLSCAN neu erstellt werden.
  3. Um das Wartungsfenster unter der Woche möglichst klein zu halten, werden am Wochenende alle Indexstatistiken mit FULLSCAN neu erstellt.
  4. Unter der Woche werden nur die Statistiken zu Indizes, deren Daten sich stark geändert haben, täglich mit FULLSCAN neu aufgebaut. Als Ausgangswert für die jeweiligen Schwellwerte kann man sich hier an den Parametern orientieren, die Microsoft auch für die automatische Statistikaktualisierung nutzt:
    • mindestens 50.000 Zeilen in der Tabelle
    • mindestens 20% geänderte Zeilen
    • mindestens 500 geänderte Zeilen (ist aufgrund der anderen beiden Bedingungen ohnehin erfüllt)

Natürlich kann man sowohl die Schwellwerte als auch die Häufigkeit der Aktualisierungen individuell anpassen. Es handelt sich hier lediglich um Startwerte, die für viele Datenbanken einen sinnvollen Kompromiss zwischen Wartungsaufwand und Aktualität der Statistiken darstellen.

Um die Statistiken, deren Tabellen, Indizes und Änderungswerte zu ermitteln kann man folgende Abfrage verwenden:

SELECT
sch.name AS [Schemaname],
obj.name AS [Tablename],
s.name AS [Statisticname],
STATS_DATE(s.[object_id],s.[stats_id]) AS [StatisticUpdateDate],
sp.[rows],
sp.rows_sampled,
sp.modification_counter
FROM sys.stats s
INNER JOIN sys.objects obj ON s.object_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
LEFT JOIN sys.indexes i ON i.object_id = obj.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties (obj.object_id, s.stats_id) AS sp
WHERE
obj.type = 'U'
ORDER BY
s.name

Diese Abfrage durchläuft man dann mit einem SQL Cursor und führt pro Zeile die entsprechende UPDATE Statistics-Anweisung aus:

EXEC ('UPDATE STATISTICS [' + @Schemaname + N'].[' + @Tablename + N'] [' + @Statisticname + '] WITH FULLSCAN')

Von diesem Ablauf müssen nun nur noch anhand der oben beschriebenen Kriterien für die Fälle 1 bis 4 unterschiedliche Varianten erstellt werden, die in die jeweiligen SQL Server Agent Jobs kommen. Das einzige was hierbei nicht ganz offensichtlich ist, ist die Erkennung von Statistiken zu Indizes auf Identity-Spalten. Da sich die Namen von Statistiken aber an denen der dazu gehörenden Indizes orientieren, sind diese leicht von den anderen zu unterscheiden, sofern man sich an entsprechende Namenskonventionen gehalten hat (z.B. „PK“ als Präfix für Primärschlüssel, „IX“ für normale Indizes).

Schreibe einen Kommentar

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: