sys.dm_db_index_operational_stats (Transact-SQL)
Gilt für:
SQL Server Azure SQL-Datenbank Azure SQL verwaltete Instanz
Gibt die aktuelle E/A-Aktivität auf niedrigerer Ebene, Sperren, Verriegeln und Zugriffsmethoden für jede Partition einer Tabelle oder eines Indexes in der Datenbank zurück.
Speicheroptimierte Indizes werden in dieser DMV nicht angezeigt.
Hinweis
sys.dm_db_index_operational_stats gibt keine Informationen zu speicheroptimierten Indizes zurück. Informationen zur speicheroptimierten Indexverwendung finden Sie unter sys.dm_db_xtp_index_stats (Transact-SQL).For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).
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 | VORGABE
Die ID der Datenbank. database_id ist klein. Gültige Eingaben sind die ID einer Datenbank, NULL, 0 oder DEFAULT. Der Standardwert ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertig.
Geben Sie NULL an, um Informationen für alle Datenbanken in der Sql Server-Instanz zurückzugeben. Wenn Sie NULL für database_id angeben, müssen Sie auch NULL für object_id, index_id und partition_number angeben.
Die integrierte Funktion DB_ID kann angegeben werden.
object_id | NULL | 0 | VORGABE
Objekt-ID der Tabelle oder Ansicht, auf der sich der Index befindet. object_id ist int.
Gültige Eingaben sind die ID einer Tabelle und Sicht, NULL, 0 oder DEFAULT. Der Standardwert 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 | VORGABE
Die ID des Index. index_id ist int. Gültige Eingaben sind die ID-Nummer eines Indexes, 0, wenn object_id ein Heap, NULL, -1 oder DEFAULT ist. 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 NULL für index_id angeben, müssen Sie auch NULL für partition_number angeben.
partition_number | NULL | 0 | VORGABE
Partitionsnummer im Objekt. partition_number ist int. Gültige Eingaben sind die partion_number eines Indexes oder Heaps, NULL, 0 oder DEFAULT. Der Standardwert 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 basiert auf 1. Ein nicht partitioniertes Index oder Heap hat partition_number auf 1 festgelegt.
Zurückgegebene Tabelle
| Spaltenname | Datentyp | Beschreibung |
|---|---|---|
| database_id | smallint | Datenbank-ID In Azure SQL-Datenbank sind die Werte innerhalb einer einzelnen Datenbank oder eines elastischen Pools eindeutig, aber nicht innerhalb eines logischen Servers. |
| 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. |
| hobt_id | bigint | Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL-Datenbank. ID des Datenhap- oder B-Struktur-Rowsets, das interne Daten für einen Spaltenspeicherindex nachverfolgt. NULL - dies ist kein internes Columnstore-Rowset. Weitere Informationen finden Sie unter sys.internal_partitions (Transact-SQL) |
| leaf_insert_count | bigint | Gesamtzahl der Einfügevorgänge auf Blattebene. |
| leaf_delete_count | bigint | Gesamtzahl der Löschvorgänge auf Blattebene. leaf_delete_count wird nur für gelöschte Datensätze erhöht, die nicht als Geister gekennzeichnet sind. Bei gelöschten Datensätzen, die zuerst gespenstet werden, wird leaf_ghost_count stattdessen erhöht. |
| leaf_update_count | bigint | Gesamtzahl der Updates auf Blattebene. |
| leaf_ghost_count | bigint | Gesamtzahl der Zeilen auf Blattebene, die als gelöscht markiert sind, jedoch noch nicht entfernt wurden. Diese Anzahl enthält keine Datensätze, die sofort gelöscht werden, ohne als Geist gekennzeichnet zu werden. Diese Zeilen werden durch einen Cleanupthread in bestimmten Intervallen entfernt. In diesem Wert sind keine Zeilen enthalten, die aufgrund einer ausstehenden Momentaufnahmeisolationstransaktion beibehalten werden. |
| nonleaf_insert_count | bigint | Gesamtzahl der Einfügevorgänge über der Blattebene. 0 = Heap oder columnstore |
| nonleaf_delete_count | bigint | Gesamtzahl der Löschvorgänge über der Blattebene. 0 = Heap oder columnstore |
| nonleaf_update_count | bigint | Gesamtzahl der Updates über der Blattebene. 0 = Heap oder columnstore |
| 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 oder columnstore |
| leaf_page_merge_count | bigint | Gesamtzahl der Seitenzusammenführungen auf der Blattebene. Immer 0 für den columnstore-Index. |
| nonleaf_page_merge_count | bigint | Gesamtzahl der Seitenzusammenführungen über der Blattebene. 0 = Heap oder columnstore |
| 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 sind. Weitere Informationen finden Sie unter Datentypen (Transact-SQL). |
| 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 Daten, die in Spalten vom Typ varchar(n), nvarchar(n), varbinary(n) und sql_variant gespeichert wurden, die aus der Zeile verschoben wurden. |
| 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 einen Updatevorgang 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. |
| row_lock_count | bigint | Gesamtzahl der angeforderten Zeilensperren. |
| row_lock_wait_count | bigint | Kumulierte Anzahl der Wartezeiten für die Datenbank-Engine auf eine Zeilensperre. |
| row_lock_wait_in_ms | bigint | Die Gesamtzahl der Millisekunden, die die Datenbank-Engine auf eine Zeilensperre gewartet haben. |
| page_lock_count | bigint | Gesamtzahl der angeforderten Seitensperren. |
| page_lock_wait_count | bigint | Kumulierte Anzahl der Wartezeiten des Datenbank-Engine auf eine Seitensperre. |
| page_lock_wait_in_ms | bigint | Die Gesamtzahl der Millisekunden, die die Datenbank-Engine auf eine Seitensperre gewartet haben. |
| index_lock_promotion_attempt_count | bigint | Kumulierte Häufigkeit, mit der die Datenbank-Engine versucht haben, Sperren zu eskalieren. |
| index_lock_promotion_count | bigint | Kumulierte Anzahl der Datenbank-Engine eskalierten Sperren. |
| page_latch_wait_count | bigint | Kumulierte Anzahl der Wartezeiten für die Datenbank-Engine aufgrund eines Verschlusses. |
| page_latch_wait_in_ms | bigint | Die kumulierte Anzahl von Millisekunden, die die Datenbank-Engine gewartet hat, aufgrund von Riegelungskonflikten. |
| page_io_latch_wait_count | bigint | Kumulierte Anzahl der Wartezeiten des Datenbank-Engine auf einer E/A-Seite. |
| page_io_latch_wait_in_ms | bigint | Kumulierte Anzahl von Millisekunden, die die Datenbank-Engine auf einer Seiten-E/A-Riegelung gewartet haben. |
| tree_page_latch_wait_count | bigint | Teilmenge von page_latch_wait_count , die nur die B-Strukturseiten der oberen Ebene enthält. Immer 0 für einen Heap oder einen columnstore-Index. |
| tree_page_latch_wait_in_ms | bigint | Teilmenge von page_latch_wait_in_ms , die nur die B-Strukturseiten der oberen Ebene enthält. Immer 0 für einen Heap oder einen columnstore-Index. |
| tree_page_io_latch_wait_count | bigint | Teilmenge von page_io_latch_wait_count , die nur die B-Strukturseiten der oberen Ebene enthält. Immer 0 für einen Heap oder einen columnstore-Index. |
| tree_page_io_latch_wait_in_ms | bigint | Teilmenge von page_io_latch_wait_in_ms , die nur die B-Strukturseiten der oberen Ebene enthält. Immer 0 für einen Heap oder einen columnstore-Index. |
| 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. Immer 0 für den columnstore-Index. |
| page_compression_success_count | bigint | Die Anzahl der Datenseiten, die mithilfe von PAGE-Komprimierung für bestimmte Partitionen einer Tabelle, eines Index oder einer indizierten Sicht komprimiert wurden. Immer 0 für den columnstore-Index. |
Hinweis
In der SQL Server-Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert SQL Server eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.
Hinweise
Dieses dynamische Verwaltungsobjekt akzeptiert keine korrelierten Parameter von CROSS APPLY und OUTER APPLY.
Sie können sys.dm_db_index_operational_stats verwenden, um die Zeitspanne nachzuverfolgen, die Benutzer warten müssen, um in eine Tabelle, einen Index oder eine Partition zu lesen oder zu schreiben, und die Tabellen oder Indizes zu identifizieren, die auf erhebliche E/A-Aktivitäten oder Hotspots stoßen.
Mithilfe der folgenden Spalten können Sie Konfliktbereiche erkennen.
Verwenden Sie die folgenden Spalten, um ein allgemeines Zugriffsmuster für die Tabelle 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 an, wie oft die Datenbank-Engine versucht haben, 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 von physischen E/A-Dateien auf 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 für nicht gruppierte Indizes größer als Null sein, die eine oder mehrere BRANCHENspalten als eingeschlossene Spalten enthalten. Weitere Informationen finden Sie unter Create Indexes with Included Columns. Ebenso kann der Wert in den Spalten row_overflow_fetch_in_pages und row_overflow_fetch_in_bytes für nicht gruppierte Indizes größer als 0 sein, wenn der Index Spalten enthält, die aus der Zeile verschoben werden können.
Zurücksetzen der Leistungsindikatoren im Metadatencache
Die von sys.dm_db_index_operational_stats zurückgegebenen Daten sind nur vorhanden, solange das Metadatencacheobjekt, das den Heap oder den Index darstellt, verfügbar ist. Diese Daten sind weder persistent 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. Weitere Informationen hierzu finden Sie unter sys.dm_db_index_usage_stats (Transact-SQL).For information about this, see 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. Daher verfügt ein aktiver Heap oder Index wahrscheinlich immer über seine Metadaten im Cache, und die kumulierten Zählungen spiegeln die Aktivität ab, seit die Instanz von SQL Server zuletzt gestartet wurde. 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 "Hinweise" in sys.dm_db_index_physical_stats (Transact-SQL).For more information, see the Remarks section in sys.dm_db_index_physical_stats (Transact-SQL).
Berechtigungen
Folgende Berechtigungen sind erforderlich:
CONTROLBerechtigung für das angegebene Objekt in der DatenbankVIEW DATABASE STATEoderVIEW DATABASE PERFORMANCE STATE(SQL Server 2022)-Berechtigung zum Zurückgeben von Informationen zu allen Objekten innerhalb der angegebenen Datenbank mithilfe des Objekt-Wildcards @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) Berechtigung zum Zurückgeben von Informationen zu allen Datenbanken mithilfe des Datenbank-Wildcards @database_id = NULL
Durch die Gewährung VIEW DATABASE STATE können alle Objekte in der Datenbank zurückgegeben werden, unabhängig von steuerelementberechtigungen, die für bestimmte Objekte verweigert wurden.
Wenn VIEW DATABASE STATE Sie verweigern, dass alle Objekte in der Datenbank zurückgegeben werden, unabhängig von steuerelementberechtigungen, die für bestimmte Objekte erteilt wurden. Wenn der Datenbank-Wildcard @database_id=NULL angegeben wird, wird die Datenbank ebenfalls nicht angegeben.
Weitere Informationen finden Sie unter Dynamische Verwaltungsansichten und -funktionen (Transact-SQL).For more information, see Dynamic Management Views and Functions (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 AdventureWorks2022-Datenbank zurückgegeben. Für die Ausführung dieser Abfrage ist zumindest die CONTROL-Berechtigung in der Person.Address-Tabelle erforderlich.
Wichtig
Wenn Sie die Transact-SQL-Funktionen DB_ID und OBJECT_ID verwenden, um einen Parameterwert zurückzugeben, stellen Sie immer sicher, 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 dies ein versehentlicher Vorgang sein kann, veranschaulichen die Beispiele in diesem Abschnitt, wie Sie auf sichere Weise Datenbank- und Objekt-IDs bestimmen.
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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 innerhalb der SQL Server-Instanz zurückgegeben. Zum Ausführen dieser Abfrage ist die VIEW SERVER STATE-Berechtigung erforderlich.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Weitere Informationen
Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
Indexbezogene dynamische Verwaltungsansichten und -funktionen (Transact-SQL)
Überwachen und Optimieren der Leistung
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter: https://aka.ms/ContentUserFeedback.
Einreichen und Feedback anzeigen für