sys.dm_db_index_physical_stats (Transact-SQL)
Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance
Gibt Größen- und Fragmentierungsinformationen für die Daten und Indizes der angegebenen Tabelle oder Ansicht 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. Für LOB-Daten (Large Object) 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 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.
sys.dm_db_index_physical_stats
gibt keine Informationen zu speicheroptimierten Columnstore-Indizes zurück. Informationen zur speicheroptimierten Indexverwendung finden Sie unter sys.dm_db_xtp_index_stats (Transact-SQL).
Wenn Sie eine Abfrage sys.dm_db_index_physical_stats
auf einem Server instance, der ein lesbares sekundäres Replikat der Verfügbarkeitsgruppe hostet, tritt möglicherweise ein REDO
Blockierungsproblem auf. Dies liegt daran, dass diese dynamische Verwaltungsansicht eine IS
Sperre für die angegebene Benutzertabelle oder -sicht abruft, die Anforderungen eines REDO
Threads für eine X
Sperre für diese Benutzertabelle oder -sicht 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 | 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 gleichwertige Werte.
Geben Sie NULL an, um Informationen für alle Datenbanken im instance 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. Bei Verwendung DB_ID
ohne Angabe eines Datenbanknamens muss der Kompatibilitätsgrad der aktuellen Datenbank mindestens 90 betragen.
object_id | NULL | 0 | STANDARD
Die 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. Die Standardeinstellung ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertige Werte. Ab SQL Server 2016 (13.x) umfassen gültige Eingaben auch den Service Broker-Warteschlangennamen oder den internen Tabellennamen der Warteschlange. Wenn Standardparameter angewendet werden (d. h. alle Objekte, alle Indizes usw.), sind Fragmentierungsinformationen für alle Warteschlangen im Resultset enthalten.
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 eines Indexes, 0, wenn object_id ein Heap, NULL, -1 oder DEFAULT ist. 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 gleichwertige Werte.
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. modus 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. 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 – GRUPPIERTER INDEX – NICHT GRUPPIERTER INDEX – PRIMÄRER XML-INDEX - ERWEITERTER INDEX – XML-INDEX – COLUMNSTORE MAPPING INDEX (intern) – COLUMNSTORE DELETEBUFFER INDEX (intern) – COLUMNSTORE DELETEBITMAP INDEX (intern) |
hobt_id | bigint | Heap- oder B-Tree-ID des Indexes oder der Partition. Bei 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-Bäume gespeichert. Sie weisen dieselbe Index-ID wie der übergeordnete Columnstore-Index auf. Weitere Informationen finden Sie unter sys.internal_partitions (Transact-SQL). |
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 Indexblattebenen, Heaps und LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten.Werte größer 0 für Nicht-Indexblattebenen. index_level ist die höchste auf der Stammebene eines Indexes. Die nicht verleerten Ebenen von Indizes werden nur verarbeitet, wenn modus = DETAILED ist. |
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 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 der Fragmente auf der Blattebene einer IN_ROW_DATA Zuordnungseinheit. Weitere Informationen zu Fragmenten finden Sie unter den Hinweisen.NULL für nicht leere Ebenen eines Indexes und LOB_DATA oder Zuordnungseinheiten ROW_OVERFLOW_DATA .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 nicht leere Ebenen eines Indexes und LOB_DATA oder Zuordnungseinheiten ROW_OVERFLOW_DATA .NULL für Heaps im Modus = SAMPLED. |
page_count | bigint | Gesamtanzahl von Index- oder Datenseiten. Bei einem Index die Gesamtzahl der Indexseiten auf der aktuellen Ebene der B-Struktur in der IN_ROW_DATA Zuordnungseinheit.Bei einem Heap die Gesamtanzahl der Datenseiten in der IN_ROW_DATA Zuordnungseinheit.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 der Durchschnitt aller Datenseiten in der IN_ROW_DATA Zuordnungseinheit.Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten der Durchschnitt 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 die Gesamtzahl der Datensätze 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 von für SELECT COUNT(*) 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 in mehrere Datensätze im LOB_DATA Speicher aufgeteilt.Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten die Gesamtzahl der Datensätze in der vollständigen 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 nichtleafe Ebenen 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 nichtleafe Ebenen 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 die minimale Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit.Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten die minimale Datensatzgröße in der vollständigen 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 die maximale Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit.Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten die maximale Datensatzgröße in der vollständigen 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 die durchschnittliche Datensatzgröße in der IN_ROW_DATA Zuordnungseinheit.Für LOB_DATA oder ROW_OVERFLOW_DATA Zuordnungseinheiten die durchschnittliche Datensatzgröße in der vollständigen Zuordnungseinheit.NULL bei Modus = LIMITED. |
forwarded_record_count | bigint | Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger auf einen anderen Datenspeicherort besitzen. (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 jede andere Zuordnungseinheit als die IN_ROW_DATA Zuordnungseinheiten für einen Heap.NULL für Heaps, wenn modus = LIMITED. |
compressed_page_count | bigint | Die Anzahl der komprimierten Seiten. Für Heaps werden neu zugeordnete Seiten nicht PAGE komprimiert. 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 verursachen, 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. |
columnstore_delete_buffer_state | tinyint | 0 = NOT_APPLICABLE 1 = OPEN 2 = ENTWÄSSERUNG 3 = FLUSHING 4 = IN DEN RUHESTAND 5 = BEREIT Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL Datenbank und Azure SQL Managed Instance |
columnstore_delete_buffer_state_desc | nvarchar(60) | UNGÜLTIG: Der übergeordnete Index ist kein Columnstore-Index. OPEN: Löscher und Scanner verwenden dies. ENTWÄSSERUNG : Löschvorgänge werden ausgelaugt, aber Von Scannern wird sie weiterhin verwendet. FLUSHING: Der Puffer wird geschlossen, und zeilen im Puffer werden in die Bitmap zum Löschen geschrieben. ZURÜCKSTELLEN: Zeilen im Puffer für geschlossenes Löschen wurden in die Bitmap "Löschen" geschrieben, aber der Puffer wurde nicht abgeschnitten, da Scanner ihn weiterhin verwenden. Neue Scanner müssen den zurückstellenden 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 Managed Instance |
version_record_count | bigint | Dies ist die Anzahl der Zeilenversionsdatensätze, die in diesem Index verwaltet werden. Diese Zeilenversionen werden vom Feature Beschleunigte Datenbankwiederherstellung verwaltet. Gilt für: SQL Server 2019 (15.x) und höhere Versionen und 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) und höhere Versionen und Azure SQL Datenbank |
inrow_diff_version_record_count | bigint | Anzahl der ADR-Versionsdatensätze, die in Form von Unterschieden zur Basisversion aufbewahrt werden. Gilt für: SQL Server 2019 (15.x) und höhere Versionen und Azure SQL Datenbank |
total_inrow_version_payload_size_in_bytes | bigint | Gesamtgröße in Bytes der Zeilenversionsdatensätze für diesen Index. Gilt für: SQL Server 2019 (15.x) und höhere Versionen und Azure SQL Datenbank |
offrow_regular_version_record_count | bigint | Anzahl der Versionsdatensätze, die außerhalb der ursprünglichen Datenzeile aufbewahrt werden. Gilt für: SQL Server 2019 (15.x) und höhere Versionen und 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 und Azure SQL Datenbank |
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
Die sys.dm_db_index_physical_stats
dynamische Verwaltungsfunktion 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
Für ist nur eine beabsichtigte gemeinsame Tabellensperre (Intent-Shared, IS) erforderlich, und zwar unabhängig vom Modus, in dem die Funktion 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 NULL, compressed_page_count
da die Datenbank-Engine nur nicht leere Seiten der B-Struktur und der IAM- und PFS-Seiten des Heaps scannt. Verwenden Sie den Modus SAMPLED, um einen geschätzten Wert für compressed_page_count
zu erhalten. Verwenden Sie den Modus DETAILED, um den tatsächlichen Wert für compressed_page_count
zu erhalten. 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. Es ist die schnellste und gibt keine Zeile für jede nicht verleerte 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 oder falsch geschrieben sind, geben beide Funktionen NULL zurück. Von der sys.dm_db_index_physical_stats
-Funktion wird NULL als Platzhalterwert interpretiert, von dem alle Datenbanken bzw. 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 ausgewertet, nicht im Kontext der in database_id angegebenen Datenbank. 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 möglicherweise 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
Empfehlung
Stellen Sie immer sicher, dass eine gültige ID zurückgegeben wird, wenn Sie oder OBJECT_ID
verwendenDB_ID
. Wenn Sie beispielsweise verwenden OBJECT_ID
, geben Sie einen dreiteiligen Namen wie OBJECT_ID(N'AdventureWorks2022.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 auf die Zeilen der Tabelle und der Indizes verteilt sind, kann die Fülle jeder Seite im Laufe der Zeit variieren. 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 zur DBCC SHOWCONTIG
Fragmentierungsberechnung betrachten die Algorithmen für die Fragmentierungsberechnung in beiden Fällen einen Speicher, der sich über mehrere Dateien erstreckt und daher genau ist.
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.
Ausdehnungsfragmentierung
Dies ist der Prozentsatz der Blöcke, die auf den Blattseiten eines Heaps nicht ordnungsgemäß sortiert sind. Bei einer Überschreitung der Reihenfolge handelt es sich um einen Bereich, bei dem der Umfang, der die aktuelle Seite für einen Heap enthält, physisch nicht das nächste Ausmaß nach dem Umfang 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, den Ersatz für
DBCC INDEXDEFRAG
, um die Seiten auf Blattebene des Indexes in logischer 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 die Daten nicht so gut wie ein Indexneuerstellungsvorgang neu organisiert werden und statistiken nicht aktualisiert werden.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 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. Wenn die Abfrageworkload für eine fragmentierte Tabelle oder einen fragmentierten Index keine Überprüfungen umfasst, da es sich bei der Workload hauptsächlich um Singleton-Nachschlagevorgänge handelt, hat das Entfernen der Fragmentierung möglicherweise keine Auswirkungen.
Hinweis
Wenn DBCC SHRINKFILE
ein Index während des Verkleinerungsvorgangs teilweise oder vollständig verschoben wird, kann es zu einer Fragmentierung führen DBCC SHRINKDATABASE
. 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 bleiben optimal positioniert. 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 großer Objektdaten
Standardmäßig komprimiert die ALTER INDEX REORGANIZE-Anweisung Seiten, die LOB-Daten (Large Object) enthalten. Da die Zuordnung von LOB-Seiten nicht aufgehoben wird, wenn sie leer sind, kann das Komprimieren der Daten die Speicherplatznutzung verbessern, wenn viele LOB-Daten gelöscht wurden 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 Sicht zugeordnet sind, neu organisiert. Darüber hinaus werden alle BRANCHENspalten, die dem gruppierten Index, der zugrunde liegenden Tabelle oder dem nicht gruppierten Index mit eingeschlossenen Spalten zugeordnet sind, komprimiert.
Auswerten der Speicherplatznutzung
Die avg_page_space_used_in_percent
-Spalte zeigt Seitenauslastung an. Um eine optimale Speicherplatznutzung zu erzielen, sollte dieser Wert für einen Index, der nicht viele zufällige Einfügungen aufweist, bei nahezu 100 Prozent liegen. Ein Index mit vielen zufälligen Einfügungen und sehr vollen Seiten weist jedoch eine erhöhte Anzahl von Seitenaufteilungen auf. 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. ALTER INDEX REORGANIZE kann die Seitenfülle nicht reduzieren. 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 also der avg_fragment_size_in_pages
-Wert, desto besser die Leistung des Bereichsscans. Der avg_fragment_size_in_pages
- und der avg_fragmentation_in_percent
-Wert sind zueinander umgekehrt proportional. 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'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 Heap dbo.DatabaseLog
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. 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'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 instance 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 in sys.dm_db_index_physical_stats
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. Ein Fehler wird generiert, wenn die aktuelle Datenbank einen Kompatibilitätsgrad von 80 oder niedriger aufweist. 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
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 Sie sys.dm_db_index_physical_stats
, um die Anzahl der seitenkomprimierten Seiten anzuzeigen.
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 sys.dm_db_index_physical_stats
im SAMPLED-Modus
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 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 Indexfragmentierung
Gilt für: SQL Server 2016 (13.x) und höhere Versionen.
Das folgende Beispiel zeigt, 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);
Siehe auch
- 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)
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unterFeedback senden und anzeigen für