Freigeben über


sys.dm_db_index_operational_stats (Transact-SQL)

Gibt aktuelle E/A-Aktivitäten auf niedriger Ebene sowie Aktivitäten im Zusammenhang mit Sperren, Latches und Zugriffsmethoden für alle Partitionen einer Tabelle oder eines Indexes in der Datenbank zurück.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

sys.dm_db_index_operational_stats (
    { database_id | NULL | 0 | DEFAULT }
    , { object_id | NULL | 0 | DEFAULT }
    , { index_id | 0 | NULL | -1 | DEFAULT }
    , { partition_number | NULL | 0 | DEFAULT }
)

Argumente

  • database_id | NULL | 0 | DEFAULT
    ID der Datenbank. database_id ist vom Datentyp smallint. Gültige Eingaben sind die ID einer Datenbank, NULL, 0 oder DEFAULT. Die Standardeinstellung ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertig.

    Geben Sie NULL an, wenn Informationen zu allen Datenbanken in der Instanz von SQL Server zurückgegeben werden sollen. Wenn Sie für database_id NULL angeben, müssen Sie auch für object_id, index_id und partition_number NULL angeben.

    Die integrierte DB_ID-Funktion kann angegeben werden. Wenn DB_ID verwendet wird, ohne dass ein Datenbankname angegeben wird, muss der Kompatibilitätsgrad der aktuellen Datenbank 90 sein.

  • object_id | NULL | 0 | DEFAULT
    Objekt-ID der Tabelle oder Sicht, für die der Index gilt. object_id ist vom Datentyp int.

    Gültige Eingaben sind die ID einer Tabelle und Sicht, NULL, 0 oder DEFAULT. Die Standardeinstellung ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertig.

    Geben Sie NULL an, wenn zwischengespeicherte Informationen zu allen Tabellen und Sichten in der angegebenen Datenbank zurückgegeben werden sollen. Wenn Sie NULL für object_id angeben, müssen Sie auch NULL für index_id und partition_number angeben.

  • index_id | 0 | NULL | -1 | DEFAULT
    ID des Indexes. index_id ist vom Datentyp int. Gültige Eingaben sind die ID eines Indexes, 0, falls object_id ein Heap ist, NULL, -1 oder DEFAULT. Der Standardwert ist -1. NULL, -1 und DEFAULT sind in diesem Kontext gleichwertig.

    Geben Sie NULL an, wenn zwischengespeicherte Informationen zu allen Indizes für eine Basistabelle oder Sicht zurückgegeben werden sollen. Wenn Sie für index_id NULL angeben, müssen Sie auch für partition_number NULL angeben.

  • partition_number | NULL | 0 | DEFAULT
    Partitionsnummer im Objekt. partition_number ist vom Datentyp int. Gültige Eingaben sind der partion_number-Wert eines Indexes bzw. Heaps, NULL, 0 oder DEFAULT. Die Standardeinstellung ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertig.

    Geben Sie NULL an, um zwischengespeicherte Informationen für alle Partitionen des Indexes oder Heaps zurückzugeben.

    partition_number ist 1-basiert. Bei einem nicht partitionierten Index oder Heap ist partition_number auf 1 festgelegt.

Zurückgegebene Tabelle

Spaltenname

Datentyp

Beschreibung

database_id

smallint

Datenbank-ID.

object_id

int

ID der Tabelle oder Sicht.

index_id

int

ID des Indexes oder Heaps.

0 = Heap

partition_number

int

Auf 1 basierende Partitionsnummer im Index oder Heap.

leaf_insert_count

bigint

Gesamtzahl der Einfügevorgänge auf Blattebene.

leaf_delete_count

bigint

Gesamtzahl der Löschvorgänge auf Blattebene.

leaf_update_count

bigint

Gesamtzahl der Aktualisierungen auf Blattebene.

leaf_ghost_count

bigint

Gesamtzahl der Zeilen auf Blattebene, die als gelöscht markiert sind, jedoch noch nicht entfernt wurden. Diese Zeilen werden durch einen Cleanupthread in bestimmten Intervallen entfernt. In diesem Wert sind keine Zeilen enthalten, die aufgrund einer ausstehenden Momentaufnahmeisolationstransaktion beibehalten werden. Weitere Informationen zu Momentaufnahmeisolationstransaktionen finden Sie unter Auf Zeilenversionsverwaltung basierende Isolationsstufen im Datenbankmodul.

nonleaf_insert_count

bigint

Gesamtzahl der Einfügevorgänge über der Blattebene.

0 = Heap

nonleaf_delete_count

bigint

Gesamtzahl der Löschvorgänge über der Blattebene.

0 = Heap

nonleaf_update_count

bigint

Gesamtzahl der Aktualisierungen über der Blattebene.

0 = Heap

leaf_allocation_count

bigint

Gesamtzahl der Seitenzuordnungen auf Blattebene im Index oder Heap.

Bei einem Index entspricht eine Seitenzuordnung einer Seitenteilung.

nonleaf_allocation_count

bigint

Gesamtzahl der durch Seitenteilungen über der Blattebene verursachten Seitenzuordnungen.

0 = Heap

leaf_page_merge_count

bigint

Gesamtzahl der Seitenzusammenführungen auf der Blattebene.

nonleaf_page_merge_count

bigint

Gesamtzahl der Seitenzusammenführungen über der Blattebene.

0 = Heap

range_scan_count

bigint

Gesamtzahl der im Index oder Heap gestarteten Bereichs- und Tabellenscans.

singleton_lookup_count

bigint

Gesamtzahl der Abrufvorgänge einzelner Zeilen aus dem Index oder Heap.

forwarded_fetch_count

bigint

Anzahl der über einen weitergeleiteten Datensatz abgerufenen Zeilen.

0 = Indizes

lob_fetch_in_pages

bigint

Gesamtzahl der aus der LOB_DATA-Zuordnungseinheit abgerufenen LOB-Seiten (Large Object). Diese Seiten enthalten Daten, die in Spalten vom Typ text, ntext, image, varchar(max), nvarchar(max), varbinary(max) und xml gespeichert werden. Weitere Informationen finden Sie unter Datentypen (Transact-SQL). Weitere Informationen zu Zuordnungseinheiten finden Sie unter Organisationsstruktur von Tabellen und Indizes.

lob_fetch_in_bytes

bigint

Gesamtzahl der abgerufenen LOB-Datenbytes.

lob_orphan_create_count

bigint

Gesamtzahl verwaister LOB-Werte, die für Massenvorgänge erstellt werden.

0 = Nicht gruppierter Index

lob_orphan_insert_count

bigint

Gesamtzahl verwaister LOB-Werte, die während Massenvorgängen eingefügt werden.

0 = Nicht gruppierter Index

row_overflow_fetch_in_pages

bigint

Gesamtwert der Zeilenüberlauf-Datenseiten, die aus der ROW_OVERFLOW_DATA-Zuordnungseinheit abgerufen werden.

Diese Seiten enthalten in Spalten vom Typ varchar(n), nvarchar(n), varbinary(n) und sql_variant gespeicherte Daten, die durch Ausführen eines Pushs außerhalb von Zeilen verschoben wurden. Weitere Informationen finden Sie unter Zeilenüberlauf bei Daten über 8 KB. Weitere Informationen zu Zuordnungseinheiten finden Sie unter Organisationsstruktur von Tabellen und Indizes.

row_overflow_fetch_in_bytes

bigint

Gesamtzahl der abgerufenen Zeilenüberlauf-Datenbytes.

column_value_push_off_row_count

bigint

Gesamtzahl der Spaltenwerte für LOB-Daten und Zeilenüberlaufdaten, die durch Ausführen eines Pushs außerhalb von Zeilen verschoben wurden, damit eine eingefügte oder aktualisierte Zeile auf eine Seite passt.

column_value_pull_in_row_count

bigint

Gesamtwert der Spaltenwerte für LOB-Daten und Zeilenüberlaufdaten, die durch Ausführen eines Pulls innerhalb eine Zeile verschoben werden. Dieser Vorgang findet statt, wenn Speicherplatz in einem Datensatz durch eine Aktualisierung frei gemacht wird und die Möglichkeit besteht, durch Ausführen eines Pulls einen oder mehrere Werte außerhalb von Zeilen aus den Zuordnungseinheiten LOB_DATA oder ROW_OVERFLOW_DATA zur IN_ROW_DATA-Zuordnungseinheit zu verschieben. Weitere Informationen zu Zuordnungseinheiten finden Sie unter Organisationsstruktur von Tabellen und Indizes.

row_lock_count

bigint

Gesamtzahl der angeforderten Zeilensperren.

row_lock_wait_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) auf eine Zeilensperre gewartet hat.

row_lock_wait_in_ms

bigint

Gesamtzahl der Millisekunden, die Database Engine (Datenbankmodul) auf eine Zeilensperre gewartet hat.

page_lock_count

bigint

Gesamtzahl der angeforderten Seitensperren.

page_lock_wait_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) auf eine Seitensperre gewartet hat.

page_lock_wait_in_ms

bigint

Gesamtzahl der Millisekunden, die Database Engine (Datenbankmodul) auf eine Seitensperre gewartet hat.

index_lock_promotion_attempt_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) versucht hat, Sperren auszuweiten.

index_lock_promotion_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) Sperren ausgeweitet hat.

page_latch_wait_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) aufgrund eines Latchkonflikts gewartet hat.

page_latch_wait_in_ms

bigint

Gesamtzahl der Millisekunden, die Database Engine (Datenbankmodul) aufgrund eines Latchkonflikts gewartet hat.

page_io_latch_wait_count

bigint

Gesamthäufigkeit, mit der Database Engine (Datenbankmodul) auf einen E/A-Seitenlatch gewartet hat.

page_io_latch_wait_in_ms

bigint

Gesamtzahl der Millisekunden, die Database Engine (Datenbankmodul) auf einen E/A-Seitenlatch gewartet hat.

tree_page_latch_wait_count

bigint

Eine Teilmenge von page_latch_wait_count, die nur die B-Baumstrukturseiten der oberen Ebene umfasst. Für einen Heap lautet der Wert immer 0.

tree_page_latch_wait_in_ms

bigint

Eine Teilmenge von page_latch_wait_in_ms, die nur die B-Baumstrukturseiten der unteren Ebene umfasst. Für einen Heap lautet der Wert immer 0.

tree_page_io_latch_wait_count

bigint

Eine Teilmenge von page_io_latch_wait_count, die nur die B-Baumstrukturseiten der oberen Ebene umfasst. Für einen Heap lautet der Wert immer 0.

tree_page_io_latch_wait_in_ms

bigint

Eine Teilmenge von page_io_latch_wait_in_ms, die nur die B-Baumstrukturseiten der oberen Ebene umfasst. Für einen Heap lautet der Wert immer 0.

page_compression_attempt_count

bigint

Die Anzahl der Seiten, die für Komprimierung auf PAGE-Ebene für bestimmte Partitionen einer Tabelle, eines Index oder einer indizierten Sicht bewertet wurden. Dies schließt Seiten ein, die nicht komprimiert wurden, da beträchtliche Einsparungen nicht erreicht werden konnten.

page_compression_success_count

bigint

Die Anzahl der Datenseiten, die mithilfe von PAGE-Komprimierung für bestimmte Partitionen einer Tabelle, eines Indexes oder einer indizierten Sicht komprimiert wurden.

Hinweise

Dieses dynamische Verwaltungsobjekt akzeptiert keine abhängigen Parameter von CROSS APPLY und OUTER APPLY.

Mithilfe von sys.dm_db_index_operational_stats können Sie nachverfolgen, wie lange Benutzer warten müssen, um eine Tabelle, einen Index oder eine Partition zu lesen, in eine Tabelle, einen Index oder eine Partition zu schreiben und die Tabellen oder Indizes mit hohen E/A-Aktivitäten oder Hotspots zu identifizieren.

Mithilfe der folgenden Spalten können Sie Konfliktbereiche erkennen.

Verwenden Sie die folgenden Spalten, um gebräuchliche Zugriffsmuster für die Tabellen- oder Indexpartition zu analysieren:

  • leaf_insert_count

  • leaf_delete_count

  • leaf_update_count

  • leaf_ghost_count

  • range_scan_count

  • singleton_lookup_count

Verwenden Sie die folgenden Spalten, um Latch- und Sperrkonflikte zu identifizieren:

  • page_latch_wait_count und page_latch_wait_in_ms

    Diese Spalten geben an, ob ein Latchkonflikt im Index oder Heap vorliegt, und zeigen die Bedeutung des Konflikts an.

  • row_lock_count und page_lock_count

    Diese Spalten geben die Häufigkeit an, mit der Database Engine (Datenbankmodul) versucht hat, Zeilen- und Seitensperren abzurufen.

  • row_lock_wait_in_ms und page_lock_wait_in_ms

    Diese Spalten geben an, ob ein Sperrkonflikt im Index oder Heap vorliegt, und zeigen die Bedeutung des Konflikts an.

So analysieren Sie Statistiken physischer E/A-Vorgänge in einer Index- oder Heappartition

  • page_io_latch_wait_count und page_io_latch_wait_in_ms

    Diese Spalten geben an, ob physische E/A-Vorgänge zum Verschieben der Index- oder Heapseiten in den Arbeitsspeicher ausgestellt wurden, und zeigen die Anzahl der ausgestellten E/A-Vorgänge an.

Hinweise zu Spalten

Die Werte in lob_orphan_create_count und lob_orphan_insert_count sollten immer gleich sein.

Der Wert in den Spalten lob_fetch_in_pages und lob_fetch_in_bytes kann bei nicht gruppierten Indizes, die mindestens eine LOB-Spalte als eingeschlossene Spalte enthalten, größer als 0 sein. Weitere Informationen finden Sie unter Index mit eingeschlossenen Spalten. Entsprechend kann der Wert in den Spalten row_overflow_fetch_in_pages und row_overflow_fetch_in_bytes bei nicht gruppierten Indizes größer als 0 sein, wenn der Index Spalten enthält, die durch Ausführen eines Pushs außerhalb von Zeilen verschoben werden können. Weitere Informationen finden Sie unter Zeilenüberlauf bei Daten über 8 KB.

Zurücksetzen der Leistungsindikatoren im Metadatencache

Die von sys.dm_db_index_operational_stats zurückgegebenen Daten sind nur so lange vorhanden, wie das Metadaten-Cacheobjekt, das den Heap oder Index darstellt, verfügbar ist. Diese Daten sind weder permanent noch im Hinblick auf Transaktionen konsistent. Sie können somit diese Leistungsindikatoren nicht verwenden, um zu ermitteln, ob und wann ein Index zuletzt verwendet wurde. Informationen hierzu finden Sie unter sys.dm_db_index_usage_stats (Transact-SQL).

Die Daten für die einzelnen Spalten werden auf 0 gesetzt, wenn die Metadaten für den Heap oder Index in den Metadatencache verschoben und Statistiken gesammelt werden, bis das Cacheobjekt aus dem Metadatencache entfernt wird. Deshalb befinden sich die Metadaten eines aktiven Heaps oder Indexes wahrscheinlich immer im Cache; die Gesamtzahlen können die Aktivität seit dem letzten Starten der SQL Server-Instanz widerspiegeln. Die Metadaten für einen weniger aktiven Heap oder Index werden, abhängig von der Verwendung, in und aus dem Cache verschoben. Folglich ist es möglich, dass Werte zur Verfügung stehen oder auch nicht. Durch das Löschen eines Indexes werden die entsprechenden Statistiken aus dem Arbeitsspeicher entfernt und nicht mehr von der Funktion gemeldet. Sonstige indexbezogene DDL-Vorgänge können dazu führen, dass der Wert der Statistiken auf 0 zurückgesetzt wird.

Verwenden von Systemfunktionen zum Angeben von Parameterwerten

Sie können die Transact-SQL-Funktionen DB_ID und OBJECT_ID verwenden, um einen Wert für die Parameter database_id und object_id anzugeben. Das Übergeben von Werten, die für diese Funktionen nicht gültig sind, kann jedoch zu unerwarteten Ergebnissen führen. Stellen Sie stets sicher, dass bei der Verwendung von DB_ID oder OBJECT_ID eine gültige ID zurückgegeben wird. Weitere Informationen finden Sie im Abschnitt mit Hinweisen unter sys.dm_db_index_physical_stats (Transact-SQL).

Berechtigungen

Folgende Berechtigungen sind erforderlich:

  • CONTROL-Berechtigung für das angegebene Objekt innerhalb der Datenbank

  • VIEW DATABASE STATE-Berechtigung für die Rückgabe von Informationen zu allen Objekten in der angegebenen Datenbank mithilfe des Objektplatzhalters @object_id = NULL

  • VIEW SERVER STATE-Berechtigung zur Rückgabe von Informationen zu allen Datenbanken mithilfe des Datenbankplatzhalters @database_id = NULL

Wenn die VIEW DATABASE STATE-Berechtigung erteilt wurde, ist die Rückgabe für alle Objekte in der Datenbank zulässig, unabhängig davon, ob CONTROL-Berechtigungen für bestimmte Objekte verweigert wurden.

Nach dem Verweigern der VIEW DATABASE STATE-Berechtigung können keine Objekte in der Datenbank zurückgegeben werden, unabhängig von möglicherweise erteilten CONTROL-Berechtigungen für bestimmte Objekte. Wenn der Datenbank-Platzhalter @database_id=NULL angegeben wird, wird zudem die Datenbank ausgelassen.

Weitere Informationen finden Sie unter Dynamische Verwaltungssichten und Funktionen (Transact-SQL).

Beispiele

A. Zurückgeben von Informationen für eine angegebene Tabelle

Im folgenden Beispiel werden Informationen für alle Indizes und Partitionen der Person.Address-Tabelle in der AdventureWorks2008R2-Datenbank zurückgegeben. Für die Ausführung dieser Abfrage wird zumindest die CONTROL-Berechtigung für die Person.Address-Tabelle benötigt.

Wichtiger HinweisWichtig

Wenn Sie die Transact-SQL-Funktionen DB_ID und OBJECT_ID zur Rückgabe eines Parameterwerts verwenden, sollten Sie sicherstellen, dass eine gültige ID zurückgegeben wird. Wenn der Datenbank- oder Objektname nicht gefunden werden kann, wenn sie z. B. nicht vorhanden oder fehlerhaft geschrieben sind, geben beide Funktionen NULL zurück. Die sys.dm_db_index_operational_stats-Funktion interpretiert NULL als Platzhalterwert, der alle Datenbanken oder alle Objekte angibt. Da es sich hierbei um einen unbeabsichtigten Vorgang handeln kann, soll anhand der Beispiele in diesem Abschnitt die sichere Lösung veranschaulicht werden, um Datenbank- und Objekt-IDs zu ermitteln.

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');
IF @db_id IS NULL 
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

B. Zurückgeben von Informationen für alle Tabellen und Indizes

Im folgenden Beispiel werden Informationen für alle Tabellen und Indizes in der SQL Server-Instanz zurückgegeben. Für die Ausführung dieser Abfrage ist die VIEW SERVER STATE-Berechtigung erforderlich.

SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO