INF: Funktionsweise der automatischen Statistikaktualisierung von SQL Server 7.0

Die neu eingeführte Funktionalität für die Wartung statistischer Werte, die AutoStats-Prozedur, erzeugt möglicherweise nicht erwünschten Zusatzaufwand auf einem Produktionssystem, indem eine der folgenden Aktionen ausgeführt wird:

• Initiieren statistischer Aktualisierungen in Zeiträumen mit hoher Produktion.
  
  - Oder -
• Initiieren einer äußerst hohen Anzahl von UPDATE STATISTICS-Prozessen zu einem beliebigen Zeitpunkt.

In diesem Artikel sollen die Bedingungen detailliert dargestellt werden, unter denen das Erzeugen automatischer Statistikaktualisierungen und das Ausführen von UPDATE STATISTICS-Prozessen in Tabellen einer Datenbank zu erwarten ist.

Allgemeine Informationen

SQL Server verwendet einen kostenbasierten Optimierer, der möglicherweise sehr sensibel auf statistische Informationen zu Tabellen und Indizes reagiert. Ohne korrekte und aktuelle statistische Informationen geht SQL Server eventuell davon aus, der beste Plan für die Ausführung einer bestimmten Abfrage müsse bestimmt werden.

Zu den Statistiken, die in jeder Tabelle in SQL Server 7.0 gewartet werden, um den Optimierer bei der kostenbasierten Entscheidungsfindung zu unterstützen, gehören Folgende:

• Anzahl der Zeilen in der Tabelle.
• Anzahl der von der Tabelle verwendeten Seiten.
• Anzahl der Änderungen, die nach dem letzten Aktualisieren der Statistik an den Schlüsseln durchgeführt wurden.

Für Indizes werden zusätzliche Informationen gespeichert, darunter (für jeden Index):

• Ein Histogramm mit gleich großen Intervallen über die erste Spalte.
• Dichte aller Spaltenpräfixe.
• Durchschnittliche Schlüssellänge.

Statistiken für Indizes werden automatisch erstellt, wenn ein neuer Index erstellt wird. Darüber hinaus ist es jetzt möglich, Statistiken auch für andere Spalten zu erstellen und warten.

Um die statistischen Informationen so aktuell wie möglich zu halten, enthält SQL Server 7.0 die Prozedur AutoStats, die über die SQL Server-Überwachungsfunktion für Tabellenänderungen die Statistik für eine Tabelle automatisch aktualisieren kann, wenn ein bestimmter Änderungsschwellenwert erreicht wurde. Zusätzlich wird mit SQL Server 7.0 die automatische Statistikerstellung eingeführt, die den Server veranlasst, automatisch alle für die akkurate Optimierung einer bestimmten Abfrage erforderlichen Statistiken zu erzeugen.

Bestimmen bevorstehender automatischer Statistikaktualisierungen

Wie bereits erwähnt, aktualisiert die AutoStats-Prozedur automatisch die Statistik für eine bestimmte Tabelle, wenn ein Änderungsschwellenwert erreicht wurde. Die Spalte Sysindexes.rowmodctr verwaltet eine laufende Summe aller Änderungen in einer Tabelle, die sich mit der Zeit nachteilig auf den Entscheidungsprozess des Abfrageprozessors auswirken können. Dieser Zähler wird aktualisiert, wenn eines der folgenden Ereignisse auftritt:

• Eine einzelne Zeile wird eingefügt.
• Eine einzelne Zeile wird gelöscht.
• Eine indizierte Spalte wird aktualisiert.

HINWEIS : Mit TRUNCATE TABLE wird rowmodctr nicht aktualisiert.

Nach dem Aktualisieren der Tabellenstatistik wird der Wert rowmodctr auf 0 zurückgesetzt, und die Schemaversion der Tabellenstatistik wird aktualisiert.

Darüber hinaus wird in Situationen, in denen ein leicht auf Statistiken reagierender Ausführungsplan für eine gespeicherte Prozedur aus dem Cache entnommen wird, die Schemaversion der Statistik mit der aktuellen Version verglichen. Wenn eine neue Statistik verfügbar ist, wird der Plan für die gespeicherte Prozedur erneut kompiliert.

Der Basisalgorithmus für die automatische Statistikaktualisierung lautet wie folgt:

• Wenn die Kardinalität für eine Tabelle weniger als sechs beträgt und die Tabelle sich in der Datenbank tempdb befindet, wird nach jeweils sechs Änderungen in der Tabelle eine automatische Aktualisierung durchgeführt.
• Wenn die Kardinalität für eine Tabelle größer als 6, jedoch kleiner oder gleich 500 ist, wird der Status nach jeweils 500 Änderungen aktualisiert.
• Wenn die Kardinalität für eine Tabelle größer als 500 ist, wird die Statistik nach jeweils (500 + 20% der Tabelle) Änderungen aktualisiert.

HINWEIS: Im engsten Sinn zählt SQL Server Kardinalität als die Anzahl der Zeilen in einer Tabelle.

Die beiden folgenden Beispiele sollen dieses Konzept veranschaulichen:

Beispiel 1

Betrachten Sie die Tabelle Authors in der Datenbank Pubs, die 23 Zeilen und zwei Indizes enthält. Der eindeutige gruppierte Index UPKCL_auidind wird für die Spalte au_id indiziert, und der zusammengesetze, nicht gruppierte Index aunmind wird in den Spalten au_lname und au_fname erstellt. Da diese Tabelle weniger als 500 Zeilen enthält, beginnt die AutoStat-Prozedur nach 500 Änderungen an den Tabellendaten. Bei den Änderungen kann es sich um eine von mehr als 500 Einfüge-, Lösch- oder Änderungsaktionen in einer indizierten Spalte wie au_lname handeln, jedoch auch um eine Kombination mehrerer Aktionen.

Sie können daher vorhersagen, wann UPDATE STATISTICS initiiert wird, indem Sie den Wert sysindexes.rowmodctr überwachen, der bei jeder Aktualisierung steigt. Erreicht oder übersteigt der Wert 500, können Sie damit rechnen, dass UPDATE STATISTICS beginnt.

Beispiel 2

Nehmen Sie jetzt eine zweite Tabelle T2 an, die eine Kardinalität von 1.000 hat. Bei Tabellen mit mehr als 500 Zeilen beginnt SQL Server 7.0 den UPDATE STATISTICS-Vorgang nach (500 + 20%) Änderungen. Da sich als 20% von 1.000 der Wert 200 ergibt, können Sie davon ausgehen, dass die AutoStats-Prozedur nach etwa 700 Änderungen in der Tabelle begonnen wird.

Automatisieren der Zeitpunktbestimmung automatischer Statistikaktualisierungen

Um das Bestimmen des Zeitpunkts zu automatisieren, an dem die AutoStats-Prozedur ausgeführt wird, können Sie die Tabelle Sysindexes abrufen und festlegen, wann Tabellenänderungen den Startpunkt erreichen. Dies ist ein entsprechender Basisalgorithmus:

Sie können zu einem späteren Zeitpunkt einen Auftrag planen, der Folgendes ausführt:

• UPDATE STATISTICS für alle Tabellen ausführen, für die Sie den Prozess im Laufe des Tages deaktivieren mussten.
  
  - Und -
• Erneutes Aktivieren der AutoStats-Prozedur, da der Änderungszähler jeder Tabelle auf 0 zurückgesetzt worden ist, wenn UPDATE STATISTICS ausgeführt wurde.

Steuern der Ausführung von UPDATE STATISTICS für eine Tabelle

Wenn sich die AutoStats-Prozedur als problematisch erwiesen hat, besteht die nahe liegendste Lösung für diese Frage im Deaktivieren der automatischer Statistikerzeugung, so dass der Datenbankadministrator die Möglichkeit hat, UPDATE STATISTICS-Prozesse für ruhigere Zeiten zu planen. Sie können dazu die UPDATE STATISTICS-Anweisung oder die gespeicherte Prozedur sp_autostats verwenden. Die Syntax für die UPDATE STATISTICS-Anweisung lautet:

Die Syntax für die gespeicherte Prozedur sp_autostats lautet:

wobei <stats_flag> entweder "on" oder "off" sein kann.

Sie können auch sp_dboption verwenden, um das automatische Auftreten von UPDATE STATISTICS oder CREATE STATISTICS für einzelne Datenbanken zu deaktivieren:

Steuern der Anzahl gleichzeitiger UPDATE STATISTICS-Prozesse

Derzeit ist es außer durch Deaktivieren der automatischen Statistikaktualisierung für bestimmte Tabellen nicht möglich, die Anzahl der automatischen UPDATE STATISTICS-Anweisungen zu konfigurieren, die gleichzeitig ausgeführt werden (dies wurde in DCR 51539 aufgenommen). Der Server beschränkt jedoch die Anzahl gleichzeitiger UPDATE STATISTICS-Prozesse auf vier pro Prozessor.

Festlegen der Ausführungszeitpunkte automatischer Statistikaktualisierungen

Sie können Ablaufverfolgungsflag 205 verwenden, um zu berichten, wenn eine von der Statistik abhängige gespeicherte Prozedur infolge einer automatischen Statistikaktualisierung erneut kompiliert wird. Dieses Ablaufverfolgungsflag schreibt folgende Meldung in das Fehlerprotokoll:

Wenn Ablaufverfolgungsflag 205 deaktiviert ist, wird die von 8721 erstellte Meldung der automatischen Statistikaktualisierung auch von dieser Meldung umgeben, wenn die Statistiken aktualisiert werden. Die vorangestellte Nachricht kann am Wert RowModCnt erkannt werden, der größer als 0 ist. Die nachgestellte Nachricht nach UPDATE STATISTICS hat einen RowModCnt-Wert von 0:

In dieser Meldung steht "RowModCnt" für die Gesamtanzahl der Änderungen in der Tabelle. "RowModLimit" ist der Schwellenwert, bei dessen Überschreiten die Anweisung UPDATE STATISTICS für die Tabelle ausgeführt wird.

Es ist auch möglich, den Ablaufverfolgungsflag 8721 zu aktivieren, der Informationen im Fehlerprotokoll abbildet, wenn eine automatische Statistikaktualisierung ausgeführt wurde. Folgendes ist ein Beispiel des zu erwartenden Meldungstyps:

In dieser Meldung steht "Mods" für die Gesamtanzahl der Änderungen in der Tabelle. "Bound" ist der Änderungsschwellenwert, "Duration" ist die zum Abschließen der UPDATE STATISTICS-Anweisung erforderliche Dauer, und "UpdCount" ist die Anzahl der aktualisierten Statistikwerte.

Sie können auch SQL Server Profiler dazu verwenden, den Ausführungszeitpunkt der UPDATE STATISTICS-Anweisungen festzustellen. Führen Sie dazu die folgenden Schritte aus:

1. Klicken Sie im Menü von Profiler auf Extras, und klicken Sie dann auf Optionen.
2. Gehen Sie zu der Registerkarte Ereignisse, und wählen Sie dann Alle Ereignisklassen aus.
3. Legen Sie eine neue Ablaufverfolgung fest, und wählen Sie Sonst. unter Ereignisse aus. Wählen Sie das Teilereignis Auto-UpdateStats aus.

HINWEIS: Wird eine große Anzahl von Statistiken durch automatische Statistikaktualisierung aktualisiert, wird möglicherweise eine große Anzahl Meldungen an das Fehlerprotokoll geschrieben. Testen Sie die Ablaufverfolgungsflags sorgfältig, bevor Sie diese auf einem Produktions- oder einem anderen wichtigen Server verwenden.

Schemasperren

SQL Server 7.0 verwendet zwei Typen von Schemasperren, die beide bei der Aktualisierung einer Statistik für eine Tabelle verwendet werden werden:

Sie können diese Sperren anzeigen, indem Sie sp_lock ausführen oder sie in der Syslockinfo-Tabelle auswählen.



Bitte beachten Sie: Bei diesem Artikel handelt es sich um eine Übersetzung aus dem Englischen. Es ist möglich, dass nachträgliche Änderungen bzw. Ergänzungen im englischen Originalartikel in dieser Übersetzung nicht berücksichtigt sind. Die in diesem Artikel enthaltenen Informationen basieren auf der/den englischsprachigen Produktversion(en). Die Richtigkeit dieser Informationen in Zusammenhang mit anderssprachigen Produktversionen wurde im Rahmen dieser Übersetzung nicht getestet. Microsoft stellt diese Informationen ohne Gewähr für Richtigkeit bzw. Funktionalität zur Verfügung und übernimmt auch keine Gewährleistung bezüglich der Vollständigkeit oder Richtigkeit der Übersetzung.