Artikel
09/24/2010

Ressourcenverwendung bei der Zeilenversionsverwaltung

Das Framework für die Zeilenversionsverwaltung unterstützt die folgenden in SQL Server verfügbaren Features:

Trigger
Multiple Active Results Sets (MARS)
Online-Indizierung

Das Framework für die Zeilenversionsverwaltung unterstützt zudem die folgenden auf der Zeilenversionsverwaltung basierenden Transaktionsisolationsstufen, die standardmäßig nicht aktiviert sind:

Wenn für die Datenbankoption READ_COMMITTED_SNAPSHOT der Wert ON festgelegt ist, bieten READ_COMMITTED-Transaktionen bei Verwendung der Zeilenversionsverwaltung eine Lesekonsistenz auf Anweisungsebene.
Wenn für die Datenbankoption ALLOW_SNAPSHOT_ISOLATION der Wert ON festgelegt ist, bieten SNAPSHOT-Transaktionen bei Verwendung der Zeilenversionsverwaltung eine Lesekonsistenz auf Anweisungsebene.

Durch die auf der Zeilenversionsverwaltung basierenden Isolationsstufen wird die Anzahl der von der Transaktion abgerufenen Sperren dadurch reduziert, dass keine freigegebenen Sperren für Lesevorgänge verwendet werden. Auf diese Weise wird die Systemleistung erhöht, da die Anzahl der für die Verwaltung der Sperren verwendeten Ressourcen reduziert wird. Die Leistung wird zudem dadurch erhöht, dass die Anzahl von Sperrungen einer Transaktion durch von anderen Transaktionen angeforderte Sperren verringert wird.

Auf der Zeilenversionsverwaltung basierende Isolationsstufen erhöhen die von Datenänderungen benötigten Ressourcen. Bei Aktivierung dieser Optionen werden für alle Datenänderungen für die Datenbank Versionen angegeben. Eine Kopie der Daten in dem Zustand vor der Änderung wird in tempdb gespeichert. Dies ist auch dann der Fall, wenn keine aktiven Transaktionen die auf der Zeilenverwaltung basierende Isolation verwenden. Die Daten nach der Änderung enthalten einen Verweis auf die in tempdb gespeicherten Daten, die über eine Versionsangabe verfügen. Im Fall von großen Objekten wird nur ein Teil des geänderten Objekts in tempdb gespeichert.

In tempdb verwendeter Speicherplatz

tempdb muss für jede Instanz von Database Engine (Datenbankmodul) über genügend Speicherplatz für die Zeilenversionen verfügen, die für sämtliche Datenbanken in der Instanz generiert wurden. Der Datenbankadministrator muss sicherstellen, dass tempdb über genügend Speicherplatz verfügt, um den Versionsspeicher zu unterstützen. In tempdb befinden sich zwei Versionsspeicher:

Der Onlineindexerstellungs-Versionsspeicher wird für Onlineindexerstellungen in allen Datenbanken verwendet.
Der allgemeine Versionsspeicher wird für alle anderen Datenänderungsvorgänge in sämtlichen Datenbanken verwendet.

Zeilenversionen müssen so lange gespeichert werden, wie eine aktive Transaktion darauf zugreifen muss. Einmal pro Minute entfernt ein Hintergrundthread nicht mehr benötigte Zeilenversionen und gibt so Versionsspeicherplatz in tempdb frei. Eine Transaktion mit langer Ausführungszeit verhindert, dass der Speicherplatz im Versionsspeicher freigegeben werden kann, wenn sie eine der folgenden Bedingungen erfüllt:

Sie verwendet die auf der Zeilenversionsverwaltung basierende Isolation.
Sie verwendet Trigger, MARS oder Onlineindexerstellungs-Vorgänge.
Sie generiert Zeilenversionen.

Hinweis
Wenn innerhalb einer Transaktion ein Trigger aufgerufen wird, werden die vom Trigger generierten Zeilenversionen bis zum Ende der Transaktion beibehalten, auch wenn die Zeilenversionen nach Abschluss des Triggers nicht mehr benötigt werden. Dies gilt auch für Read Committed-Transaktionen, die Zeilenversionsverwaltung verwenden. Bei diesem Transaktionstyp wird nur für die einzelnen Anweisungen in der Transaktion eine im Hinblick auf Transaktionen konsistente Sicht der Datenbank benötigt. Dies bedeutet, dass die für eine Anweisung in der Transaktion erstellten Zeilenversionen nach Abschluss der Anweisung nicht mehr benötigt werden. Die von den einzelnen Anweisungen in der Transaktion erstellten Zeilenversionen werden jedoch bis zum Abschluss der Transaktion beibehalten.

Wenn innerhalb einer Transaktion ein Trigger aufgerufen wird, werden die vom Trigger generierten Zeilenversionen bis zum Ende der Transaktion beibehalten, auch wenn die Zeilenversionen nach Abschluss des Triggers nicht mehr benötigt werden. Dies gilt auch für Read Committed-Transaktionen, die Zeilenversionsverwaltung verwenden. Bei diesem Transaktionstyp wird nur für die einzelnen Anweisungen in der Transaktion eine im Hinblick auf Transaktionen konsistente Sicht der Datenbank benötigt. Dies bedeutet, dass die für eine Anweisung in der Transaktion erstellten Zeilenversionen nach Abschluss der Anweisung nicht mehr benötigt werden. Die von den einzelnen Anweisungen in der Transaktion erstellten Zeilenversionen werden jedoch bis zum Abschluss der Transaktion beibehalten.

Wenn tempdb nicht mehr über genügend Speicherplatz verfügt, erzwingt Database Engine (Datenbankmodul) eine Verkleinerung der Versionsspeicher. Während des Verkleinerungsprozesses werden die Transaktionen mit der längsten Ausführungszeit, die noch keine Zeilenversionen generiert haben, als Opfer gekennzeichnet. Die Meldung 3967 wird im Fehlerprotokoll für jede Opfertransaktion generiert. Wenn eine Transaktion als Opfer gekennzeichnet ist, kann sie die Zeilenversionen im Versionsspeicher nicht mehr lesen. Wenn die Transaktion versucht, Zeilenversionen zu lesen, wird die Meldung 3966 generiert, und es wird ein Rollback für die Transaktion ausgeführt. Ist die Verkleinerung des Prozesses erfolgreich, wird Speicherplatz in tempdb verfügbar. Anderenfalls ist in tempdb nicht mehr genügend Speicherplatz vorhanden, und folgender Fehler tritt auf:

Schreibvorgänge werden weiterhin ausgeführt, generieren jedoch keine Versionen. Eine Informationsmeldung (3959) wird im Fehlerprotokoll angezeigt. Die Transaktion, die Daten schreibt, ist jedoch nicht betroffen.
Transaktionen, die versuchen, auf Zeilenversionen zuzugreifen, die aufgrund eines vollständigen tempdb-Rollbacks nicht generiert wurden, werden beendet, und der Fehler 3958 wird ausgegeben.

In Datenzeilen verwendeter Speicherplatz

Jede Datenbankzeile kann am Ende der Zeile bis zu 14 Byte für Zeilenversionsverwaltungs-Informationen nutzen. Zu den Zeilenversionsverwaltungs-Informationen zählen die Transaktionssequenznummer der Transaktion, die den Commit für die Version ausgeführt hat, sowie der Verweis auf die Zeile mit Versionsangabe. Diese 14 Byte werden hinzugefügt, wenn die Zeile zum ersten Mal geändert wird oder wenn unter einer der folgenden Bedingungen eine neue Zeile eingefügt wird:

Die Optionen READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION sind aktiviert (ON).
Die Tabelle verfügt über einen Trigger.
Multiple Active Results Sets (MARS) wird verwendet.
Onlineindexerstellungs-Vorgänge werden derzeit für die Tabelle ausgeführt.

Diese 14 Byte werden aus der Datenbankzeile entfernt, wenn die Zeile zum ersten Mal unter allen der folgenden Bedingungen geändert wird:

Die Optionen READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION sind deaktiviert (OFF).
Der Trigger ist nicht mehr für die Tabelle vorhanden.
MARS wird nicht verwendet.
Es werden derzeit keine Onlineindexerstellungs-Vorgänge ausgeführt.

Der Datenbank sollte so viel Speicherplatz zugeordnet werden, dass sie 14 Bytes pro Datenbankzeile aufnehmen kann, falls eines der Feature zur Zeilenversionsverwaltung verwendet wird. Das Hinzufügen von Zeilenversionsverwaltungs-Informationen kann Indexseitenteilungen oder die Zuordnung einer neuen Datenseite zur Folge haben, falls auf der aktuellen Seite nicht genügend Speicherplatz verfügbar ist. Beispiel: Wenn die durchschnittliche Zeilenlänge 100 Bytes beträgt, wächst eine vorhandene Tabelle durch die zusätzlichen 14 Bytes um 14 Prozent.

Durch Verringern des Füllfaktors kann die Fragmentierung der Indexseiten reduziert oder verhindert werden. Zu Anzeigen der Fragmentierungsinformationen für die Daten und Indizes einer Tabelle oder Sicht können Sie DBCC SHOWCONTIG verwenden.

In großen Objekten verwendeter Speicherplatz

SQL Server Database Engine (Datenbankmodul) unterstützt sechs Datentypen, die lange Zeichenfolgen von bis zu 2 Gigabyte (GB) Länge aufnehmen können: nvarchar(max), varchar(max), varbinary(max), ntext, text und image. Lange Zeichenfolgen, die mithilfe dieser Datentypen gespeichert werden, werden in einer Reihe von Datenfragmenten gespeichert, die mit der Datenzeile verknüpft sind. Zeilenversionsverwaltungs-Informationen werden in sämtlichen Fragmenten gespeichert, die zum Speichern dieser langen Zeichenfolgen verwendet werden. Datenfragmente stellen eine Sammlung von Seiten dar, die für große Objekte in einer Tabelle dediziert sind.

Wenn einer Datenbank neue große Werte hinzugefügt werden, werden diese mithilfe von maximal 8.040 Byte an Daten pro Fragment zugeordnet. In früheren Versionen von Database Engine (Datenbankmodul) wurden bis zu 8.080 Byte an ntext-, text- oder image-Daten pro Fragment gespeichert.

Vorhandene ntext-, text- und image-Daten großer Objekte (LOB, Large Objects) werden nicht aktualisiert, um Speicherplatz für die Zeilenversionsverwaltungs-Informationen freizugeben, wenn ein Update einer Datenbank von einer früheren Version von SQL Server auf SQL Server durchgeführt wird. Wenn die LOB-Daten jedoch zum ersten Mal geändert werden, wird mit ihnen ein dynamisches Update durchgeführt, um das Speichern von Versionsinformationen zu ermöglichen. Dies ist auch dann der Fall, wenn keine Zeilenversionen generiert werden. Nachdem ein Update mit den LOB-Daten durchgeführt wurde, wird die maximale Byteanzahl, die pro Fragment gespeichert wird von 8.080 auf 8.040 reduziert. Der Updateprozess ist dem Löschen des LOB-Werts und dem erneuten Einsetzen desselben Werts gleichwertig. Ein Update der LOB-Daten wird auch dann durchgeführt, wenn nur ein Byte geändert wird. Es handelt sich hierbei um einen einmaligen Vorgang für jede ntext-, text-, oder image-Spalte. Durch jeden Vorgang wird jedoch je nach dem Umfang der LOB-Daten eine hohe Menge an Seitenzuordnungen und E/A-Aktivitäten generiert. Es können zudem viele Protokollierungsaktivitäten generiert werden, sofern die Änderung vollständig protokolliert wird. WRITETEXT- und UPDATETEXT-Vorgänge werden minimal protokolliert, wenn der Datenbankwiederherstellungsmodus auf den Wert FULL festgelegt ist.

Die nvarchar(max)-, varchar(max)- und varbinary(max)-Datentypen sind in früheren Versionen von SQL Server nicht verfügbar. Aus diesem Grund weisen sie keine Updateprobleme auf.

Es sollte genügend Speicherplatz zugeordnet werden, um dieser Anforderung gerecht zu werden.

Überwachen der Zeilenversionsverwaltung und des Versionsspeichers

Für die Überwachung von Zeilenversionsverwaltungs-, Versionsspeicher- und Snapshotisolationsprozessen in Bezug auf die Leistung und Probleme stellt SQL Server Tools in Form von dynamische Verwaltungssichten (DMVs, Dynamic Management Views) und Leistungsindikatoren im Systemmonitor von Windows zur Verfügung.

DMVs

Die folgenden DMVs stellen Informationen zu den aktuellen Systemstatus von tempdb und den Versionsspeicher sowie die Transaktionen bereit, die die Zeilenversionsverwaltung verwenden.

sys.dm_db_file_space_usage. Gibt Informationen zur Speicherverwendung für jede Datei in der Datenbank zurück. Weitere Informationen finden Sie unter sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage. Gibt Aktivität für die Seitenzuordnung und die Zuordnungsaufhebung nach Sitzung für die Datenbank zurück. Weitere Informationen finden Sie unter sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage. Gibt Aktivität für die Seitenzuordnung und die Zuordnungsaufhebung nach Task für die Datenbank zurück. Weitere Informationen finden Sie unter sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators. Gibt eine virtuelle Tabelle für die Objekte zurück, die die meisten Versionen im Versionsspeicher erzeugen. Hierbei werden die ersten 256 aggregierten Datensatzlängen nach database_id und rowset_id gruppiert. Mithilfe dieser Funktion können Sie die größten Consumer des Versionsspeichers finden. Weitere Informationen finden Sie unter sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store. Gibt eine virtuelle Tabelle zurück, die alle Versionsdatensätze im allgemeinen Versionsspeicher anzeigt. Weitere Informationen finden Sie unter sys.dm_tran_version_store (Transact-SQL).

Hinweis
Die Ausführung von sys.dm_tran_top_version_generators und sys.dm_tran_version_store kann sehr teuer sein, da beide Funktionen den gesamten Versionsspeicher abfragen, der möglicherweise sehr groß ist.

sys.dm_tran_active_snapshot_database_transactions. Gibt eine virtuelle Tabelle für alle aktiven Transaktionen in sämtlichen Datenbanken in der SQL Server-Instanz zurück, die die Zeilenversionsverwaltung verwenden. Systemtransaktionen werden in dieser DMV nicht angezeigt. Weitere Informationen finden Sie unter sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot. Gibt eine virtuelle Tabelle zurück, die Snapshots anzeigt, die von den einzelnen Transaktionen erstellt wurden. Der Snapshot enthält die Sequenznummer der aktiven Transaktionen, die die Zeilenversionsverwaltung verwenden. Weitere Informationen finden Sie unter sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction. Gibt eine einzelne Zeile zurück, die auf die Zeilenversionsverwaltung bezogene Statusinformationen der Transaktion in der aktuellen Sitzung anzeigt. Weitere Informationen finden Sie unter sys.dm_tran_current_transactions (Transact-SQL).

sys.dm_tran_current_snapshot. Gibt eine virtuelle Tabelle zurück, die alle aktiven Transaktionen zum Zeitpunkt des Startens der aktuellen Snapshotisolation aufführt. Falls die aktuelle Transaktion die Snapshotisolation verwendet, gibt diese Funktion keine Zeilen zurück. sys.dm_tran_current_snapshot ist mit sys.dm_tran_transactions_snapshot vergleichbar, gibt jedoch nur die aktiven Transaktionen für den aktuellen Snapshot zurück. Weitere Informationen finden Sie unter sys.dm_tran_current_snapshot (Transact-SQL).

Leistungsindikatoren

SQL Server-Leistungsindikatoren stellen Informationen zur Auswirkung von SQL Server-Prozessen auf die Systemleistung zur Verfügung. Die folgenden Leistungsindikatoren überwachen tempdb und den Versionsspeicher sowie Transaktionen mithilfe der Zeilenversionsverwaltung. Die Leistungsindikatoren sind im SQLServer:Transaktionen-Leistungsobjekt enthalten.

Freier Speicherplatz in 'tempdb' (KB). Überwacht die Menge des freien Speicherplatzes in Kilobyte (KB), der in der tempdb-Datenbank zur Verfügung steht. Es muss genügend freier Speicherplatz in tempdb zur Verfügung stehen, um den Versionsspeicher zu bearbeiten, der die Snapshotisolation unterstützt.

Die folgende Formel ermöglicht eine grobe Schätzung der Größe des Versionsspeichers. Bei lange andauernden Transaktionen kann es sich als sinnvoll erweisen, die Generierungs- und Cleanuprate zu überwachen, um die maximale Größe des Versionsspeichers einzuschätzen.

[size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

Die längste Ausführungszeit von Transaktionen sollte Onlineindexerstellungs-Vorgänge nicht einschließen. Da diese Vorgänge bei sehr großen Tabellen viel Zeit in Anspruch nehmen können, verwenden Onlineindexerstellungs-Vorgänge einen separaten Versionsspeicher. Die ungefähre Größe des Onlineindexerstellungs-Versionsspeichers entspricht der Menge der in der Tabelle geänderten Daten, einschließlich aller Indizes, während die Onlineindexerstellung aktiviert ist.

Versionsspeichergröße (KB). Überwacht die Größe in KB aller Versionsspeicher. Mithilfe dieser Informationen können Sie die Menge des Speicherplatzes bestimmen, die in der tempdb-Datenbank für den Versionsspeicher benötigt wird. Das Überwachen dieser Indikatoren über einen gewissen Zeitraum ermöglicht eine hilfreiche Schätzung des zusätzlich für tempdb benötigten Speicherplatzes.

Versionsgenerierungsrate (KB/s). Überwacht die Versionsgenerierungsrate, in KB pro Sekunde, in allen Versionsspeichern.

Versionscleanuprate (KB/s). Überwacht die Versionscleanuprate, in KB pro Sekunde, in allen Versionsspeichern.

Hinweis
Die Informationen aus Versionsgenerierungsrate (KB/s) und Versionscleanuprate (KB/s) können zur Vorhersage von Speicherplatzanforderungen für tempdb verwendet werden.

Anzahl der Versionsspeichereinheiten. Überwacht die Anzahl der Versionsspeichereinheiten.

Erstellung von Versionsspeichereinheiten. Überwacht die Gesamtzahl der Versionsspeichereinheiten, die für das Speichern von Zeilenversionen erstellt wurden, seitdem die Instanz gestartet wurde.

Abschneiden von Versionsspeichereinheiten. Überwacht die Gesamtzahl der Versionsspeichereinheiten, die abgeschnitten wurden, seitdem die Instanz gestartet wurde. Eine Versionsspeichereinheit wird abgeschnitten, wenn SQL Server bestimmt, dass keine der Versionszeilen, die in der Versionsspeichereinheit gespeichert sind, für die Ausführung aktiver Transaktionen benötigt wird.

Updatekonfliktquote. Überwacht die Quote von Update-Snapshottransaktionen, die Updatekonflikte aufweisen, im Verhältnis zur Gesamtzahl der Update-Snapshottransaktionen.

Längste Transaktionsausführungszeit. Überwacht die längste Ausführungszeit in Sekunden aller Transaktionen, die die Zeilenversionsverwaltung verwenden. Hiermit kann bestimmt werden, ob eine Transaktion über eine nicht vertretbare Zeitdauer ausgeführt wird.

Transaktionen. Überwacht die Gesamtzahl aktiver Transaktionen. Dieser Leistungsindikator schließt keine Systemtransaktionen ein.

Snapshottransaktionen. Überwacht die Gesamtzahl aktiver Snapshottransaktionen.

Update-Snapshottransaktionen. Überwacht die Gesamtzahl aktiver Snapshottransaktionen, die Updatevorgänge ausführen.

Nichtsnapshot-Versionstransaktionen. Überwacht die Gesamtzahl aktiver Nichtsnapshot-Transaktionen, die Versionsdatensätze generieren.

Hinweis
Die Summe von Update-Snapshottransaktionen und Nichtsnapshot-Versionstransaktionen stellt die Gesamtzahl der Transaktionen dar, die an der Versionsgenerierung teilnehmen. Die Differenz zwischen Snapshottransaktionen und Update-Snapshottransaktionen gibt die Anzahl der schreibgeschützten Snapshottransaktionen an.