Indexstatistiken

Aktualisiert: 12. Dezember 2006

SQL Server 2005 ermöglicht das Erstellen von statistischen Informationen bezüglich der Verteilung von Werten in einer Spalte. Der Abfrageoptimierer verwendet diese statistischen Informationen, um den optimalen Abfrageplan durch Schätzen der Kosten der Verwendung eines Indexes zum Auswerten der Abfrage zu ermitteln.

Wenn Statistiken erstellt werden, sortiert Datenbankmodul die Werte der Spalten, für die Statistiken erstellt werden, und erstellt ein Histogramm basierend auf bis zu 200 dieser Werte, das durch Intervalle getrennt wird. Das Histogramm gibt an, wie viele Zeilen jedem Intervallwert genau entsprechen und wie viele Zeilen in ein Intervall fallen. Außerdem wird eine Berechnung der Dichte der Werte oder des Vorkommens doppelter Werte in einem Intervall bereitgestellt.

SQL Server 2005 stellt zusätzliche Informationen bereit, die durch Statistiken ermittelt werden, die für char-, varchar-, varchar(max)-, nchar-, nvarchar-, nvarchar(max)-, text- und ntext-Spalten erstellt werden. Diese Informationen, die als Zeichenfolgezusammenfassung bezeichnet werden, unterstützen den Abfrageoptimierer beim Schätzen der Selektivität von Abfrageprädikaten für Zeichenfolgemuster. Zeichenfolgezusammenfassungen führen zu genaueren Schätzungen von Resultsetgrößen und häufig zu besseren Abfrageplänen, wenn LIKE-Bedingungen in einer Abfrage vorhanden sind. Dies schließt Bedingungen wie z. B. WHERE ProductName LIKE '%Bike' und WHERE Name LIKE '[CS]heryl' ein.

Hinweis:

Zeichenfolge-Zusammenfassungsinformationen werden nicht verwaltet, wenn die Zusammenfassung für eine Spaltenstichprobe zu groß ist, um von Datenbankmodul verwaltet werden zu können. Eine Zeichenfolgezusammenfassung wird z. B. nicht für Statistiken verwaltet, die mithilfe von WITH FULLSCAN für eine eindeutige varchar(80)-Spalte mit 80 Zeichen in jeder Zeichenfolge, beinahe keiner Ähnlichkeit zwischen den Zeichenfolgen und für eine Tabelle mit 85.000 Zeilen erstellt wurden. Wenn Sie ermitteln möchten, ob eine Zeichenfolgezusammenfassung für ein bestimmtes Statistikobjekt gespeichert wurde, verwenden Sie DBCC SHOW_STATISTICS (Transact-SQL).

Funktionsweise automatischer Statistiken

Wenn Sie einen Index erstellen, speichert der Abfrageoptimierer automatisch statistische Informationen zu den indizierten Spalten. Wenn die Datenbankoption AUTO_CREATE_STATISTICS auf ON (Standard) festgelegt ist, erstellt Datenbankmodul darüber hinaus automatisch Statistiken für Spalten ohne Indizes, die in einem Prädikat verwendet werden.

Sobald die Daten in einer Spalte geändert werden, sind der Index und die Spaltenstatistiken möglicherweise nicht mehr aktuell, sodass der Abfrageoptimierer nicht mehr die besten Entscheidungen hinsichtlich der Verarbeitung der Abfrage treffen kann. Wenn Sie z. B. eine Tabelle mit einer indizierten Spalte und 1.000 Datenzeilen erstellen, die alle einen eindeutigen Wert in der indizierten Spalte enthalten, legt der Abfrageoptimierer fest, dass die indizierte Spalte eine geeignete Methode zur Verfügung stellt, um die Daten für eine Abfrage zu ermitteln. Wenn Sie die Daten in der Spalte so aktualisieren, dass sie zahlreiche doppelte Werte enthält, ist die Spalte nicht mehr optimal für die Verwendung in einer Abfrage geeignet. Der Abfrageoptimierer geht jedoch weiterhin davon aus, dass die Spalte gut geeignet ist, da er sich weiterhin an der veralteten Verteilungsstatistik des Indexes orientiert, der die Daten vor der Aktualisierung zugrunde liegen.

Hinweis:

Veraltete oder fehlende Statistiken werden als Warnungen angegeben (Tabellenname als rot formatierter Text), wenn der Ausführungsplan einer Abfrage mithilfe von SQL Server Management Studio grafisch angezeigt wird. Weitere Informationen finden Sie unter Anzeigen von grafischen Ausführungsplänen (SQL Server Management Studio). Das Fehlen von Statistiken wird zudem angezeigt, wenn die Missing Column Statistics-Ereignisklasse mithilfe von SQL Server Profiler überwacht wird. Weitere Informationen finden Sie unter Fehler und Warnungen-Ereigniskategorie (Datenbankmodul).

Wenn die Datenbankoption AUTO_UPDATE_STATISTICS auf ON (Standardeinstellung) festgelegt wird, aktualisiert der Abfrageoptimierer diese statistischen Informationen automatisch und regelmäßig, wenn sich die Daten in der Tabelle ändern. Eine Statistikaktualisierung wird immer dann eingeleitet, wenn die in einem Abfrageausführungsplan verwendeten Statistiken einen Test auf aktuelle Statistiken nicht bestehen. Die Stichprobe, die nach dem Zufallsprinzip über die Datenseiten verteilt ist, wird aus der Tabelle oder dem kleinsten nicht gruppierten Index für die Spalten entnommen, die für die Statistik benötigt werden. Nachdem eine Datenseite vom Datenträger gelesen wurde, werden alle Zeilen auf der Datenseite zum Aktualisieren der statistischen Informationen verwendet. Die statistischen Informationen werden fast immer aktualisiert, wenn für ungefähr 20 % der Datenzeilen Änderungen aufgetreten sind. Der Abfrageoptimierer stellt jedoch immer sicher, dass die Stichproben mit einer Mindestanzahl von Zeilen durchgeführt werden. Tabellen, die kleiner als 8 MB sind, werden immer vollständig gescannt, um Statistiken zu ermitteln.

Durch die Stichprobenuntersuchung der Daten anstelle der Analyse aller Daten werden die Kosten automatischer statistischer Aktualisierungen verringert. Unter bestimmten Umständen ist es jedoch nicht möglich, die Merkmale von Daten in einer Tabelle mithilfe der Stichprobenuntersuchung genau zu ermitteln. Sie können den Umfang der Daten, für die während der manuellen Aktualisierung der Statistik eine Stichprobe genommen wird, auf Tabellenbasis steuern, indem Sie die SAMPLE- und FULLSCAN-Klauseln der UPDATE STATISTICS-Anweisung verwenden. Durch die FULLSCAN-Klausel wird angegeben, dass zum Zusammenstellen der Statistiken alle Daten in der Tabelle gescannt werden. Mithilfe der SAMPLE-Klausel können Sie entweder den Prozentsatz oder die Anzahl der Zeilen angeben, für die eine Stichprobe erstellt wird.

Asynchrone Statistikaktualisierungen

Eine Abfrage, die eine Aktualisierung einer veralteten Statistik einleitet, muss warten, bis diese Statistiken aktualisiert wurden, bevor die Kompilierung erfolgt und ein Resultset zurückgegeben wird. Aus diesem Umstand können sich unvorhersehbare Antwortzeiten von Abfragen ergeben; auch können Anwendungen, die kurze Timeouts verwenden, einen Fehler erzeugen.

In SQL Server 2005 stellt die Datenbankoption AUTO_UPDATE_STATISTICS_ASYNC asynchrone Statistikaktualisierung bereit. Wenn diese Option auf ON festgelegt wird, warten Abfragen nicht darauf, dass die Statistiken aktualisiert werden, bevor sie die Kompilierung durchführen. Die veralteten Statistiken werden in diesem Fall in eine Warteschlange verschoben, damit sie durch einen Arbeitsthread in einem Hintergrundprozess aktualisiert werden. Die Abfrage sowie alle anderen gleichzeitigen Abfragen führen die Kompilierung mithilfe der vorhandenen veralteten Statistiken sofort durch. Da keine Verzögerung aufgrund aktualisierter Statistiken entsteht, sind die Antwortzeiten von Abfragen vorhersehbar; die veralteten Statistiken können jedoch dazu führen, dass der Abfrageoptimierer einen weniger effizienten Abfrageplan auswählt. Abfragen, die gestartet werden, nachdem die aktualisierten Statistiken verfügbar sind, verwenden diese Statistiken. Dies kann zur Neukompilierung zwischengespeicherter Pläne führen, die von der Version der älteren Statistiken abhängen. Die Aktualisierung von asynchronen Statistiken kann nicht erfolgen, wenn Anweisungen der Datendefinitionssprache (DDL, Data Definition Language), wie z. B. CREATE-, ALTER- und DROP-Anweisungen, in derselben expliziten Benutzertransaktion vorkommen.

Die AUTO_UPDATE_STATISTICS_ASYNC-Option wird auf Datenbankebene festgelegt; sie bestimmt die Aktualisierungsmethode für alle Statistiken in der Datenbank. Sie gilt nur für die Statistikaktualisierung und kann nicht für das asynchrone Erstellen von Statistiken verwendet werden. Wenn diese Option auf ON festgelegt wird, muss auch AUTO_UPDATE_STATISTICS auf ON festgelegt werden, damit Auswirkungen zu beobachten sind. Standardmäßig ist die AUTO_UPDATE_STATISTICS_ASYNC auf OFF festgelegt. Weitere Informationen zum Festlegen dieser Option finden Sie unter ALTER DATABASE (Transact-SQL).

Stellen Sie sicher, dass die AUTO_UPDATE_STATISTICS_ASYNC-Option auf OFF festgelegt ist, bevor Sie eine Datenbank auf SINGLE_USER festlegen. Wenn der Hintergrundthread, der zum Aktualisieren von Statistiken verwendet wird, auf ON festgelegt wird, wird eine Verbindung mit der Datenbank hergestellt, und Sie können im Einzelbenutzermodus nicht auf die Datenbank zugreifen. Wenn die Option auf ON festgelegt wird, sollten Sie folgende Aufgaben ausführen:

1. Legen Sie AUTO_UPDATE_STATISTICS_ASYNC auf OFF fest.
2. Führen Sie eine Überprüfung auf aktive asynchrone Statistikaufträge aus, indem Sie die dynamische Verwaltungssicht sys.dm_exec_background_job_queue abfragen.
3. Wenn aktive Aufträge vorhanden sind, warten Sie, bis die Aufträge abgeschlossen sind, oder beenden Sie sie manuell mithilfe von KILL STATS JOB.

Überlegungen zu bewährten Methoden

Sie sollten in Betracht ziehen, die AUTO_UPDATE_STATISTICS_ASYNC-Option auf ON festzulegen, wenn die folgenden Merkmale auf Ihre Anwendung zutreffen:

• Sie neigt zu Timeouts bei Clientanforderungen, die durch eine oder mehrere Warteschlangen verursacht werden, die auf aktualisierte Statistiken warten.
• Sie benötigt vorhersehbare Antwortzeiten für Abfragen, auch wenn dann gelegentlich Abfragen mit weniger effizienten Abfrageplänen aufgrund von veralteten Statistiken ausgeführt werden.

Anzeigen der Eigenschaften asynchroner Aktualisierungsstatistiken

Wenn Sie den ON- oder OFF-Status der AUTO_UPDATE_STATISTICS_ASYNC-Option anzeigen möchten, wählen Sie die is_auto_update_stats_async_on-Spalte aus der sys.databases-Katalogsicht aus. Weitere Informationen finden Sie unter sys.databases (Transact-SQL).

Wenn Sie ermitteln möchten, ob die Statistiken für die Aktualisierung in einer Warteschlange platziert wurden oder gerade aktualisiert werden, verwenden Sie die dynamische Verwaltungssicht sys.dm_exec_background_job_queue. Für Statistiken zeigt die object_id1-Spalte die Tabellen- oder Sicht-ID an, und die object_id2-Spalte zeigt die Statistik-ID an. Verwenden Sie die dynamische Verwaltungssicht sys.dm_exec_background_job_queue_stats, um zusammengefasste Statistiken für alle Hintergrund-Auftragswarteschlangen anzuzeigen, z. B. die Anzahl der auf ihre Ausführung wartenden Aufträge, die Anzahl der Anforderungen, die einen Fehler erzeugt haben, und die durchschnittlichen Ausführungszeiten zuvor übermittelter Anforderungen.

Deaktivieren automatischer Statistiken

Es gibt folgende Möglichkeiten, um die automatische Statistikgenerierung für eine bestimmte Spalte oder einen Index zu deaktivieren:

• Verwenden der gespeicherten Systemprozedur sp_autostats.
• Verwenden der STATISTICS_NORECOMPUTE-Klausel der CREATE INDEX-Anweisung.
• Verwenden der NORECOMPUTE-Klausel der UPDATE STATISTICS-Anweisung.
• Verwenden der NORECOMPUTE-Klausel der CREATE STATISTICS-Anweisung.
• Festlegen der Datenbankoptionen AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS auf OFF mithilfe der ALTER DATABASE-Anweisung. Weitere Informationen finden Sie unter Festlegen von Datenbankoptionen.

Wenn Sie festlegen, dass Datenbankmodul Statistiken nicht automatisch verwaltet, müssen Sie die statistischen Informationen manuell aktualisieren.

Hinweis:
Die UPDATE STATISTICS-Anweisung aktiviert die automatische Statistikaktualisierung erneut, wenn nicht die NORECOMPUTE-Klausel angegeben wurde.

Manuelles Erstellen und Aktualisieren von Statistiken

Mithilfe der gespeicherten Systemprozedur sp_createstats können Statistiken auch für alle infrage kommenden Spalten in allen Benutzertabellen der aktuellen Datenbank mit einer einzigen Anweisung erstellt werden. Statistiken können für bestimmte Spalten einer Tabelle oder Sicht mithilfe der CREATE STATISTICS-Anweisung erstellt und mithilfe der UPDATE STATISTICS-Anweisung aktualisiert werden. Die maximale Anzahl von Statistiken, die für eine Tabelle oder Sicht unabhängig von einem Index erstellt werden kann, beträgt 2000. Jede Spalte oder Kombination aus Spalten, die als Indexschlüssel geeignet ist, ist auch für Statistiken geeignet. Dabei gelten folgende Ausnahmen:

• Spalten vom LOB-Typ (Large Object) können mit Ausnahme von xml angegeben werden. Die Typen varchar(max), nvarchar(max), varbinary(max), image, text und ntext können angegeben werden.
• Die maximal zulässige Größe der kombinierten Spaltenwerte darf die 900-Byte-Grenze überschreiten, der der Indexschlüsselwert unterliegt.

Die Statistiken, die für eine Spalte generiert wurden, können gelöscht werden, wenn Sie sie nicht mehr beibehalten und verwalten möchten.

Wenn Sie Statistiken manuell erstellen, können Sie Statistiken erstellen, die mehrere Spaltendichten enthalten. Diese geben die durchschnittliche Anzahl der Duplikate für die Kombination der Spalten an. Eine Abfrage enthält z. B. die folgende Klausel WHERE a = 7 and b = 9.

Durch das manuelle Erstellen von Statistiken für beide Spalten (a, b) zusammen kann es Datenbankmodul ermöglicht werden, eine bessere Schätzung für die Abfrage vorzunehmen, da die Statistiken ebenfalls die durchschnittliche Anzahl unterschiedlicher Werte für die Kombination aus den a- und b-Spalten enthalten.

So erstellen Sie Statistiken für eine Spalte

CREATE STATISTICS (Transact-SQL)

So erstellen Sie Statistiken für alle infrage kommenden Spalten in allen Benutzertabellen

sp_createstats (Transact-SQL)

So aktualisieren Sie Statistiken manuell

UPDATE STATISTICS (Transact-SQL)

So zeigen Sie die Statistiken für eine Tabelle an

DBCC SHOW_STATISTICS (Transact-SQL)

So löschen Sie die Statistiken für eine Spalte

DROP STATISTICS (Transact-SQL)

Arbeiten mit Statistiken nach dem Aktualisieren einer Datenbank auf SQL Server 2005

Wenn Sie einer Datenbank aus einer früheren Version von SQL Server auf SQL Server 2005 aktualisieren, werden alle Statistiken aus der früheren Version als veraltet betrachtet. Aus diesem Grund werden bei der ersten Verwendung die Statistiken, die für eine Aktualisierung unter der Datenbankoption AUTO_UPDATE_STATISTICS infrage kommen, mithilfe der Standardstichprobenrate aktualisiert. Dieses Feature weist wichtige Vorteile auf, und Sie müssen in der Regel keine Aktionen ausführen. In seltenen Fällen kann dies jedoch zu weniger genauen Statistiken führen, wenn die Statistiken in einer früheren Version von SQL Server mithilfe von FULLSCAN oder einer anderen hohen Stichprobenrate manuell berechnet wurden oder die Tabelle, für die die Stichprobe genommen wird, größer als 8 MB und die Datenverteilung nicht zufällig ist. Tatsächlich kann eine Verringerung der Stichprobenrate für FULLSCAN-Statistiken für Tabellen, die größer als 8 MB sind, immer auftreten, wenn AUTO_UPDATE_STATISTICS ausgeführt wird. Die anfängliche Statistikaktualisierung kann einfach zu einem früheren Zeitpunkt auftreten, nachdem Sie die Aktualisierung auf die neue Version von SQL Server vorgenommen haben.

Die Vorteile der Aktualisierung von Statistiken auf das SQL Server 2005-Format ergeben sich daraus, dass SQL Server 2005-Statistiken für eine bestimmte Stichprobenrate in der Regel eine höhere Qualität aufweisen als in SQL Server 2000 oder früher. Außerdem erstellt SQL Server 2005 besondere Zeichenfolgezusammenfassungs-Statistiken für Zeichenspalten, wie zuvor beschrieben wurde. Weitere Informationen zu Statistiken in SQL Server 2005 finden Sie auf dieser Microsoft-Website.

Überlegungen zu bewährten Methoden

In den meisten Fällen müssen Sie keine besonderen Aktionen hinsichtlich der Statistiken durchführen, nachdem Sie eine Datenbank aktualisiert haben. Wenn Sie jedoch eine große Datenbank mit anspruchsvollen Leistungsanforderungen verwenden, wird empfohlen, nach der Aktualisierung sp_updatestats (Transact-SQL) mit der RESAMPLE-Option auszuführen. Auf diese Weise bleiben die früheren Stichprobenraten erhalten, und alle Statistiken werden auf das aktuelle Format aktualisiert. Denken Sie daran, dass Statistiken, die während der Indexerstellung erstellt werden, mithilfe der FULLSCAN-Stichprobenrate erstellt werden. Diese sowie andere FULLSCAN-Statistiken verwenden nach der Aktualisierung aufgrund von AUTO_UPDATE_STATISTICS die Standardstichprobenrate. Wenn Sie es vorziehen, nicht alle Statistiken durch Ausführen von sp_updatestats zu aktualisieren, können Sie UPDATE STATISTICS für das selektive Aktualisieren von Statistiken für Indizes und andere FULLSCAN-Statistiken mit der FULLSCAN-Stichprobenrate nach der Datenbankaktualisierung verwenden.

Siehe auch

Konzepte

Optimieren von Indizes

Andere Ressourcen

CREATE INDEX (Transact-SQL)
Optimieren von Abfragen
sp_autostats (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf