Wozu Statistiken?

Es genügt schon, eine ganz simple Abfrage zu betrachten:

SELECT * FROM Kunden WHERE Familienname BETWEEN 'M' AND 'SZZZ'

Gibt es keinen Index, kann man hier wenig optimieren, aber angenommen, es gäbe einen (nonclustered) Index auf Familienname. Sollen wir ihn benutzen oder nicht? Benutzen wir ihn nicht, müssen wir sämtliche Daten der Tabelle lesen und filtern. Benutzen wir ihn, können wir nur den relevanten Teil selektieren, müssen aber wegen des "SELECT *" für jeden Datensatz einen teuren Key-Lookup durchführen. Sollten wir die gewählte Query dann parallel auf mehreren Prozessoren durchführen, oder ist ein einzelner Thread sinnvoller?

Es ist leicht ersichtlich, dass die Beantwortung dieser Fragen im Wesentlichen von der Anzahl der Datensätze in der Tabelle, ihrer durchschnittlichen Größe und von der Verteilung der Namen abhängt. Genau aus diesem Grund benötigt der Query-Optimizer Statistiken. Statistiken werden per Default für jeden Index angelegt, sowie für Spalten, die zwar in keinem Index, aber in WHERE-Bedingungen auftauchen.

Datenbank Optionen

Das automatische Anlegen solcher Statistiken wird über die Datenbankoption AUTO_CREATE_STATISTICS kontrolliert. Diese ist standardmäßig eingeschaltet und es gibt angesichts des geringen Speicherbedarfs von Statistiken und ihrer essentiellen Wichtigkeit für den Query-Analyzer normalerweise keinen Grund, diese Option auszuschalten.

Eine weitere per Default eingeschaltete Option ist AUTO_UPDATE_STATISTICS. Eine veraltete Statistik kann schlimmer sein als gar keine, deshalb sollten die Statistiken möglichst up-to-date sein. Mit der Auto-Update Option markiert der SQL Server eine Statistik als veraltet, sobald sich ca. 20% der Daten in der betroffenen Spalte geändert haben. Die Statistik wird jedoch erst neu berechnet, wenn sie benötigt wird, also bei der nächsten Abfrage auf diese Spalte.

Schließlich gibt es noch die Option AUTO_UPDATE_STATISTICS_ASYNC. Das im letzten Absatz beschriebene Neuberechnen der Statistiken kann eine Weile dauern und verzögert damit die Ausführung der Query. Besonders bei großen Tabellen kann das zu unerwünschten Response-Zeiten oder sogar zu Timeouts führen. Dieses Verhalten lässt sich übrigens schwer reproduzieren, denn bei der nächsten Query ist wieder alles wie zuvor. Hier hilft ggf. ein Blick auf den letzten Updatezeitpunkt der Statistik via select object_name(object_id), stats_date(object_id, stats_id), * from sys.stats. Setzt man die Async Option, blockiert die Query nicht. Allerdings arbeiten dann die Querys mit den veralteten Statistiken bis das Update fertig ist.

Das automatische Statistik Update lässt sich pro Tabelle/Index/Statistik über die jeweilige NORECOMPUTE Option abschalten, falls AUTO_UPDATE_STATISTICS für die Datenbank eingeschaltet ist. Umgekehrt kann man jedoch nicht AUTO_UPDATE_STATISTICS abschalten und für individuelle Objekte anstellen. Stattdessen muss man dann alle Statistiken selbst pflegen.

Einen schnellen Überblick über die gesetzten DB Optionen verschafft
select * from sys.databases

Update Strategien

Wann macht es Sinn, Statistiken "von Hand" zu pflegen? Zum Beispiel, wenn man sehr große Tabellen in der Datenbank hat. Ein Statistik-Update kann hier durchaus mehrere Minuten dauern, deshalb sollte man dies lieber präventiv durchführen, z.B. im Rahmen eines nächtlichen Maintenance Tasks.

Verschärft wird das Problem noch bei Tabellen, denen ständig neue Daten hinzugefügt werden. Schlimmstenfalls werden nämlich genau diese neuen Daten ständig abgefragt. Da sie aber noch gar nicht in der Statistik erfasst sind, liefert der Query Optimizer ständig nicht optimale Query Pläne, was bei großen Tabellen dramatische Performanceauswirkungen haben kann. Werden die Daten batchweise hinzugefügt, bietet es sich an, am Ende des Batches noch ein manuelles Statistik-Update anzuhängen.

Bei solchen selbst ausgeführten Updates stellt sich noch die Frage nach der Samplegröße. Man kann einen vollständigen Scan ausführen (FULLSCAN), oder nur eine bestimmte Anzahl zufällig ausgewählter Werte einbeziehen ("SAMPLE 10 PERCENT" bzw. "SAMPLE 10000 ROWS"). In letzterem Fall braucht man abhängig von der Anzahl verschiedener Werte in der Spalte ein wenig Mathematik, um zu der korrekten Samplegröße zu kommen. Als Lackmustest kann man sich für einige einfache Querys den Actual Execution Plan anschauen und die vorhergesagte Zahl der Rückgabewerte mit den tatsächlichen vergleichen. Zumindest die Größenordnung sollte hier stimmen.

Fazit

Die Statistiken in SQL Datenbanken haben einen erheblichen Einfluss auf die Performance, nicht nur bei SELECT, sondern auch bei UPDATE und DELETE. Die Default-Einstellungen des Servers sind im allgemeinen Fall ausreichend, stoßen aber speziell bei großen Datenbanken auch an ihre Grenzen. Die Statistiken sollten daher gut verstanden sein, beobachtet und intelligent gepflegt werden.

Quellen

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Statistics.aspx
http://msdn.microsoft.com/en-us/library/dd535534.aspx
http://www.sqlservercentral.com/blogs/steve_jones/2009/10/28/how-many-rows-have-changed/