sys.dm_db_index_physical_stats (Transact-SQL)

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 Ansicht in der SQL Server-Datenbank-Engine 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 Daten für große Objekte (LOB) wird eine Zeile für die LOB_DATA Zuordnungseinheit jeder Partition zurückgegeben. Wenn Zeilenüberlaufdaten in der Tabelle vorhanden sind, wird eine Zeile für die ROW_OVERFLOW_DATA Zuordnungseinheit in jeder Partition zurückgegeben.

Hinweis

In der Dokumentation wird der Begriff B-Struktur im Allgemeinen in Bezug auf Indizes verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Spaltenspeicherindizes oder Indizes für speicheroptimierte Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

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.

Wenn Sie eine Abfrage auf einer Serverinstanz durchführen sys.dm_db_index_physical_stats , die ein verfügbarkeitsgruppenlesbares sekundäres Replikat hosten, tritt möglicherweise ein REDO Blockierungsproblem auf. Dies liegt daran, dass diese dynamische Verwaltungsansicht eine Intent-Shared (IS)-Sperre für die angegebene Benutzertabelle oder -ansicht abruft, die Anforderungen durch einen REDO Thread für eine Exklusive (X)-Sperre für diese Benutzertabelle oder -ansicht blockieren kann.

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 | VORGABE

Die ID der Datenbank. database_id ist klein. Gültige Eingaben sind die ID einer Datenbank, NULL, , 0oder DEFAULT. Der Standardwert ist 0. NULL, 0und DEFAULT sind gleichwertige Werte in diesem Kontext.

Geben Sie NULL an, dass Informationen für alle Datenbanken in der Sql Server-Instanz zurückgegeben werden sollen. Wenn Sie für database_id angeben, müssen Sie auch für object_id, index_id und partition_number angebenNULL.NULL

Die integrierte Funktion DB_ID kann angegeben werden. Wenn Sie ohne Angabe eines Datenbanknamens verwenden DB_ID , muss die Kompatibilitätsebene der aktuellen Datenbank oder höher sein 90 .

object_id | NULL | 0 | VORGABE

Die Objekt-ID der Tabelle oder Ansicht, in der der Index aktiviert ist. object_id ist int. Gültige Eingaben sind die ID einer Tabelle und einer Ansicht, NULL, , 0oder DEFAULT. Der Standardwert ist 0. NULL, 0und DEFAULT sind gleichwertige Werte in diesem Kontext.

In SQL Server 2016 (13.x) und höheren Versionen umfassen gültige Eingaben auch den Namen der Servicebrokerwarteschlange oder den internen Tabellennamen der Warteschlange. Wenn Standardparameter angewendet werden (d. h. alle Objekte, alle Indizes usw.), werden Fragmentierungsinformationen für alle Warteschlangen im Resultset enthalten.

Geben Sie NULL an, dass Informationen für alle Tabellen und Ansichten in der angegebenen Datenbank zurückgegeben werden sollen. Wenn Sie für object_id angeben, müssen Sie auch für index_id und partition_number angebenNULL.NULL

index_id | 0 | NULL | -1 | VORGABE

Die ID des Indexes. index_id ist int. Gültige Eingaben sind die ID eines Indexes, 0 wenn object_id ein Heap, NULL, , -1oder DEFAULT. Der Standardwert ist -1. NULL, -1und DEFAULT sind gleichwertige Werte in diesem Kontext.

Geben Sie NULL an, dass Informationen für alle Indizes für eine Basistabelle oder -ansicht zurückgegeben werden sollen. Wenn Sie für index_id angebenNULL, müssen Sie auch für partition_number angebenNULL.

partition_number | NULL | 0 | VORGABE

Die Partitionsnummer im Objekt. partition_number ist int. Gültige Eingaben sind die partion_number eines Indexes oder Heaps, NULL, 0oder DEFAULT. Der Standardwert ist 0. NULL, 0und DEFAULT sind gleichwertige Werte in diesem Kontext.

Geben Sie NULL an, dass Informationen für alle Partitionen des besitzenden Objekts zurückgegeben werden sollen.

partition_number basiert auf 1. Ein nicht partitioniertes Index oder Heap hat partition_number festgelegt.1

Modus | NULL | VORGABE

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

Zurückgegebene Tabelle

Spaltenname	Datentyp	Beschreibung
database_id	smallint	Datenbank-ID der Tabelle oder Sicht. In Azure SQL-Datenbank sind die Werte innerhalb einer einzelnen Datenbank oder eines Pools für elastische Datenbanken eindeutig, aber nicht innerhalb eines logischen Servers.
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 - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (intern) - COLUMNSTORE DELETEBUFFER INDEX (intern) - COLUMNSTORE DELETEBITMAP INDEX (intern)
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. Die ROW_OVERFLOW_DATA Zuordnungseinheit enthält die Daten, die in Spalten vom Typ varchar(n), nvarchar(n), varbinary(n) und sql_variant gespeichert werden, die aus der Zeile verschoben werden.
index_depth	tinyint	Anzahl von Indexebenen. 1 = Heap oder LOB_DATA ROW_OVERFLOW_DATA Zuordnungseinheit.
index_level	tinyint	Aktuelle Ebene des Indexes. 0 für Indexblattebenen, Heaps und LOB_DATA zuordnungseinheiten ROW_OVERFLOW_DATA . Größer als 0 für Nichtleaf-Indexebenen. index_level ist die höchste auf der Stammebene eines Indexes. Die nichtleafierten Indizes werden nur verarbeitet, wenn der Modus ist DETAILED.
avg_fragmentation_in_percent	float	Logische Fragmentierung für Indizes oder Erweiterungsfragmentierung für Heaps in der IN_ROW_DATA Zuordnungseinheit. Der Wert wird als Prozentsatz gemessen und berücksichtigt mehrere Dateien. Definitionen der logischen Fragmentierung und Erweiterung finden Sie in den Anmerkungen. 0 für LOB_DATA und ROW_OVERFLOW_DATA Zuordnungseinheiten. NULL für Heaps, wenn der Modus ist SAMPLED.
fragment_count	bigint	Anzahl der Fragmente auf der Blattebene einer IN_ROW_DATA Zuordnungseinheit. Weitere Informationen zu Fragmenten finden Sie in den Hinweisen. NULL für nichtleafliche Ebenen eines Indexes und LOB_DATA oder ROW_OVERFLOW_DATA für Zuordnungseinheiten. NULL für Heaps, wenn der Modus ist 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 nichtleafliche Ebenen eines Indexes und LOB_DATA oder ROW_OVERFLOW_DATA für Zuordnungseinheiten. NULL für Heaps, wenn der Modus ist SAMPLED.
page_count	bigint	Gesamtanzahl von Index- oder Datenseiten. Bei einem Index wird die Gesamtanzahl der Indexseiten auf der aktuellen Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit angezeigt. Bei einem Heap wird die Gesamtanzahl der Datenseiten in der IN_ROW_DATA Zuordnungseinheit angezeigt. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, Gesamtanzahl der 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 wird der Mittelwert aller Datenseiten in der IN_ROW_DATA Zuordnungseinheit berechnet. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, der Mittelwert aller Seiten in der Zuordnungseinheit. NULL wenn der Modus ist LIMITED.
record_count	bigint	Gesamtanzahl von Datensätzen. Bei einem Index gilt die Gesamtanzahl der Datensätze auf der aktuellen Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap wird die Gesamtanzahl der Datensätze in der IN_ROW_DATA Zuordnungseinheit verwendet. 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 zurückgegeben werden, indem ein gegen SELECT COUNT(*) den Heap ausgeführt wird. Dies liegt daran, dass eine Zeile mehrere Datensätze enthalten kann. In einigen Aktualisierungssituationen kann beispielsweise eine einzelne Heapzeile einen Weiterleitungsdatensatz und einen weitergeleiteten Datensatz als Ergebnis des Aktualisierungsvorgangs aufweisen. Außerdem werden die meisten großen BRANCHENzeilen in mehrere Datensätze im LOB_DATA Speicher aufgeteilt. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, die Gesamtanzahl der Datensätze in der vollständigen Zuordnungseinheit. NULL wenn der Modus ist 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 nichtleafliche Ebenen eines Indexes in der IN_ROW_DATA Zuordnungseinheit. NULL wenn der Modus ist LIMITED.
version_ghost_record_count	bigint	Anzahl inaktiver Datensätze, die von einer ausstehenden Momentaufnahme-Isolationstransaktion in einer Zuordnungseinheit beibehalten werden. 0 für nichtleafliche Ebenen eines Indexes in der IN_ROW_DATA Zuordnungseinheit. NULL wenn der Modus ist LIMITED.
min_record_size_in_bytes	int	Minimale Datensatzgröße in Bytes. Für einen Index gilt die mindeste Datensatzgröße für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap wird die mindeste Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit verwendet. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, die mindeste Datensatzgröße in der vollständigen Zuordnungseinheit. NULL wenn der Modus ist LIMITED.
max_record_size_in_bytes	int	Maximale Datensatzgröße in Bytes. Bei einem Index gilt die maximale Datensatzgröße für die aktuelle Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit. Bei einem Heap wird die maximale Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit verwendet. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, die maximale Datensatzgröße in der vollständigen Zuordnungseinheit. NULL wenn der Modus ist 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 wird die durchschnittliche Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit verwendet. Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten, die durchschnittliche Datensatzgröße in der vollständigen Zuordnungseinheit. NULL wenn der Modus ist LIMITED.
forwarded_record_count	bigint	Die Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger an einen anderen Datenspeicherort aufweisen. (Dieser Zustand tritt während einer Aktualisierung auf, wenn nicht genügend Platz zum Speichern der neuen Zeile am ursprünglichen Speicherort vorhanden ist.) NULL für alle anderen Zuordnungseinheiten als die IN_ROW_DATA Zuordnungseinheiten für einen Heap. NULL für Heaps, wenn der Modus ist LIMITED.
compressed_page_count	bigint	Die Anzahl der komprimierten Seiten. Bei Heaps werden neu zugeordnete Seiten nicht PAGE komprimiert. Ein Heap wird PAGE unter zwei besonderen Bedingungen komprimiert: wenn Daten massenimportiert werden oder ein Heap neu erstellt wird. Typische DML-Vorgänge, die zu Seitenzuweisungen führen, werden nicht PAGE komprimiert. Erstellen Sie einen Heap neu, wenn der compressed_page_count Wert größer als der gewünschte Schwellenwert wird. Bei Tabellen mit einem gruppierten Index gibt der compressed_page_count Wert die Effektivität der PAGE Komprimierung an.
hobt_id	bigint	Heap- oder B-Struktur-ID des Indexes oder der Partition. Bei Columnstore-Indizes ist dies die ID für ein Rowset, das interne Spaltenspeicherdaten für eine Partition nachverfolgt. Die Rowsets werden als Datenhaps oder B-Strukturen gespeichert. Sie haben dieselbe Index-ID wie der übergeordnete Spaltenspeicherindex. Weitere Informationen finden Sie unter sys.internal_partitions.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL verwaltete Instanz
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID - Der übergeordnete Index ist kein Spaltenspeicherindex. OPEN - Löscher und Scanner verwenden dies. DRAINING - Löscher werden ausgelassen, aber Scanner verwenden es weiterhin. FLUSHING - Puffer ist geschlossen, und Zeilen im Puffer werden in die Löschbitmap geschrieben. RETIRING - Zeilen im Puffer für den geschlossenen Löschvorgang wurden in die Löschbitmap geschrieben, der Puffer wurde jedoch nicht abgeschnitten, da Scanner sie weiterhin verwenden. Neue Scanner müssen den Puffer nicht verwenden, da der geöffnete Puffer ausreichend ist. READY - Dieser Löschpuffer ist einsatzbereit. Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL verwaltete Instanz
version_record_count	bigint	Dies ist die Anzahl der Zeilenversionsdatensätze, die in diesem Index verwaltet werden. Diese Zeilenversionen werden vom Feature für die beschleunigte Datenbankwiederherstellung verwaltet. Gilt für: SQL Server 2019 (15.x) und höhere Versionen sowie Azure SQL-Datenbank
inrow_version_record_count	bigint	Die Anzahl der ADR-Versionsdatensätze, die in der Datenzeile gespeichert sind, um einen schnellen Abruf zu ermöglichen. Gilt für: SQL Server 2019 (15.x) und höhere Versionen sowie Azure SQL-Datenbank
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) und höhere Versionen sowie Azure SQL-Datenbank
total_inrow_version_payload_size_in_bytes	bigint	Gesamtgröße in Byte der Zeilenversionsdatensätze für diesen Index. Gilt für: SQL Server 2019 (15.x) und höhere Versionen sowie 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) und höhere Versionen sowie 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) und höhere Versionen sowie Azure SQL-Datenbank

Hinweis

In der Dokumentation wird der Begriff B-Struktur im Allgemeinen in Bezug auf Indizes verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Spaltenspeicherindizes oder Indizes für speicheroptimierte Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Hinweise

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, SAMPLEDoder DETAILED. 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 LIMITED Modus ist der schnellste Modus und scannt die kleinste Anzahl von Seiten. Bei einem Index werden nur die Seiten der übergeordneten B-Strukturebene (d. h. die Seiten oberhalb der Blattebene) gescannt. Bei einem Heap werden die zugehörigen PFS- und IAM-Seiten untersucht, und die Datenseiten eines Heaps werden im LIMITED Modus gescannt.

Der LIMITED Modus liegt NULL daran, compressed_page_count dass die Datenbank-Engine nur nichtleafe Seiten der B-Struktur und der IAM- und PFS-Seiten des Heaps scannt. Verwenden Sie SAMPLED den Modus, um einen geschätzten Wert für compressed_page_count, und verwenden Sie den Modus DETAILED , um den tatsächlichen Wert für compressed_page_count. Der SAMPLED Modus gibt Statistiken zurück, die auf einer Stichprobe von 1 Prozent aller Seiten im Index oder Heap basieren. Die Ergebnisse im SAMPLED Modus sollten als ungefähr betrachtet werden. Wenn der Index oder Heap weniger als 10.000 Seiten aufweist, DETAILED wird der Modus anstelle von SAMPLED.

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

Die Modi sind schrittweise langsamer von LIMITED zu DETAILED, da mehr Arbeit in jedem Modus ausgeführt wird. Um die Größe oder Fragmentierungsebene einer Tabelle oder eines Indexes schnell zu messen, verwenden Sie den LIMITED Modus. Es ist die schnellste und gibt keine Zeile für jede nichtleafliche Ebene in der IN_ROW_DATA Zuordnungseinheit des Indexes 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 unbeabsichtigten Ergebnissen führen. Wenn beispielsweise der Datenbank- oder Objektname nicht gefunden werden kann, weil sie nicht vorhanden sind oder falsch geschrieben sind, geben beide Funktionen zurück NULL. Die sys.dm_db_index_physical_stats Funktion wird als Wildcardwert interpretiert NULL , der alle Datenbanken oder alle Objekte angibt.

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 ausgewertet, nicht in der datenbank, die in database_id angegeben ist. Dieses Verhalten kann dazu führen, dass die OBJECT_ID Funktion einen NULL Wert zurückgibt. Wenn der Objektname sowohl im aktuellen Datenbankkontext als auch in der angegebenen Datenbank vorhanden ist, wird eine Fehlermeldung zurückgegeben. 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'AdventureWorks2022'), 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 AdventureWorks2022;
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

Best Practice

Stellen Sie immer sicher, dass beim Verwenden oder Verwenden DB_ID eine OBJECT_IDgültige ID zurückgegeben wird. Wenn Sie z. B. verwenden OBJECT_ID, geben Sie einen dreiteiligen Namen an, z OBJECT_ID(N'AdventureWorks2022.Person.Address'). B. 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

Fragmentierung erfolgt durch den Prozess von Datenänderungen (INSERT, UPDATEund DELETE Anweisungen), die für die Tabelle und daher an die in der Tabelle definierten Indizes vorgenommen werden. Da diese Änderungen nicht gleichmäßig auf die Zeilen der Tabelle und Indizes verteilt werden, kann die Füllkraft jeder Seite im Laufe der Zeit variieren. Bei Abfragen, die einen Teil oder alle Indizes einer Tabelle scannen, kann diese Art von Fragmentierung zu weiteren Seitenlesevorgängen führen, wodurch das parallele Scannen von Daten behindert wird.

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 dazu DBCC SHOWCONTIGberücksichtigen die Fragmentierungsberechnungsalgorithmen in beiden Fällen speicherübergreifend mehrere Dateien und sind daher genau.

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.

Fragmentierung des Umfangs

Dies ist der Prozentsatz der Blöcke, die auf den Blattseiten eines Heaps nicht ordnungsgemäß sortiert sind. Ein out-of-order-Umfang ist eine, für die der Umfang, in dem die aktuelle Seite für einen Heap enthalten ist, nach dem Umfang, der die vorherige Seite enthält, nicht physisch das nächste Ausmaß ist.

Der Wert für avg_fragmentation_in_percent den Wert sollte so nah wie möglich null sein, um maximale Leistung zu erzielen. Werte von 0 Prozent bis 10 Prozent können jedoch akzeptabel sein. Alle Methoden zum Reduzieren der Fragmentierung, z. B. Neuerstellen, Neuorganisation oder Neuerstellen, können verwendet werden, um diese Werte zu reduzieren. Weitere Informationen zum Analysieren des Grads der Fragmentierung in einem Index finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

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:

• Legen Sie den gruppierten Index ab, und erstellen Sie den gruppierten Index neu.

  Durch das Neuerstellen eines gruppierten Indexes werden die Daten neu verteilt und ergebnisse in vollständigen Datenseiten. Der Füllungsgrad kann über die FILLFACTOR-Option in CREATE INDEX konfiguriert werden. Die Nachteile dieser Methode sind, dass der Index während des Abbruchs offline und neu erstellt wird, und dass der Vorgang atomar ist. Wenn die Indexerstellung unterbrochen wird, wird der Index nicht neu erstellt. Weitere Informationen finden Sie unter CREATE INDEX.

• Verwenden Sie ALTER INDEX REORGANIZEden Ersatz für DBCC INDEXDEFRAG, um die Blattebenenseiten des Indexes 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. Der Nachteil dieser Methode besteht darin, dass es nicht so gut ist, die Daten als Indexneuerstellungsvorgang neu zu organisieren, und es werden keine Statistiken aktualisiert.

• Verwenden Sie ALTER INDEX REBUILD, den Ersatz für DBCC DBREINDEX, um den Index online oder offline neu zu erstellen. Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Die Fragmentierung allein reicht nicht aus, 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. Wenn die Abfrageworkloads in einer fragmentierten Tabelle oder einem Index keine Scans umfassen, da die Workload in erster Linie Singleton-Nachschlagevorgänge ist, kann das Entfernen der Fragmentierung keine Auswirkungen haben.

Hinweis

Wird ausgeführt DBCC SHRINKFILE oder DBCC SHRINKDATABASE kann fragmentiert werden, wenn 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 gelöscht wird, um den Heap neu zu erstellen, werden die Daten nicht verschoben und bleiben optimal positioniert. Informationen zum Ausführen dieser Vorgänge finden Sie unter CREATE INDEX und DROP INDEX.

Achtung

Beim Erstellen und Ablegen eines gruppierten Indexes in einer Tabelle werden alle nicht gruppierten Indizes in dieser Tabelle zweimal neu erstellt.

Komprimieren großer Objektdaten

Standardmäßig komprimiert die ALTER INDEX REORGANIZE Anweisung Seiten, die große Objektdaten (LOB) enthalten. Da branchenspezifische Seiten beim Leeren nicht abgeglichen werden, kann die Komprimierung dieser Daten die Speicherplatznutzung verbessern, wenn viele BRANCHENdaten gelöscht werden oder eine LOB-Spalte gelöscht 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 Ansicht zugeordnet sind, neu organisiert. Darüber hinaus werden alle Branchenspalten, die dem gruppierten Index, der zugrunde liegenden Tabelle oder einem nicht gruppierten Index mit enthaltenen Spalten zugeordnet sind, komprimiert.

Auswerten der Speicherplatznutzung

Die avg_page_space_used_in_percent Spalte gibt die Seitenfülle an. Um eine optimale Speicherplatznutzung zu erzielen, sollte dieser Wert für einen Index, der nicht über viele zufällige Einfügungen verfügt, nahe 100 Prozent liegen. Ein Index mit vielen zufälligen Einfügungen und sehr vollständigen Seiten hat jedoch eine höhere Anzahl von Seitenaufteilungen. Dadurch entsteht mehr Fragmentierung. Deshalb sollte dieser Wert unter 100 % liegen, um Seitenteilungen zu reduzieren. Durch das Neuerstellen eines Indexes mit der FILLFACTOR angegebenen Option kann die Seitenfüllzahl so geändert werden, dass sie an das Abfragemuster im Index angepasst wird. Weitere Informationen zum Füllfaktor finden Sie unter Angeben des Füllfaktors für einen Index. Komprimiert außerdem einen Index, indem versucht wird, ALTER INDEX REORGANIZE Seiten auf das FILLFACTOR zuletzt angegebene Zufüllen zu füllen. Dadurch erhöht sich der Wert in avg_space_used_in_percent. ALTER INDEX REORGANIZE Die Seitenfülle kann nicht reduziert werden. 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. Je größer der avg_fragment_size_in_pages Wert ist, desto besser ist die Leistung des Bereichsscans. Die avg_fragment_size_in_pages Werte sind avg_fragmentation_in_percent umgekehrt proportional zueinander. Deshalb sollte durch das Neuerstellen oder Neuorganisieren eines Indexes die Fragmentierung reduziert und die Fragmentgröße erhöht werden.

Begrenzungen

Gibt keine Daten für gruppierte Spaltenspeicherindizes zurück.

Berechtigungen

Folgende Berechtigungen sind erforderlich:

• CONTROL Berechtigung für das angegebene Objekt in der Datenbank.

• VIEW DATABASE STATEoder VIEW DATABASE PERFORMANCE STATE (SQL Server 2022)-Berechtigung zum Zurückgeben von Informationen zu allen Objekten innerhalb der angegebenen Datenbank mithilfe der Objekt-Wildcard-@object_id = NULL.

• VIEW SERVER STATEoder VIEW SERVER PERFORMANCE STATE (SQL Server 2022)-Berechtigung zum Zurückgeben von Informationen zu allen Datenbanken mithilfe des Datenbank-Wildcard-@database_id = NULL.

Durch die Gewährung VIEW DATABASE STATE können alle Objekte in der Datenbank zurückgegeben werden, unabhängig von berechtigungen CONTROL , die für bestimmte Objekte verweigert wurden.

Das VIEW DATABASE STATE Verweigern der Rückgabe aller Objekte in der Datenbank ist unabhängig von den CONTROL Berechtigungen, die für bestimmte Objekte erteilt wurden, unzulässig. Wenn der Datenbank-Wildcard -@database_id = NULL angegeben wird, wird die Datenbank nicht angegeben.

Weitere Informationen finden Sie unter dynamische Systemverwaltungsansichten.

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

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. Zum Ausführen dieser Abfrage ist mindestens CONTROL eine Berechtigung für die Person.Address Tabelle erforderlich.

DECLARE @db_id SMALLINT;
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_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 dbo.DatabaseLog-Heap in der AdventureWorks2022-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. Zum Ausführen dieser Abfrage ist mindestens CONTROL eine Berechtigung für die dbo.DatabaseLog Tabelle erforderlich.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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 für alle Datenbanken

Im folgenden Beispiel werden alle Statistiken für alle Tabellen und Indizes innerhalb der SQL Server-Instanz 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 zum Neuerstellen oder Neuorganisieren von Indizes

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.

-- 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
    INNER 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. Verwenden von sys.dm_db_index_physical_stats zum Anzeigen der Anzahl der seitenkomprimierten Seiten

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
INNER 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 SAMPLED-Modus

Das folgende Beispiel zeigt, wie SAMPLED der Modus eine Ungefähre zurückgibt, die sich von den Ergebnissen des DETAILED Modus unterscheidet.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

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. Abfragedienstbrokerwarteschlangen für die Indexfragmentierung

Gilt für: SQL Server 2016 (13.x) und höhere Versionen

Das folgende Beispiel zeigt, wie Sie Serverbrokerwarteschlangen nach 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);

Zugehöriger Inhalt

• Dynamische Systemverwaltungssichten
• Indexbezogene dynamische Verwaltungsansichten und -funktionen (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)
• Transact-SQL-Referenz (Datenbank-Engine)