sys.dm_db_index_physical_stats (Transact-SQL)

Artikel
03/03/2023
23 Minuten Lesedauer

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Gibt Größen- und Fragmentierungsinformationen für die Daten und Indizes der angegebenen Tabelle oder Sicht in SQL Server zurück. Bei einem Index wird eine Zeile für jede B-Strukturebene in den einzelnen Partitionen zurückgegeben. Bei einem Heap wird eine Zeile für die IN_ROW_DATA-Zuordnungseinheit jeder Partition zurückgegeben. Bei LOB-Daten (Large Object) wird eine Zeile für die LOB_DATA-Zuordnungseinheit jeder Partition zurückgegeben. Falls Zeilenüberlaufdaten in der Tabelle vorhanden sind, wird eine Zeile für die ROW_OVERFLOW_DATA-Zuordnungseinheit in jeder Partition zurückgegeben. Gibt keine Informationen zu speicheroptimierten xVelocity-ColumnStore-Indizes zurück.

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 Design von SQL Server-Indizes.

Wenn Sie sys.dm_db_index_physical_stats auf einer Serverinstanz abfragen, die ein Always On lesbares sekundäres Replikat hostet, tritt möglicherweise ein Problem mit der REDO-Blockierung auf. Dies kommt daher, dass diese dynamische Verwaltungssicht eine IS-Sperre für die angegebene Benutzertabelle oder Sicht erhält, die Anforderungen von einem REDO-Thread für eine X-Sperre dieser Benutzertabelle oder Sicht blockieren kann.

sys.dm_db_index_physical_stats gibt keine Informationen zu speicheroptimierten Indizes zurück. Informationen zur speicheroptimierten Indexverwendung finden Sie unter sys.dm_db_xtp_index_stats (Transact-SQL).

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

database_id | NULL | 0 | STANDARD
Die ID der Datenbank. database_id ist 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, um Informationen für alle Datenbanken in der Instanz von SQL Server 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 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 oder höher sein.

object_id | NULL | 0 | STANDARD
Die Objekt-ID der Tabelle oder Sicht, auf der sich der Index befindet. object_id ist 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. Ab SQL Server 2016 (13.x) enthalten gültige Eingaben auch den Namen der Service Broker-Warteschlange oder den Namen der internen Warteschlange. Wenn Standardparameter angewendet werden (d. h. alle Objekte, alle Indizes usw.), werden Fragmentierungsinformationen für alle Warteschlangen im Resultset eingeschlossen.

Geben Sie NULL an, wenn 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 | STANDARD
Die ID des Indexes. index_id ist int. Gültige Eingaben sind die ID-Nummer eines Indexes, 0, wenn object_id ein Heap ist, NULL, -1 oder DEFAULT. Der Standard ist -1. NULL, -1 und DEFAULT sind in diesem Kontext gleichwertige Werte.

Geben Sie NULL an, wenn 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 | STANDARD
Die Partitionsnummer im Objekt. partition_number ist int. Gültige Eingaben sind die partion_number eines Indexes oder Heaps, 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 Partitionen des besitzenden Objekts zurückgegeben werden sollen.

partition_number ist 1-basiert. Für einen nicht partitionierten Index oder Heap ist partition_number auf 1 festgelegt.

Modus | NULL | STANDARD
Der Name des Modus. der Modus gibt die Scanebene an, die zum Abrufen von Statistiken verwendet wird. mode ist sysname. Gültige Eingaben sind DEFAULT, NULL, LIMITED, SAMPLED oder DETAILED. Der Standardwert (NULL) ist LIMITED.

Zurückgegebene Tabelle

Spaltenname	Datentyp	BESCHREIBUNG
database_id	smallint	Datenbank-ID der Tabelle oder Sicht.
object_id	int	Objekt-ID der Tabelle oder Sicht mit dem Index.
index_id	int	Index-ID eines Indexes. 0 = Heap.
partition_number	int	1-basierte Partitionsnummer im besitzenden Objekt; eine Tabelle, eine Sicht oder ein Index. 1 = Nicht partitionierter Index oder Heap.
index_type_desc	nvarchar(60)	Beschreibung des Indextyps: HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX ERWEITERTER INDEX XML INDEX COLUMNSTORE-ZUORDNUNGSINDEX (intern) COLUMNSTORE DELETEBUFFER INDEX (intern) COLUMNSTORE DELETEBITMAP INDEX (intern)
hobt_id	bigint	Heap- oder B-Tree-ID des Indexes oder der Partition. Neben der Rückgabe der hobt_id von benutzerdefinierten Indizes gibt dies auch die hobt_id der internen Columnstore-Indizes zurück.
alloc_unit_type_desc	nvarchar(60)	Beschreibung des Typs der Zuordnungseinheit: IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA Die LOB_DATA-Zuordnungseinheit enthält die 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). Die ROW_OVERFLOW_DATA-Zuordnungseinheit enthält die Daten, die in Spalten des Typs varchar(n), nvarchar(n), varbinary(n) und sql_variant gespeichert sind, die aus der Zeile verschoben wurden.
index_depth	tinyint	Anzahl von Indexebenen. 1 = Heap oder LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheit.
index_level	tinyint	Aktuelle Ebene des Indexes. 0 für Indexblattebene, Heaps und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. Werte größer 0 für Nicht-Indexblattebenen. index_level ist auf der Stammebene eines Indexes die höchste. Die nicht verleerten Ebenen von Indizes werden nur verarbeitet, wenn modus = DETAILED ist.
avg_fragmentation_in_percent	float	Die logische Fragmentierung für Indizes oder die Blockfragmentierung für Heaps in der IN_ROW_DATA-Zuordnungseinheit. Der Wert wird als Prozentsatz gemessen und berücksichtigt mehrere Dateien. Definitionen für die logische Fragmentierung und die Blockfragmentierung finden Sie unter den Hinweisen. 0 für LOB_DATA- und ROW_OVERFLOW_DATA-Zuordnungseinheiten. NULL für Heaps im Modus = SAMPLED.
fragment_count	bigint	Anzahl von Fragmenten auf der Blattebene einer IN_ROW_DATA-Zuordnungseinheit. Weitere Informationen zu Fragmenten finden Sie unter den Hinweisen. NULL für Nichtblattebenen eines Indexes und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. NULL für Heaps im Modus = SAMPLED.
avg_fragment_size_in_pages	float	Durchschnittliche Anzahl von Seiten in einem Fragment auf der Blattebene einer IN_ROW_DATA-Zuordnungseinheit. NULL für Nichtblattebenen eines Indexes und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. NULL für Heaps im Modus = SAMPLED.
page_count	bigint	Gesamtanzahl von Index- oder Datenseiten. Bei einem Index die Gesamtanzahl der Indexseiten auf der aktuellen Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf die Gesamtanzahl von Datenseiten in der IN_ROW_DATA-Zuordnungseinheit. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf die Gesamtanzahl von Seiten in der Zuordnungseinheit.
avg_page_space_used_in_percent	float	Durchschnittlicher Prozentsatz des auf allen Seiten verwendeten verfügbaren Datenspeicherplatzes. Für einen Index gilt der Mittelwert für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf den Durchschnittswert aller Datenseiten in der IN_ROW_DATA-Zuordnungseinheit. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf den Durchschnittswert aller Seiten in der Zuordnungseinheit. NULL bei Modus = LIMITED.
record_count	bigint	Gesamtanzahl von Datensätzen. Bei einem Index gilt die Gesamtzahl der Datensätze für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf die Gesamtanzahl von Datensätzen in der IN_ROW_DATA-Zuordnungseinheit. Hinweis: Bei einem Heap stimmt die Anzahl der von dieser Funktion zurückgegebenen Datensätze möglicherweise nicht mit der Anzahl der Zeilen überein, die durch Ausführen eines SELECT COUNT()-Werts für den Heap zurückgegeben werden. Das liegt daran, dass eine Zeile möglicherweise mehrere Datensätze enthält. So kann in bestimmten Updatesituationen eine einzelne Heapzeile möglicherweise über einen Weiterleitungsdatensatz und einen weitergeleiteten Datensatz als Ergebnis des Updates verfügen. Außerdem werden die meisten großen LOB-Zeilen im LOB_DATA-Speicher in mehrere Datensätze geteilt. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf die Gesamtanzahl von Datensätzen in der gesamten Zuordnungseinheit. NULL bei Modus* = LIMITED.
ghost_record_count	bigint	Anzahl von inaktiven Datensätzen, die durch den Cleanuptask für inaktive Datensätze in der Zuordnungseinheit entfernt werden können. 0 für Nichtblattebenen eines Indexes in der IN_ROW_DATA-Zuordnungseinheit. NULL bei Modus = LIMITED.
version_ghost_record_count	bigint	Anzahl inaktiver Datensätze, die von einer ausstehenden Momentaufnahme-Isolationstransaktion in einer Zuordnungseinheit beibehalten werden. 0 für Nichtblattebenen eines Indexes in der IN_ROW_DATA-Zuordnungseinheit. NULL bei Modus = LIMITED.
min_record_size_in_bytes	int	Minimale Datensatzgröße in Bytes. Für einen Index gilt die minimale Datensatzgröße für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf die minimale Datensatzgröße in der IN_ROW_DATA-Zuordnungseinheit. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf die minimale Datensatzgröße in der gesamten Zuordnungseinheit. NULL bei Modus = LIMITED.
max_record_size_in_bytes	int	Maximale Datensatzgröße in Bytes. Für einen Index gilt die maximale Datensatzgröße für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf die maximale Datensatzgröße in der IN_ROW_DATA-Zuordnungseinheit. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf die maximale Datensatzgröße in der gesamten Zuordnungseinheit. NULL bei Modus = LIMITED.
avg_record_size_in_bytes	float	Durchschnittliche Datensatzgröße in Bytes. Bei einem Index gilt die durchschnittliche Datensatzgröße für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap auf die durchschnittliche Datensatzgröße in der IN_ROW_DATA-Zuordnungseinheit. Bei LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten auf die durchschnittliche Datensatzgröße in der gesamten Zuordnungseinheit. NULL bei Modus = LIMITED.
forwarded_record_count	bigint	Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger auf einen anderen Datenspeicherort besitzen. (Dieser Status tritt während eines Updates auf, wenn nicht genügend Speicherplatz vorhanden ist, um die neue Zeile am ursprünglichen Speicherort zu speichern.) NULL für eine beliebige Zuordnungseinheit außer IN_ROW_DATA-Zuordnungseinheiten für einen Heap. NULL für Heaps, wenn modus = LIMITED.
compressed_page_count	bigint	Die Anzahl der komprimierten Seiten. Bei Heaps sind neu zugeordnete Seiten nicht mit PAGE seitenkomprimiert. Ein Heap wird nur unter zwei besonderen Bedingungen PAGE-komprimiert: wenn Massendaten importiert werden oder wenn ein Heap neu erstellt wird. Typische DML-Vorgänge, die Seitenzuordnungen hervorrufen, werden nicht PAGE-komprimiert. Erstellen Sie einen Heap neu, wenn der compressed_page_count-Wert den gewünschten Schwellenwert überschreitet. Für Tabellen mit gruppiertem Index gibt der compressed_page_count-Wert die Wirksamkeit der PAGE-Komprimierung an.
hobt_id	BIGINT	Nur für Columnstore-Indizes ist dies die ID für ein Rowset, das interne Columnstore-Daten für eine Partition nachverfolgt. Die Rowsets werden als Datenheaps oder B-Strukturen gespeichert. Sie weisen dieselbe Index-ID wie der übergeordnete Columnstore-Index auf. Weitere Informationen finden Sie unter sys.internal_partitions (Transact-SQL). NULL, wenn Gilt für: SQL Server 2016 und höher, Azure SQL Database, Azure SQL Managed Instance
columnstore_delete_buffer_state	TINYINT	0 = NOT_APPLICABLE 1 = OPEN 2 = ENTLEERUNG 3 = LEERUNG 4 = AUßERBETRIEBNAHME 5 = BEREIT Gilt für: SQL Server 2016 und höher, Azure SQL Database, Azure SQL Managed Instance
columnstore_delete_buffer_state_desc		NICHT GÜLTIG: Der übergeordnete Index ist kein Columnstore-Index. OPEN: Löschende und Scanner verwenden dies. ENTWÄSSERUNG: Entleerer werden entleert, aber Scanner verwenden sie weiterhin. LEERUNG: Der Puffer wird geschlossen, und zeilen im Puffer werden in die Löschbitteppy geschrieben. EINSTELLUNG: Zeilen im geschlossenen Löschpuffer wurden in die Löschbitteppchen geschrieben, aber der Puffer wurde nicht abgeschnitten, da er von Scannern weiterhin verwendet wird. Neue Scanner müssen den auslaufenden Puffer nicht verwenden, da der geöffnete Puffer ausreichend ist. READY: Dieser Löschpuffer ist einsatzbereit. Gilt für: SQL Server 2016 und höher, Azure SQL Database, Azure SQL Managed Instance
version_record_count	bigint	Dies ist die Anzahl der Zeilenversionsdatensätze, die in diesem Index verwaltet werden. Diese Zeilenversionen werden von der Funktion "Beschleunigte Datenbankwiederherstellung" verwaltet. Gilt für: SQL Server 2019 (15.x), Azure SQL-Datenbank
inrow_version_record_count	bigint	Anzahl der ADR-Versionsdatensätze, die in der Datenzeile für einen schnellen Abruf aufbewahrt werden. Gilt für: SQL Server 2019 (15.x), Azure SQL Databasee
inrow_diff_version_record_count	bigint	Anzahl der ADR-Versionsdatensätze, die in Form von Unterschieden von der Basisversion aufbewahrt werden. Gilt für: SQL Server 2019 (15.x), Azure SQL-Datenbank
total_inrow_version_payload_size_in_bytes	bigint	Gesamtgröße in Byte der Inrowversionsdatensätze für diesen Index. Gilt für: SQL Server 2019 (15.x), Azure SQL-Datenbank
offrow_regular_version_record_count	bigint	Anzahl der Versionsdatensätze, die außerhalb der ursprünglichen Datenzeile gespeichert werden. Gilt für: SQL Server 2019 (15.x), Azure SQL-Datenbank
offrow_long_term_version_record_count	bigint	Anzahl der Versionsdatensätze, die als langfristig betrachtet werden. Gilt für: SQL Server 2019 (15.x), Azure SQL-Datenbank

Hinweis

Bemerkungen

Die dynamische Verwaltungsfunktion sys.dm_db_index_physical_stats ersetzt die DBCC SHOWCONTIG-Anweisung.

Scanmodi

Der Modus, in dem die Funktion ausgeführt wird, bestimmt die Scanebene, die zum Abrufen der statistischen Daten von der Funktion verwendet wird. der Modus wird als LIMITED, SAMPLED oder DETAILED angegeben. Die Funktion durchsucht die Seitenketten nach den Zuordnungseinheiten, aus denen die angegebenen Partitionen der Tabelle oder des Indexes bestehen. sys.dm_db_index_physical_stats erfordert nur eine Intent-Shared (IS)-Tabellensperre, unabhängig vom Modus, in dem sie ausgeführt wird.

Der Modus LIMITED ist am schnellsten und durchsucht am wenigsten Seiten. Bei einem Index werden nur die Seiten der übergeordneten B-Strukturebene (d. h. die Seiten oberhalb der Blattebene) gescannt. Bei einem Heap werden nur die zugehörigen PFS- und IAM-Seiten untersucht; die Datenseiten des Heaps werden im Modus LIMITED gescannt.

Im EINGESCHRÄNKTen Modus ist compressed_page_count NULL, da die Datenbank-Engine nur seitenfremde Seiten der B-Struktur und der IAM- und PFS-Seiten des Heaps scannt. Verwenden Sie den SAMPLED-Modus, um einen geschätzten Wert für compressed_page_count abzurufen, und verwenden Sie den DETAILLIERTEn Modus, um den tatsächlichen Wert für compressed_page_count abzurufen. Der Modus SAMPLED gibt Statistiken basierend auf einer Stichprobe von 1 % aller Seiten im Index oder Heap zurück. Ergebnisse im SAMPLED-Modus sollten als ungefähre Werte angesehen werden. Falls der Index oder Heap weniger als 10.000 Seiten aufweist, wird anstelle des Modus SAMPLED der Modus DETAILED verwendet.

Der Modus DETAILED durchsucht alle Seiten und gibt alle Statistiken zurück.

Die Geschwindigkeit der Modi nimmt von LIMITED zu DETAILED schrittweise ab, weil im jeweils nächsten Modus mehr Arbeitsschritte ausgeführt werden. Verwenden Sie den Modus LIMITED, wenn Sie die Größe oder die Fragmentierungsebene einer Tabelle oder eines Indexes schnell messen möchten. Dies ist der schnellste Modus und gibt nicht für jede Nichtblattebene in der IN_ROW_DATA-Zuordnungseinheit des Indexes eine Zeile zurück.

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. Falls z. B. die Datenbank oder der Objektname nicht gefunden wird, weil er nicht vorhanden oder falsch geschrieben ist, geben beide Funktionen NULL zurück. Die sys.dm_db_index_physical_stats-Funktion interpretiert NULL als Platzhalterwert, mit dem alle Datenbanken oder alle Objekte angegeben werden.

Darüber hinaus wird die OBJECT_ID-Funktion vor dem Aufruf der sys.dm_db_index_physical_stats-Funktion verarbeitet und daher im Kontext der aktuellen Datenbank und nicht der in database_id angegebenen Datenbank ausgewertet. Dadurch gibt die OBJECT_ID-Funktion unter Umständen einen NULL-Wert zurück, oder es wird eine Fehlermeldung zurückgegeben, falls der Objektname sowohl im aktuellen Datenbankkontext als auch in der angegebenen Datenbank vorhanden ist. In den folgenden Beispielen werden diese nicht beabsichtigten Ergebnisse veranschaulicht.

USE master;  
GO  
-- In this example, OBJECT_ID is evaluated in the context of the master database.   
-- Because Person.Address does not exist in master, the function returns NULL.  
-- When NULL is specified as an object_id, all objects in the database are returned.  
-- The same results are returned when an object that is not valid is specified.  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- This example demonstrates the results of specifying a valid object name  
-- that exists in both the current database context and  
-- in the database specified in the database_id parameter of the   
-- sys.dm_db_index_physical_stats function.  
-- An error is returned because the ID value returned by OBJECT_ID does not  
-- match the ID value of the object in the specified database.  
CREATE DATABASE Test;  
GO  
USE Test;  
GO  
CREATE SCHEMA Person;  
GO  
CREATE Table Person.Address(c1 int);  
GO  
USE AdventureWorks2012;  
GO  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- Clean up temporary database.  
DROP DATABASE Test;  
GO

Bewährte Methode

Stellen Sie stets sicher, dass bei der Verwendung von DB_ID oder OBJECT_ID eine gültige ID zurückgegeben wird. Wenn Sie z. B. OBJECT_ID verwenden, geben Sie einen dreiteiligen Namen wie OBJECT_ID(N'AdventureWorks2012.Person.Address')an, oder testen Sie den von den Funktionen zurückgegebenen Wert, bevor Sie sie in der sys.dm_db_index_physical_stats-Funktion verwenden. In den nachstehenden Beispielen A und B wird eine sichere Möglichkeit zur Angabe von Datenbank- und Objekt-IDs aufgezeigt.

Erkennen der Fragmentierung

Die Fragmentierung wird durch Datenänderungen (mithilfe der Anweisungen INSERT, UPDATE oder DELETE) in Bezug auf die Tabelle und dadurch an den für diese Tabelle definierten Indizes hervorgerufen. Da diese Änderungen normalerweise nicht gleichmäßig über alle Zeilen der Tabelle und Indizes verteilt vorgenommen werden, kann sich mit der Zeit der Füllgrad jeder Seite ändern. Diese Tabellenfragmentierung kann bei Abfragen, bei denen die Indizes einer Tabelle teilweise oder ganz gescannt werden, zu zusätzlichen Seitenlesevorgängen führen. Dies behindert das parallele Scannen von Daten.

Die Fragmentierungsebene eines Indexes oder Heaps wird in der avg_fragmentation_in_percent-Spalte angezeigt. Bei Heaps stellt dieser Wert die Blockfragmentierung des Heaps dar. Bei Indizes stellt dieser Wert die logische Fragmentierung des Indexes dar. Im Gegensatz zu DBCC SHOWCONTIG wird bei den Algorithmen zur Fragmentierungsberechnung in beiden Fällen Speicherplatz berücksichtigt, der sich über mehrere Dateien erstreckt; folglich sind diese Algorithmen genauer.

Logische Fragmentierung

Dies ist der Prozentsatz der Seiten, die auf den Blattseiten eines Indexes nicht ordnungsgemäß sortiert sind. Eine nicht ordnungsgemäß einsortierte Seite ist eine Seite, für die die nächste physische Seite, die dem Index zugeordnet ist, nicht die Seite ist, auf die der Zeiger für die nächste Seite auf der aktuellen Blattseite zeigt.

Blockfragmentierung

Dies ist der Prozentsatz der Blöcke, die auf den Blattseiten eines Heaps nicht ordnungsgemäß sortiert sind. Ein nicht ordnungsgemäß sortierter Block ist ein Block, für den der Block, der die aktuelle Seite eines Heaps enthält, physisch nicht der nächste Block nach dem Block ist, der die vorherige Seite enthält.

Der Wert für avg_fragmentation_in_percent sollte möglichst nahe bei null liegen, um eine optimale Leistung sicherzustellen. Werte zwischen 0 und 10 % sind jedoch akzeptabel. Um diese Werte zu verringern, können alle Methoden zur Reduzierung der Fragmentierung verwendet werden, wie z. B. Neuerstellung oder Neuorganisierung. Weitere Informationen zum Analysieren des Fragmentierungsgrads in einem Index finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

Reduzieren der Fragmentierung in einem Index

Wenn ein Index derart fragmentiert ist, dass die Fragmentierung die Abfrageleistung beeinträchtigt, gibt es drei Möglichkeiten, um die Fragmentierung zu reduzieren:

Löschen Sie den gruppierten Index, und erstellen Sie ihn neu.

Durch das erneute Erstellen eines gruppierten Indexes werden die Daten neu verteilt, was zu vollen Datenseiten führt. Der Füllungsgrad kann über die Option FILLFACTOR in CREATE INDEX konfiguriert werden. Diese Methode hat den Nachteil, dass der Index während des Löschens und Neuerstellens offline und der Vorgang atomar ist. Wenn die Indexerstellung unterbrochen wird, wird der Index nicht neu erstellt. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).
Verwenden Sie ALTER INDEX REORGANIZE (der Ersatz für DBCC INDEXDEFRAG), um die Indexseiten auf Blattebene in einer logischen Reihenfolge neu anzuordnen. Da es sich hierbei um einen Onlinevorgang handelt, steht der Index während der Ausführung der Anweisung zur Verfügung. Der Vorgang kann auch ohne Verlust bereits abgeschlossener Arbeitsschritte unterbrochen werden. Diese Methode hat den Nachteil, dass die Daten nicht so gut neu organisiert werden wie bei einem Indexneuerstellungsvorgang, und außerdem werden die Statistiken nicht aktualisiert.
Verwenden Sie ALTER INDEX REBUILD (der Ersatz für DBCC DBREINDEX), um den Index im Online- oder Offlinemodus neu zu erstellen. Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Die Fragmentierung alleine ist kein ausreichender Grund, um einen Index neu zu organisieren oder neu zu erstellen. Durch die Fragmentierung wird in erster Linie der Read-Ahead-Durchsatz von Seiten während Indexscans reduziert. Dies verursacht langsamere Antwortzeiten. Falls die Abfragearbeitsauslastung für eine fragmentierte Tabelle oder einen fragmentierten Index keine Scans enthält, weil es sich bei der Arbeitsauslastung in erster Linie um Singleton-Suchvorgänge handelt, hat das Beseitigen der Fragmentierung möglicherweise keine Auswirkungen.

Hinweis

Das Ausführen von DBCC SHRINKFILE oder DBCC SHRINKDATABASE kann zur Fragmentierung führen, falls ein Index während des Verkleinerungsvorgangs teilweise oder vollständig verschoben wird. Wenn ein Verkleinerungsvorgang ausgeführt werden muss, sollten Sie diesen deshalb vor dem Beseitigen der Fragmentierung vornehmen.

Reduzieren der Fragmentierung in einem Heap

Um die Blockfragmentierung eines Heaps zu reduzieren, erstellen Sie einen gruppierten Index für die Tabelle, und löschen Sie dann den Index. Dadurch werden die Daten neu verteilt, während der gruppierte Index erstellt wird. Dabei wird ein möglichst optimaler Zustand in Bezug auf die Verteilung des freien Speicherplatzes in der Datenbank angestrebt. Wenn der gruppierte Index dann gelöscht wird, um den Heap neu zu erstellen, werden die Daten nicht verschoben und verbleiben an ihrer optimalen Position. Weitere Informationen zum Ausführen dieser Vorgänge finden Sie unter CREATE INDEX und DROP INDEX.

Achtung

Beim Erstellen und Löschen eines gruppierten Indexes für eine Tabelle werden alle nicht gruppierten Indizes für diese Tabelle zweimal neu erstellt.

Komprimieren von LOB-Daten

Standardmäßig komprimiert die ALTER INDEX REORGANIZE-Anweisung Seiten, die LOB-Daten (Large Object) enthalten. Die Zuordnung von LOB-Seiten wird nicht aufgehoben, wenn sie leer sind. Deshalb kann durch das Komprimieren dieser Daten der Speicherplatz optimiert werden, falls viele LOB-Daten gelöscht wurden oder eine LOB-Spalte entfernt wird.

Durch das Neuorganisieren eines angegebenen gruppierten Indexes werden alle im gruppierten Index enthaltenen LOB-Spalten komprimiert. Durch das Neuorganisieren eines nicht gruppierten Indexes werden alle LOB-Spalten komprimiert, die (eingeschlossene) Nichtschlüsselspalten im Index sind. Wenn ALL in der Anweisung angegeben wird, werden alle Indizes, die der angegebenen Tabelle oder Sicht zugeordnet sind, neu organisiert. Darüber hinaus werden alle LOB-Spalten, die dem gruppierten Index, der zugrunde liegenden Tabelle oder dem nicht gruppierten Index mit eingeschlossenen Spalten zugeordnet sind, komprimiert.

Auswerten der Speicherplatzverwendung

Die avg_page_space_used_in_percent-Spalte gibt den Seitenfüllgrad an. Für eine optimale Speicherplatzverwendung sollte dieser Wert für einen Index ohne viele zufällige Einfügungen nahe bei 100 % liegen. Ein Index mit zahlreichen zufälligen Einfügungen und sehr vollen Seiten verfügt jedoch über eine höhere Anzahl von Seitenteilungen. Dadurch entsteht mehr Fragmentierung. Deshalb sollte dieser Wert unter 100 % liegen, um Seitenteilungen zu reduzieren. Durch die Neuerstellung eines Indexes mit angegebener Option FILLFACTOR kann der Seitenfüllgrad an das Abfragemuster für den Index angepasst werden. Weitere Informationen zum Füllfaktor finden Sie unter Angeben des Füllfaktors für einen Index. Darüber hinaus komprimiert ALTER INDEX REORGANIZE einen Index, indem versucht wird, Seiten bis zum zuletzt angegebenen FILLFACTOR-Wert zu füllen. Dadurch erhöht sich der Wert in avg_space_used_in_percent. Beachten Sie, dass der Seitenfüllgrad mit ALTER INDEX REORGANIZE nicht reduziert werden kann. Stattdessen muss eine Indexneuerstellung ausgeführt werden.

Auswerten von Indexfragmenten

Ein Fragment besteht aus aufeinander folgenden Blattseiten in derselben Datei für eine Zuordnungseinheit. Ein Index weist mindestens ein Fragment auf. Die maximale Anzahl von Fragmenten für einen Index entspricht der Anzahl von Seiten auf der Blattebene des Indexes. Größere Fragmente bedeuten, dass weniger Datenträger-E/A-Vorgänge zum Lesen der gleichen Anzahl von Seiten erforderlich sind. Deshalb gilt, je höher der Wert für avg_fragment_size_in_pages, desto besser ist die Leistung des Bereichsscans. Die Werte avg_fragment_size_in_pages und avg_fragmentation_in_percent verhalten sich umgekehrt proportional zueinander. Deshalb sollte durch das Neuerstellen oder Neuorganisieren eines Indexes die Fragmentierung reduziert und die Fragmentgröße erhöht werden.

Einschränkungen

Gibt keine Daten für gruppierte columnstore-Indizes zurück.

Berechtigungen

Folgende Berechtigungen sind erforderlich:

CONTROL-Berechtigung für das angegebene Objekt innerhalb der Datenbank.
DIE BERECHTIGUNG VIEW DATABASE STATE oder VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) zum Zurückgeben von Informationen zu allen Objekten in der angegebenen Datenbank mithilfe des Objektplatzhalters @object_id=NULL.
VIEW SERVER STATE oder VIEW SERVER PERFORMANCE STATE (SQL Server 2022)-Berechtigung zum Zurückgeben 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 außerdem das Datenbankplatzhalterzeichen @database_id=NULL angegeben wird, wird die Datenbank weggelassen.

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

Beispiele

A. Zurückgeben von Informationen zu einer angegebenen Tabelle

Im folgenden Beispiel werden die Größen- und Fragmentierungsstatistiken für alle Indizes und Partitionen der Person.Address-Tabelle zurückgegeben. Als Scanmodus ist 'LIMITED' festgelegt, um eine optimale Leistung sicherzustellen und die zurückgegebenen Statistiken zu begrenzen. Für die Ausführung dieser Abfrage wird zumindest die CONTROL-Berechtigung für die Person.Address-Tabelle benötigt.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');  
END;  
GO

B. Zurückgeben von Informationen zu einem Heap

Im folgenden Beispiel werden alle Statistiken für den Heap dbo.DatabaseLog in der AdventureWorks2019-Datenbank zurückgegeben. Da die Tabelle LOB-Daten enthält, wird eine Zeile für die LOB_DATA-Zuordnungseinheit zurückgegeben. Dies geschieht zusätzlich zu der Zeile, die für IN_ROW_ALLOCATION_UNIT zurückgegeben wird und in der die Datenseiten des Heaps gespeichert sind. Für die Ausführung dieser Abfrage wird zumindest die CONTROL-Berechtigung für die dbo.DatabaseLog-Tabelle benötigt.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.dbo.DatabaseLog');  
IF @object_id IS NULL   
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN;  
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');  
END;  
GO

C. Zurückgeben von Informationen zu allen Datenbanken

Im folgenden Beispiel werden alle Statistiken für alle Tabellen und Indizes innerhalb der Instanz von SQL Server zurückgegeben, indem der Wildcard NULL für alle Parameter angegeben wird. Zum Ausführen dieser Abfrage ist die VIEW SERVER STATE-Berechtigung erforderlich.

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

D: Verwenden von "sys.dm_db_index_physical_stats" in einem Skript, um Indizes neu zu erstellen oder neu zu organisieren

Im folgenden Beispiel werden automatisch alle Partitionen in einer Datenbank neu angeordnet oder neu erstellt, die eine durchschnittliche Fragmentierung von über 10 % aufweisen. Zum Ausführen dieser Abfrage ist die VIEW DATABASE STATE-Berechtigung erforderlich. In diesem Beispiel wird DB_ID als erster Parameter angegeben, ohne einen Datenbanknamen anzugeben. Ein Fehler wird generiert, wenn die aktuelle Datenbank über einen Kompatibilitätsgrad von 80 oder niedriger verfügt. Zum Beheben des Fehlers ersetzen Sie DB_ID() durch einen gültigen Datenbanknamen. Weitere Informationen zu Datenbank-Kompatibilitätsgraden finden Sie unter ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.  
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
  
-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
  
-- Open the cursor.  
OPEN partitions;  
  
-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  
  
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  
  
-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  
  
-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

E. Anzeigen der Anzahl von Seiten mit Seitenkomprimierung mithilfe von "sys.dm_db_index_physical_stats"

Im folgenden Beispiel wird gezeigt, wie die Gesamtanzahl von Seiten angezeigt und den Seiten mit Zeilen- und Seitenkomprimierung gegenüber gestellt wird. Mithilfe dieser Informationen kann ermittelt werden, welche Vorteile diese Komprimierung für einen Index oder eine Tabelle hat.

SELECT o.name,  
    ips.partition_number,  
    ips.index_type_desc,  
    ips.record_count, ips.avg_record_size_in_bytes,  
    ips.min_record_size_in_bytes,  
    ips.max_record_size_in_bytes,  
    ips.page_count, ips.compressed_page_count  
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips  
JOIN sys.objects o on o.object_id = ips.object_id  
ORDER BY record_count DESC;

F. Verwenden von „sys.dm_db_index_physical_stats“ im Modus SAMPLED

Im folgenden Beispiel wird gezeigt, wie vom Modus SAMPLED ein ungefährer Wert zurückgegeben wird, der sich von den Ergebnissen des Modus DETAILED unterscheidet.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
DECLARE @idx int = 0;  
WHILE @idx < 1000000  
BEGIN  
    INSERT INTO t3 (col1, col2)   
    VALUES (@idx,   
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))  
    SET @idx = @idx + 1  
END  
COMMIT;  
GO  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'SAMPLED');  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'DETAILED');

G. Abfragen von Service Broker-Warteschlangen auf Indexfragmentierung


Gilt für: SQL Server 2016 (13.x) bis SQL Server.

Die folgenden Beispiele zeigen, wie Sie Serverbrokerwarteschlangen auf Fragmentierung abfragen.

--Using queue internal table name   
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('sys.queue_messages_549576996'), default, default, default)   
  
--Using queue name directly  
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('ExpenseQueue'), default, default, default)

Weitere Informationen

Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit dem Index (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
Systemsichten (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

Syntax

Argumente

Zurückgegebene Tabelle

Bemerkungen

Scanmodi

Verwenden von Systemfunktionen zum Angeben von Parameterwerten

Bewährte Methode

Erkennen der Fragmentierung

Reduzieren der Fragmentierung in einem Index

Reduzieren der Fragmentierung in einem Heap

Komprimieren von LOB-Daten

Auswerten der Speicherplatzverwendung

Auswerten von Indexfragmenten

Einschränkungen

Berechtigungen

Beispiele

A. Zurückgeben von Informationen zu einer angegebenen Tabelle

B. Zurückgeben von Informationen zu einem Heap

C. Zurückgeben von Informationen zu allen Datenbanken

D: Verwenden von "sys.dm_db_index_physical_stats" in einem Skript, um Indizes neu zu erstellen oder neu zu organisieren

E. Anzeigen der Anzahl von Seiten mit Seitenkomprimierung mithilfe von "sys.dm_db_index_physical_stats"

F. Verwenden von „sys.dm_db_index_physical_stats“ im Modus SAMPLED

G. Abfragen von Service Broker-Warteschlangen auf Indexfragmentierung

Weitere Informationen

Zusätzliche Ressourcen

Zusätzliche Ressourcen