sys.dm_db_index_physical_stats

Aktualisiert: 15. September 2007

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 Tabellen- und Indexarchitektur.

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. Der Standardwert 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. Der Standardwert 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 Indexes. index_id ist vom Datentyp int. Gültige Eingaben sind die ID eines Indexes, 0, falls object_id ein Heap ist, NULL, -1 oder DEFAULT. Der Standardwert 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 Indexes bzw. Heaps, NULL, 0 oder DEFAULT. Der Standardwert 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. Eine gültige Eingabe ist DEFAULT, NULL, LIMITED, SAMPLED oder DETAILED. Der Standardwert (NULL) ist LIMITED.

Zurückgegebene Tabelle

Spaltenname

Datentyp

Beschreibung

database_id

smallint

Datenbank-ID der Tabelle oder Sicht.

object_id

int

Objekt-ID der Tabelle oder Sicht mit dem Index.

index_id

int

Index-ID eines Indexes.

0 = Heap.

partition_number

int

1-basierte Partitionsnummer im besitzenden Objekt; eine Tabelle, eine Sicht oder ein Index.

1 = Nicht partitionierter Index oder Heap.

index_type_desc

nvarchar(60)

Beschreibung des Indextyps:

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

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 Indexes.

0 für Indexblattebene, Heaps und LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten.

Werte größer 0 sind für Nicht-Indexblattebenen. index_level weist auf der Stammebene eines Indexes den höchsten Wert auf.

Die Nichtblattebenen 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 Nichtblattebenen eines Indexes 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 Nichtblattebenen eines Indexes 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.

Für LOB_DATA- oder ROW_OVERFLOW_DATA-Zuordnungseinheiten die Gesamtanzahl von Datensätzen in der gesamten Zuordnungseinheit.

NULL, wenn mode = LIMITED.

Hinweis:

Bei einem Heap stimmt die Anzahl der von dieser Funktion zurückgegebenen Datensätze möglicherweise nicht mit der Anzahl von Zeilen überein, die durch Ausführen von SELECT COUNT(*) für den Heap zurückgegeben wird. Das liegt daran, dass eine Zeile möglicherweise mehrere Datensätze enthält. In manchen Aktualisierungssituationen verfügt ein einzelner Heap als Ergebnis des Aktualisierungsvorgangs möglicherweise über einen weiterleitenden und einen weitergeleiteten Datenastz. Außerdem werden die größten LOB-Zeilen im LOB_DATA-Speicher in mehrere Datensätze aufgeteilt.

ghost_record_count

bigint

Anzahl von inaktiven Datensätzen, die durch den Cleanuptask für inaktive Datensätze in der Zuordnungseinheit entfernt werden können.

0 für Nichtblattebenen eines Indexes in der IN_ROW_DATA-Zuordnungseinheit.

NULL, 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 Nichtblattebenen eines Indexes 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.

Hinweise

Die dynamische Verwaltungsfunktion sys.dm_db_index_physical_stats ersetzt die DBCC SHOWCONTIG-Anweisung. Diese dynamische Verwaltungsfunktion akzeptiert keine abhängigen Parameter von CROSS APPLY und OUTER APPLY.

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 Indexes bestehen. Für sys.dm_db_index_physical_stats ist nur eine beabsichtigte gemeinsame 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. Dieser Modus durchsucht alle Seiten nach einem Heap, jedoch nur die Seiten auf der übergeordneten Ebene für einen Index, also die Seiten über der Blattebene.

Der Modus SAMPLED gibt Statistiken basierend auf einer Stichprobe von 1 % aller Seiten im Index oder 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, weil im jeweils nächsten Modus mehr Arbeitsschritte ausgeführt werden. Verwenden Sie den Modus LIMITED, wenn Sie die Größe oder die Fragmentierungsebene einer Tabelle oder eines Indexes schnell messen möchten. Dies ist der schnellste Modus und gibt keine Zeile für jede Nichtblattebene 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 ungültig sind, kann jedoch zu unerwarteten Ergebnissen führen. Falls z. B. die Datenbank oder der Objektname nicht gefunden werden, weil 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 Verwendung von DB_ID oder OBJECT_ID immer sicher, dass eine gültige ID zurückgegeben wird. Geben Sie beispielsweise bei Verwendung von OBJECT_ID einen dreiteiligen Namen an, wie OBJECT_ID(N'AdventureWorks.Person.Address'), oder testen Sie den von den Funktionen zurückgegebenen Wert, bevor Sie ihn 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) 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 2005 genauer als in SQL Server 2000. Daher scheinen die Fragmentierungswerte höher zu sein. In SQL Server 2000 gilt eine Tabelle beispielsweise nicht als fragmentiert, wenn sich zwar Seite 11 und Seite 13 im gleichen Block befinden, Seite 12 jedoch nicht. Für den Zugriff auf diese zwei Seiten sind jedoch zwei physikalische E/A-Vorgänge erforderlich, daher zählt dies in SQL Server 2005 als Fragmentierung.

Die Fragmentierungsebene eines Indexes 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 Indexes 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 richtig.

Logische Fragmentierung

Dies ist der Prozentsatz der Seiten, die auf den Blattseiten eines Indexes nicht ordnungsgemäß einsortiert sind. Eine nicht ordnungsgemäß einsortierte Seite ist eine Seite, für die die nächste dem Index zugeordnete physikalische 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, physikalisch 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 Fragmentierungsgrades in einem Index finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

Reduzieren der Fragmentierung in einem Index

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

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

Die Fragmentierung alleine ist kein ausreichender Grund, um einen Index neu zu organisieren oder neu zu erstellen. Durch die Fragmentierung wird in erster Linie der Read-Ahead-Durchsatz von Seiten während Indexscans reduziert. Dies verursacht langsamere Antwortzeiten. Falls die Abfragearbeitsauslastung für eine fragmentierte Tabelle oder einen fragmentierten Index keine Scans beinhaltet, weil 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, falls ein Index während des Verkleinerungsvorgangs teilweise oder vollständig verschoben wird. Wenn deshalb 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 neu 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 komprimiert die ALTER INDEX REORGANIZE-Anweisung Seiten, die LOB-Daten (Large Object) enthalten. Die Zuordnung von LOB-Seiten wird nicht aufgehoben, wenn sie leer sind. Deshalb kann durch das Komprimieren dieser Daten der Speicherplatz optimiert werden, falls viele LOB-Daten gelöscht wurden oder eine LOB-Spalte gelöscht wird.

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

Auswerten der Speicherplatzverwendung

Die avg_page_space_used_in_percent-Spalte gibt den Seitenfüllgrad an. Für eine optimale Speicherplatzverwendung sollte dieser Wert für einen Index ohne viele zufällige Einfügungen nahe bei 100 % liegen. Ein Index mit zahlreichen zufälligen Einfügungen und sehr vollen Seiten verfügt jedoch über eine höhere Anzahl von Seitenteilungen. Dadurch entsteht mehr Fragmentierung. Deshalb sollte dieser Wert unter 100 % liegen, um Seitenteilungen zu reduzieren. Durch das erneute Erstellen 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 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 Indexes. 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. Deshalb sollte durch das Neuerstellen oder Neuorganisieren eines Indexes 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.

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

Siehe auch

Verweis

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

Andere Ressourcen

Organisationsstruktur von Tabellen und Indizes

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version	Verlauf
15. September 2007	Geänderter Inhalt: Es wurde ein Hinweis in record_count hinzugefügt, der erläutert, warum die Anzahl von zurückgegebenen Datensätzen möglicherweise nicht gleich der Anzahl von Zeilen in einem Heap ist. Die Definition von forwarded_record_count wurde verbessert.
17. Juli 2006	Neuer Inhalt: Die Informationen zum Algorithmus für die Berechnung der Fragmentierung im Abschnitt "Erkennen der Fragmentierung" wurden hinzugefügt. Die Anforderungen an den Kompatibilitätsgrad der Datenbank für die Verwendung von DB_ID() im ersten Parameter wurden verdeutlicht.
14. April 2006	Geänderter Inhalt: Der Syntax für database_id, object_id, und partition_number wurden die Eingabewerte 0 und DEFAULT hinzugefügt. Der Syntax für index_id wurden die Eingabewerte -1 und DEFAULT hinzugefügt. Beispiel D wurde korrigiert.
05. Dezember 2005	Neuer Inhalt: Abschnitt "Verwenden von Systemfunktionen zum Angeben von Parameterwerten" hinzugefügt. Geänderter Inhalt: Berechtigungsabschnitt geändert. Beschreibung der logischen Fragmentierung korrigiert.