sys.dm_db_index_physical_stats (Transact-SQL)

Gibt Größen- und Fragmentierungsinformationen für die Daten und Indizes der angegebenen Tabelle oder Sicht zurück. Für einen Index wird eine Zeile für jede Ebene der B-Struktur in jeder Partition zurückgegeben. Für einen 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. Falls Zeilenüberlaufdaten in der Tabelle vorhanden sind, wird eine Zeile für die ROW_OVERFLOW_DATA-Zuordnungseinheit in jeder Partition zurückgegeben. Informationen zu Zuordnungseinheiten und Partitionen finden Sie unter Architektur von Tabellen- und Indexdatenstrukturen.

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 | DEFAULT
  Die ID der Datenbank. database_id ist vom Datentyp smallint. Gültige Eingaben sind die ID einer Datenbank, NULL, 0 oder DEFAULT. Die Standardeinstellung ist 0. NULL, 0 und DEFAULT sind in diesem Kontext gleichwertig.

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

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

• object_id | NULL | 0 | DEFAULT
  Die Objekt-ID der Tabelle oder Sicht mit dem Index. object_id ist vom Datentyp int.

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

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

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

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

• partition_number | NULL | 0 | DEFAULT
  Die Partitionsnummer im Objekt. partition_number ist vom Datentyp int. Gültige Eingaben sind der partion_number-Wert eines Index bzw. 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.

• mode | NULL | DEFAULT
  Der Name des Modus. mode gibt die Scanebene an, mit der Statistiken abgerufen werden. mode ist vom Datentyp 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 Index. 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 SPATIAL INDEX XML INDEX
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 Datentyp 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 vom Datentyp varchar(n), nvarchar(n), varbinary(n) und sql_variant gespeichert sind, für die ein Push außerhalb der Zeilen ausgeführt wurde. Weitere Informationen finden Sie unter Zeilenüberlauf bei Daten über 8 KB.
index_depth	tinyint	Anzahl von Indexebenen. 1 = Heap oder LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheit.
index_level	tinyint	Aktuelle Ebene des Index. 0 für Indexblattebenen, Heaps und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. Werte größer 0 für Nicht-Indexblattebenen. index_level weist auf der Stammebene eines Index den höchsten Wert auf. Die inneren Knotenebenen von Indizes werden nur verarbeitet, wenn mode = DETAILED.
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, wenn mode = 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 innere Knotenebenen eines Index und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. NULL für Heaps, wenn mode = 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 innere Knotenebenen eines Index und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten. NULL für Heaps, wenn mode = SAMPLED.
page_count	bigint	Gesamtanzahl von Index- oder Datenseiten. Für einen Index die Gesamtanzahl von Indexseiten auf der aktuellen Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen Heap die Gesamtanzahl von Datenseiten in der IN_ROW_DATA-Zuordnungseinheit. Für LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten 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 bezieht sich der Durchschnittswert auf die aktuelle Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen Heap der Durchschnittswert aller Datenseiten in der IN_ROW_DATA-Zuordnungseinheit. Für LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten der Durchschnittswert aller Seiten in der Zuordnungseinheit. NULL, wenn mode = LIMITED.
record_count	bigint	Gesamtanzahl von Datensätzen. Für einen Index bezieht sich die Gesamtanzahl von Datensätzen auf die aktuelle Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen Heap die Gesamtanzahl von Datensätzen in der IN_ROW_DATA-Zuordnungseinheit. Hinweis Bei einem Heap stimmt die Anzahl der Datensätze, die von dieser Funktion zurückgegeben wird, möglicherweise nicht mit der Anzahl der Zeilen überein, die beim Ausführen von SELECT COUNT(*) für den Heap zurückgegeben werden. Das liegt daran, dass eine Zeile möglicherweise mehrere Datensätze enthält. So kann in bestimmten Aktualisierungssituationen eine einzelne Heapzeile möglicherweise über einen Weiterleitungsdatensatz und einen weitergeleiteten Datensatz als Ergebnis der Aktualisierung verfügen. Außerdem werden die meisten großen LOB-Zeilen im LOB_DATA-Speicher in mehrere Datensätze geteilt. Für LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten die Gesamtanzahl von Datensätzen in der gesamten Zuordnungseinheit. NULL, wenn mode = 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 innere Knotenebenen eines Index in der IN_ROW_DATA-Zuordnungseinheit. NULL, wenn mode = LIMITED.
version_ghost_record_count	bigint	Anzahl inaktiver Datensätze, die von einer ausstehenden Snapshotisolationstransaktion in einer Zuordnungseinheit beibehalten werden. 0 für innere Knotenebenen eines Index in der IN_ROW_DATA-Zuordnungseinheit. NULL, wenn mode = LIMITED.
min_record_size_in_bytes	int	Minimale Datensatzgröße in Bytes. Für einen Index bezieht sich die minimale Datensatzgröße auf die aktuelle Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen 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 gesamten Zuordnungseinheit. NULL, wenn mode = LIMITED.
max_record_size_in_bytes	int	Maximale Datensatzgröße in Bytes. Für einen Index bezieht sich die maximale Datensatzgröße auf die aktuelle Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen 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 gesamten Zuordnungseinheit. NULL, wenn mode = LIMITED.
avg_record_size_in_bytes	float	Durchschnittliche Datensatzgröße in Bytes. Für einen Index bezieht sich die durchschnittliche Datensatzgröße auf die aktuelle Ebene der B-Struktur in der IN_ROW_DATA-Zuordnungseinheit. Für einen 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 gesamten Zuordnungseinheit. NULL, wenn mode = LIMITED.
forwarded_record_count	bigint	Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger auf einen anderen Datenspeicherort besitzen. (Dieser Status tritt während einer Aktualisierung 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 mode = LIMITED.
compressed_page_count	bigint	Die Anzahl der komprimierten Seiten. Für Heaps sind neu zugeordnete Seiten nicht PAGE-komprimiert. Ein Heap wird nur unter zwei besonderen Bedingungen PAGE-komprimiert: wenn Massendaten importiert werden und 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 Effektivität der PAGE-Komprimierung an.

Hinweise

Die dynamische Verwaltungsfunktion sys.dm_db_index_physical_stats ersetzt die DBCC SHOWCONTIG-Anweisung. Dieser dynamischen Verwaltungsfunktion können keine abhängigen Parameter von CROSS APPLY und OUTER APPLY übergeben werden.

Scanmodi

In welchem Modus die Funktion ausgeführt wird, bestimmt die ausgeführte Scanebene zum Abrufen der statistischen Daten, die von der Funktion verwendet werden. mode wird als LIMITED, SAMPLED oder DETAILED angegeben. Die Funktion durchsucht die Seitenketten nach den Zuordnungseinheiten, aus denen die angegebenen Partitionen der Tabelle oder des Index bestehen. Für sys.dm_db_index_physical_stats ist nur eine beabsichtigte freigegebene Tabellensperre (IS) erforderlich, und zwar unabhängig vom Modus, in dem die Funktion ausgeführt wird. Weitere Informationen zu Sperren finden Sie unter Sperrmodi.

Der Modus LIMITED ist am schnellsten und durchsucht am wenigsten Seiten. Für einen Index werden nur die Seiten der übergeordneten Ebene der B-Struktur (d. h. die Seiten über der Blattebene) gescannt. Bei einem Heap werden nur die zugehörigen PFS- und IAM-Seiten untersucht. Die Datenseiten des Heaps werden nicht gescannt. In SQL Server 2005 werden im Modus LIMITED alle Seiten eines Heaps gescannt.

Mit dem Modus LIMITED hat compressed_page_count den Wert NULL, da Database Engine (Datenbankmodul) nur innere Knotenseiten der B-Struktur und die IAM- und PFS-Seiten des Heaps scannt. Verwenden Sie den Modus SAMPLED, um einen geschätzten Wert für compressed_page_count abzurufen, und den Modus DETAILED, um den tatsächlichen Wert für den compressed_page_count abzurufen. Der Modus SAMPLED gibt Statistiken basierend auf einer Stichprobe von 1 Prozent aller Seiten im Index oder dem Heap zurück. 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 bis DETAILED schrittweise ab, da 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 Index schnell messen möchten. Dies ist der schnellste Modus. Er gibt nicht für jede innere Knotenebene in der IN_ROW_DATA-Zuordnungseinheit des Index 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 ungültig sind, kann jedoch zu unerwarteten Ergebnissen führen. Wenn z. B. die Datenbank oder der Objektname nicht gefunden werden, da sie nicht vorhanden oder falsch geschrieben sind, 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 wird 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 AdventureWorks;
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 bei der Verwendung von DB_ID oder OBJECT_ID stets sicher, dass eine gültige ID zurückgegeben wird. Geben Sie zum Beispiel bei Verwendung von OBJECT_ID einen dreiteiligen Namen wie OBJECT_ID(N'AdventureWorks.Person.Address') an, oder testen Sie den von der Funktion zurückgegebenen Wert, bevor Sie ihn in der sys.dm_db_index_physical_stats-Funktion verwenden. In den folgenden Beispielen A und B wird eine sichere Möglichkeit zur Angabe von Datenbank- und Objekt-IDs dargestellt.

Erkennen der Fragmentierung

Fragmentierung wird durch Datenänderungen (mithilfe der Anweisungen INSERT, UPDATE oder DELETE) an der 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.

Der Algorithmus für die Berechnung der Fragmentierung ist in SQL Server 2008 genauer als in SQL Server 2000. Daher scheinen die Fragmentierungswerte höher zu sein. So wird eine Tabelle zum Beispiel in SQL Server 2000 nicht als fragmentiert angesehen, wenn sich Seite 11 und Seite 13, jedoch nicht Seite 12, im gleichen Block befinden. Das Zugreifen auf diese beiden Seiten erfordert jedoch zwei physische E/A-Operationen, sodass dies in SQL Server 2008 als Fragmentierung gewertet wird.

Die Fragmentierungsebene eines Index oder Heaps wird in der avg_fragmentation_in_percent-Spalte angezeigt. Für Heaps stellt dieser Wert die Blockfragmentierung des Heaps dar. Für Indizes stellt dieser Wert die logische Fragmentierung des Index dar. Im Gegensatz zu DBCC SHOWCONTIG wird bei den Fragmentierungsberechnungsalgorithmen in beiden Fällen Speicherplatz berücksichtigt, der sich über mehrere Dateien erstreckt, und sie sind deshalb genau.

Logische Fragmentierung

Dies ist der Prozentsatz der Seiten, die auf den Blattseiten eines Index nicht ordnungsgemäß einsortiert sind. Eine nicht ordnungsgemäß einsortierte Seite ist eine Seite, für die die nächste dem Index zugeordnete physische Seite 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äß einsortiert sind. Ein nicht ordnungsgemäß einsortierter 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. Zum Reduzieren dieser Werte 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 erneut.

  Durch das erneute Erstellen eines gruppierten Index 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 unteilbar 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 außerdem ohne Verlust bereits abgeschlossener Arbeitsschritte unterbrochen werden. Diese Methode hat den Nachteil, dass die Daten nicht so gut neu organisiert werden wie bei einer Indexneuerstellung. 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 erneut 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 beinhaltet, da es sich bei der Arbeitsauslastung in erster Linie um Singleton-Suchvorgänge handelt, hat das Beseitigen der Fragmentierung möglicherweise keine Auswirkungen. Weitere Informationen finden Sie auf dieser Microsoft-Website.

Hinweis
Das Ausführen von DBCC SHRINKFILE oder DBCC SHRINKDATABASE kann zur Fragmentierung führen, wenn ein Index während des Verkleinerungsvorgangs teilweise oder vollständig verschoben wird. Wenn daher ein Verkleinerungsvorgang ausgeführt werden muss, sollten Sie diesen nicht nach 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 erneut zu erstellen, werden die Daten nicht verschoben und verbleiben optimal an ihrer Position. Weitere Informationen zum Ausführen dieser Vorgänge finden Sie unter CREATE INDEX und DROP INDEX.

Komprimieren von LOB-Daten

Standardmäßig werden mit der ALTER INDEX REORGANIZE-Anweisung Seiten komprimiert, 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 gelöscht wird.

Durch das Neuorganisieren eines gruppierten Index werden alle im gruppierten Index enthaltenen LOB-Spalten komprimiert. Durch das Neuorganisieren eines nicht gruppierten Index werden alle LOB-Spalten komprimiert, die im Index als (eingeschlossene) Nichtschlüsselspalten enthalten 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 komprimiert, die dem gruppierten Index, der zugrunde liegenden Tabelle oder dem nicht gruppierten Index mit eingeschlossenen Spalten zugeordnet sind.

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. Daher sollte dieser Wert unter 100 % liegen, um Seitenteilungen zu reduzieren. Durch das erneute Erstellen eines Index 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 Füllfaktor. Darüber hinaus komprimiert ALTER INDEX REORGANIZE einen Index, indem versucht wird, Seiten auf den zuletzt angegebenen Wert von FILLFACTOR 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 Index. Größere Fragmente bedeuten, dass weniger Datenträger-E/A zum Lesen der gleichen Anzahl von Seiten erforderlich ist. 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. Daher sollte durch das Neuerstellen oder Neuorganisieren eines Index die Fragmentierung reduziert und die Fragmentgröße erhöht werden.

Berechtigungen

Die folgenden Berechtigungen sind erforderlich:

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

• VIEW DATABASE STATE-Berechtigung zum Zurückgeben von Informationen zu allen Objekten innerhalb der angegebenen Datenbank, indem der Objektplatzhalter @object\_id=NULL verwendet wird.

• VIEW SERVER STATE-Berechtigung zum Zurückgeben von Informationen zu allen Datenbanken, indem der Datenbank-Platzhalter @database\_id = NULL verwendet wird.

Nach dem Erteilen der VIEW DATABASE STATE-Berechtigung können alle Objekte in der Datenbank zurückgegeben werden, unabhängig von möglicherweise verweigerten CONTROL-Berechtigungen für bestimmte Objekte.

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

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

Beispiele

A. Zurückgeben von Informationen zu einer angegebenen Tabelle

Im folgenden Beispiel werden die Größe und Fragmentierungsstatistiken für alle Indizes und Partitionen der Person.Address-Tabelle in der AdventureWorks-Datenbank 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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 AdventureWorks-Datenbank zurückgegeben. Da die Tabelle LOB-Daten enthält, wird eine Zeile für die LOB_DATA-Zuordnungseinheit zurückgegeben, zusätzlich zur Zeile, die für IN_ROW_ALLOCATION_UNIT zurückgegeben wird und in der die Datenseiten des Heaps gespeichert werden. 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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 Platzhalter 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 Kompatibilitätsgraden von Datenbanken finden Sie unter sp_dbcmptlevel (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

Änderungsverlauf

Aktualisierter Inhalt
Erklärt, wie sich die Modi LIMITED, SAMPLED und DETAILED auf den Wert von compressed_page_count auswirken.

Siehe auch

Verweis

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)

Konzepte

Organisationsstruktur von Tabellen und Indizes