sys.dm_db_index_physical_stats (Transact-SQL)

  • Artikel

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. Gibt keine Informationen zu speicheroptimierten xVelocity-ColumnStore-Indizes zurück.

Wichtiger HinweisWichtig

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

Themenlink (Symbol) 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 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 oder höher sein.

  • object_id | NULL | 0 | DEFAULT
    Die Objekt-ID der Tabelle oder Sicht mit dem Index. object_id ist 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 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 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 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

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.

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.

HinweisHinweis

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 Updatesituationen eine einzelne Heapzeile möglicherweise über einen Weiterleitungsdatensatz und einen weitergeleiteten Datensatz als Ergebnis des Updates verfügen. Außerdem werden die meisten großen LOB-Zeilen im LOB_DATA-Speicher in mehrere Datensätze geteilt.

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 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 Momentaufnahme-Isolationstransaktion 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 eines Updates auf, wenn nicht genügend Speicherplatz vorhanden ist, um die neue Zeile am ursprünglichen Speicherort zu speichern.)

NULL für eine beliebige Zuordnungseinheit außer IN_ROW_DATA-Zuordnungseinheiten für einen Heap.

NULL für Heaps, wenn mode = LIMITED.

compressed_page_count

bigint

Die Anzahl der komprimierten Seiten.

  • Für Heaps sind neu zugeordnete Seiten nicht mit PAGE seitenkomprimiert. Ein Heap ist eine PAGE, die unter den folgenden beiden besonderen Bedingungen komprimiert wird: wenn ein Massenimport von Daten erfolgt oder der Heap neu erstellt wird. Typische DML-Vorgänge, die Seitenzuordnungen hervorrufen, werden nicht PAGE-komprimiert. Erstellen Sie einen Heap neu, wenn der compressed_page_count-Wert den gewünschten Schwellenwert überschreitet.

  • Für Tabellen mit gruppiertem Index gibt der compressed_page_count-Wert die Wirksamkeit der PAGE-Komprimierung an.

Hinweise

Die dynamische Verwaltungsfunktion vom Typ 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 freigegebene Tabellensperre (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. 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 im Modus LIMITED gescannt.

Mit dem Modus LIMITED ist compressed_page_count NULL, da von Datenbankmodul nur Nicht- Blattseiten der B-Struktur und die IAM- und PFS-Seiten des Heaps gescannt werden. 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 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. 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 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 AdventureWorks2012;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Bewährte Methode

Stellen Sie stets sicher, dass bei der Verwendung von DB_ID oder OBJECT_ID eine gültige ID zurückgegeben wird. Geben Sie zum Beispiel bei Verwendung von OBJECT_ID einen dreiteiligen Namen wie OBJECT_ID(N'AdventureWorks2012.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 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.

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

HinweisHinweis

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

Reduzieren der Fragmentierung in einem Heap

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

VorsichtshinweisVorsicht

Durch das Erstellen oder Verwerfen eines gruppierten Indexes einer Tabelle werden alle nicht gruppierten Indexes der Tabelle doppelt neu erstellt.

Komprimieren von LOB-Daten

Standardmäßig komprimiert die ALTER INDEX REORGANIZE-Anweisung Seiten, die LOB-Daten (Large Object) enthalten. Die Zuordnung von LOB-Seiten wird nicht aufgehoben, wenn sie leer sind. Deshalb kann durch das Komprimieren dieser Daten der Speicherplatz optimiert werden, falls viele LOB-Daten gelöscht wurden oder eine LOB-Spalte 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 zeigt Seitenauslastung 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 Angeben des Füllfaktors für einen Index. Darüber hinaus komprimiert ALTER INDEX REORGANIZE einen Index, indem versucht wird, Seiten auf den zuletzt angegebenen Wert von FILLFACTOR zu füllen. Hierdurch wird der Wert in avg_space_used_in_percent erhöht. 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. 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.

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 AdventureWorks2012-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'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B.Zurückgeben von Informationen zu einem Heap

Im folgenden Beispiel werden alle Statistiken für den dbo.DatabaseLog-Heap in der AdventureWorks2012-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'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.dbo.DatabaseLog');
IF @object_id IS NULL 
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C.Zurückgeben von Informationen zu allen Datenbanken

Im folgenden Beispiel werden alle Statistiken für alle Tabellen und Indizes innerhalb der Instanz von SQL Server zurückgegeben, indem der 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 ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

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

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

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

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

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

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

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)