DBCC SHOWCONTIG (Transact-SQL)

  • Artikel

Aktualisiert: 15. September 2007

Zeigt Fragmentierungsinformationen für die Daten und Indizes der angegebenen Tabelle oder Sicht an.

ms175008.note(de-de,SQL.90).gifWichtig:
Dieses Feature wird in einer zukünftigen Version von Microsoft SQL Server entfernt. Verwenden Sie dieses Feature beim Entwickeln neuer Anwendungen nicht, und planen Sie das Ändern von Anwendungen, in denen es zurzeit verwendet wird. Verwenden Sie stattdessen sys.dm_db_index_physical_stats.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

Argumente

  • table_name | table_id | view_name | view_id
    Die Tabelle oder Sicht, für die die Fragmentierungsinformationen überprüft werden sollen. Wenn keine Angabe erfolgt, werden alle Tabellen und indizierten Sichten der aktuellen Datenbank überprüft. Verwenden Sie die OBJECT_ID-Funktion, um die ID der Tabelle oder Sicht abzurufen.
  • index_name | index_id
    Der Index, für den die Fragmentierungsinformationen überprüft werden sollen. Wenn keine Angabe erfolgt, wird der Basisindex der angegebenen Tabelle oder Sicht von der Anweisung verarbeitet. Verwenden Sie die sys.indexes-Katalogsicht, um die Index-ID abzurufen.
  • WITH
    Gibt Optionen für den von der DBCC-Anweisung zurückgegebenen Informationstyp an.
  • FAST
    Gibt an, ob ein schneller Scan des Indexes durchgeführt und Mindestinformationen ausgegeben werden sollen. Bei einem schnellen Scan werden die Seiten auf Blatt- oder Datenebene des Indexes nicht gelesen.
  • ALL_INDEXES
    Zeigt Ergebnisse für alle Indizes der angegebenen Tabellen und Sichten an, selbst wenn ein bestimmter Index angegeben ist.
  • TABLERESULTS
    Zeigt die Ergebnisse als Rowset mit zusätzlichen Informationen an.
  • ALL_LEVELS
    Nur aus Gründen der Abwärtskompatibilität beibehalten. Auch wenn ALL_LEVELS angegeben ist, wird nur die Blattebene des Indexes oder die Datenebene der Tabelle verarbeitet.
  • NO_INFOMSGS
    Unterdrückt alle Informationsmeldungen, die einen Schweregrad von 0 bis 10 haben.

Resultsets

In der folgenden Tabelle finden Sie eine Beschreibung der Informationen des Resultsets.

Statistik

Beschreibung

Gescannte Seiten

Anzahl der Seiten in der Tabelle oder im Index.

Gescannte Blöcke

Anzahl der Blöcke in der Tabelle oder im Index.

Blockwechsel

Gibt an, wie oft die DBCC-Anweisung von einem Block zu einem anderen gewechselt hat, während sie die Seiten der Tabelle oder des Indexes durchlaufen hat.

Seiten pro Block (Durchschnitt)

Die Anzahl der Seiten pro Block in der Seitenkette.

Scandichte [Bester Wert:Tatsächlicher Wert]

Gibt einen Prozentsatz an. Dies ist das Verhältnis von Bester Wert zu Tatsächlicher Wert. Dieser Wert beträgt 100, wenn alles zusammenhängend ist. Liegt der Wert unter 100, liegen einige Fragmentierungen vor.

Bester Wert ist die ideale Anzahl von Blockänderungen, wenn alles zusammenhängend verknüpft ist. Tatsächlicher Wert ist die tatsächliche Anzahl von Blockänderungen.

Logische Scanfragmentierung

Der Prozentsatz der Seiten außerhalb der Reihenfolge, die beim Scannen der Blattseiten eines Indexes zurückgegeben wurden. Diese Zahl ist für Heaps irrelevant. Eine Seite außerhalb der Reihenfolge ist eine Seite, bei der Zeiger für die nächste Seite in der aktuellen Blattseite nicht auf die nächste dem Index zugeordnete physikalische Seite verweist.

Blockscanfragmentierung

Der Prozentsatz der Blöcke, die beim Scannen der Blattseiten des Indexes außerhalb der Reihenfolge liegen. Diese Zahl ist für Heaps irrelevant. Ein Block außerhalb der Reihenfolge ist ein Block, für den der Block, der die aktuelle Seite eines Indexes enthält, physikalisch nicht der Block ist, der auf den Block mit der vorangegangenen Seite des Indexes folgt.

ms175008.note(de-de,SQL.90).gifHinweis:

Diese Zahl ist bedeutungslos, wenn der Index mehrere Dateien umfasst.

Bytes frei pro Seite (Durchschnitt)

Durchschnittliche Anzahl freier Bytes auf den gescannten Seiten. Je größer die Zahl ist, desto geringer sind die Seiten ausgelastet. Niedrigere Zahlen sind besser, wenn der Index nur über wenige zufällige Einfügungen verfügt. Diese Zahl wird ebenfalls von der Zeilengröße beeinflusst. Große Zeilen können eine höhere Zahl verursachen.

Mittlere Seitendichte (voll)

Durchschnittliche Seitendichte als Prozentsatz. Dieser Wert berücksichtigt die Zeilengröße. Daher informiert der Wert genauer darüber, wie voll die Seiten sind. Je höher die Prozentwerte, desto besser.

Wenn table_id und die Option FAST angegeben sind, gibt DBCC SHOWCONTIG ein Resultset mit nur den folgenden Spalten zurück.

  • Gescannte Seiten
  • Blockwechsel
  • Scandichte [Bester Wert:Tatsächlicher Wert]
  • Blockscanfragmentierung
  • Logische Scanfragmentierung

Wenn TABLERESULTS angegeben ist, gibt DBCC SHOWCONTIG die neun in der vorangegangenen Tabelle beschriebenen Spalten sowie die folgenden Spalten zurück.

Statistik

Beschreibung

Objektname

Der Name der verarbeiteten Tabelle oder Sicht.

ObjectId

ID des Objektnamens.

IndexName

Der Name des verarbeiteten Indexes. Für einen Heap lautet der Wert NULL.

IndexId

ID des Indexes. Für einen Heap lautet der Wert 0.

Level

Ebene des Indexes. Ebene 0 ist die Blatt- oder Datenebene des Indexes.

Für einen Heap ist die Ebene 0.

Pages

Anzahl von Seiten, die zu dieser Indexebene oder zum gesamten Heap gehören.

Rows

Anzahl der Daten- oder Indexdatensätze auf dieser Ebene des Indexes. Bei einem Heap ist dies die Anzahl von Datensätzen im gesamten Heap.

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

MinimumRecordSize

Mindestgröße des Datensatzes auf dieser Indexebene oder im gesamten Heap.

MaximumRecordSize

Maximale Datensatzgröße auf dieser Indexebene oder im gesamten Heap.

AverageRecordSize

Durchschnittliche Datensatzgröße auf dieser Indexebene oder im gesamten Heap.

ForwardedRecords

Anzahl der weitergeleiteten Datensätze auf dieser Indexebene oder im gesamten Heap.

Extents

Anzahl von Blöcken auf dieser Indexebene oder im gesamten Heap.

ExtentSwitches

Gibt an, wie oft die DBCC-Anweisung von einem Block zu einem anderen gewechselt hat, während sie die Seiten der Tabelle oder des Indexes durchlaufen hat.

AverageFreeBytes

Durchschnittliche Anzahl von freien Bytes auf den gescannten Seiten. Je größer die Zahl ist, desto weniger voll sind die Seiten. Niedrigere Zahlen sind besser, wenn der Index nur über wenige zufällige Einfügungen verfügt. Diese Zahl wird auch von der Zeilengröße beeinflusst. Große Zeilen können eine höhere Zahl verursachen.

AveragePageDensity

Durchschnittliche Seitendichte als Prozentsatz. Dieser Wert berücksichtigt die Zeilengröße. Daher informiert der Wert genauer darüber, wie voll die Seiten sind. Je höher die Prozentwerte, desto besser.

ScanDensity

Gibt einen Prozentsatz an. Dies ist das Verhältnis von BestCount zu ActualCount. Dieser Wert beträgt 100, wenn alles zusammenhängend ist. Liegt der Wert unter 100, liegen einige Fragmentierungen vor.

BestCount

Ist die ideale Anzahl von Blockänderungen, wenn alles zusammenhängend verknüpft ist.

ActualCount

Ist die tatsächliche Anzahl von Blockänderungen.

LogicalFragmentation

Der Prozentsatz der Seiten außerhalb der Reihenfolge, die beim Scannen der Blattseiten eines Indexes zurückgegeben wurden. Diese Zahl ist für Heaps irrelevant. Eine Seite außerhalb der Reihenfolge ist eine Seite, bei der Zeiger für die nächste Seite** in der aktuellen Blattseite nicht auf die nächste dem Index zugeordnete physikalische Seite verweist.

ExtentFragmentation

Der Prozentsatz der Blöcke, die beim Scannen der Blattseiten des Indexes außerhalb der Reihenfolge liegen. Diese Zahl ist für Heaps irrelevant. Ein Block außerhalb der Reihenfolge ist ein Block, für den der Block, der die aktuelle Seite eines Indexes enthält, physikalisch nicht der Block ist, der auf den Block mit der vorangegangenen Seite des Indexes folgt.

ms175008.note(de-de,SQL.90).gifHinweis:

Diese Zahl ist bedeutungslos, wenn der Index mehrere Dateien umfasst.

Wenn WITH TABLERESULTS und FAST angegeben sind, ist das Resultset dasselbe wie bei Angabe von WITH TABLERESULTS mit Ausnahme der folgenden Spalten, die NULL-Werte enthalten:

Rows

Extents

MinimumRecordSize

AverageFreeBytes

MaximumRecordSize

AveragePageDensity

AverageRecordSize

ExtentFragmentation

ForwardedRecords

 

Hinweise

Die DBCC SHOWCONTIG-Anweisung durchläuft die Seitenkette des angegebenen Indexes auf Blattebene, wenn index_id angegeben wurde. Wenn nur table_id angegeben wurde oder wenn index_id den Wert 0 hat, werden die Datenseiten der angegebenen Tabelle gescannt. Der Vorgang erfordert nur eine beabsichtigte gemeinsame Sperre (intent-shared, IS) für die Tabelle. Auf diese Weise können alle Aktualisierungen und Einfügungen ausgeführt werden, die keine exklusive Tabellensperre (X) erfordern. Dies schafft einen Kompromiss zwischen der Ausführungsgeschwindigkeit ohne Verringerung der Parallelität und der Anzahl der zurückgegebenen Statistiken. Wenn der Befehl jedoch nur zum Messen der Fragmentierung dient, wird die Verwendung der WITH FAST-Option für eine optimale Leistung empfohlen. Bei einem schnellen Scan werden die Seiten auf Blatt- oder Datenebene des Indexes nicht gelesen. Die WITH FAST-Option kann nicht für einen Heap angewendet werden.

Änderungen in SQL Server 2005

Der Algorithmus für die Berechnung der Fragmentierung ist in SQL Server 2005 genauer als in SQL Server 2000. Dies führt dazu, dass die Fragmentierungswerte höher ausfallen. So wird eine Tabelle zum Beispiel in SQL Server 2000 nicht als fragmentiert berücksichtigt, wenn sich Seite 11 und Seite 13 im selben Block befinden, nicht jedoch Seite 12. Das Zugreifen auf diese beiden Seiten erfordert jedoch zwei physikalische E/A-Vorgänge, sodass dies in SQL Server 2005 als Fragmentierung gewertet wird.

Mit DBCC SHOWCONTIG werden keine ntext-, text- und image-Datentypen angezeigt. Der Grund ist, dass Textindizes (Index-ID 255 in SQL Server 2000), die Text- und Bilddaten speichern, in SQL Server 2005 nicht mehr verwendet werden. Weitere Informationen zur Index-ID 255 finden Sie unter sys.sysindexes (Transact-SQL).

Zudem unterstützt DBCC SHOWCONTIG einige neue Features von SQL Server 2005 nicht. Zum Beispiel:

  • Wenn die angegebene Tabelle oder der angegebene Index partitioniert ist, wird durch DBCC SHOWCONTIG nur die erste Partition der angegebenen Tabelle oder des angegebenen Indexes angezeigt.
  • Mit DBCC SHOWCONTIG werden keine Zeilenüberlauf-Speicherinformationen und andere neue Datentypen außerhalb von Zeilen wie z. B. nvarchar(max), varchar(max), varbinary(max) und xml angezeigt.

Alle neuen Features in SQL Server 2005 werden vollständig von der dynamischen Verwaltungssicht sys.dm_db_index_physical_stats unterstützt.

Tabellenfragmentierung

DBCC SHOWCONTIG findet heraus, ob die Tabelle stark fragmentiert ist. Eine Tabellenfragmentierung wird durch Datenänderungen (mithilfe der Anweisungen INSERT, UPDATE oder DELETE) in der Tabelle hervorgerufen. Da diese Änderungen normalerweise nicht gleichmäßig über alle Zeilen der Tabelle verteilt vorgenommen werden, kann es mit der Zeit zu einer unterschiedlichen Auslastung der einzelnen Seiten kommen. Diese Tabellenfragmentierung kann bei Abfragen, bei denen eine Tabelle teilweise oder ganz gescannt wird, zu zusätzlichen Seitenlesevorgängen führen. Dies behindert das parallele Scannen von Daten.

Bei einem stark fragmentierten Index gibt es folgende Möglichkeiten, die Fragmentierung zu reduzieren:

  • Löschen und erneutes Erstellen eines gruppierten Indexes.
    Durch das erneute Erstellen eines gruppierten Indexes werden die Daten reorganisiert und die Datenseiten ausgelastet. Die Auslastung kann über die Option FILLFACTOR in CREATE INDEX konfiguriert werden. Die Nachteile dieser Methode liegen darin, dass der Index während des Löschens und erneuten Erstellens offline und der Vorgang unteilbar ist. Bei einer Unterbrechung der Indexerstellung wird der Index nicht neu erstellt.
  • Neuordnen der Indexseiten auf Blattebene in einer logischen Reihenfolge.
    Sortieren Sie mit ALTER INDEX…REORGANIZE die Indexseiten auf Blattebene in einer logischen Reihenfolge neu. Da es sich hierbei um einen Onlinevorgang handelt, steht der Index bei Ausführung der Anweisung zur Verfügung. Der Vorgang kann auch ohne Verlust bereits abgeschlossener Arbeitsschritte unterbrochen werden. Diese Methode hat den Nachteil, dass mit ihr die Daten weniger gut reorganisiert werden als durch das Löschen oder erneute Erstellen eines gruppierten Indexes.
  • Erneutes Erstellen des Indexes.
    Verwenden Sie ALTER INDEX mit REBUILD, um den Index neu zu erstellen. Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Die Statistiken Bytes frei pro Seite (Durchschnitt) und Mittlere Seitendichte (voll) im Resultset geben die Auslastung von Indexseiten an. Für einen Index mit nur wenigen zufälligen Einfügungen sollte die Anzahl der Bytes frei pro Seite (Durchschnitt) gering und die Mittlere Seitendichte (voll) hoch sein. Durch Löschen und erneutes Erstellen eines Indexes mithilfe der angegebenen FILLFACTOR-Option können diese Statistiken verbessert werden. Außerdem können durch ALTER INDEX mit REORGANIZE die Statistiken verbessert werden, da hierbei ein Index unter Berücksichtigung seines FILLFACTOR komprimiert wird.

ms175008.note(de-de,SQL.90).gifHinweis:
Ein Index mit zahlreichen zufälligen Einfügungen und sehr vollen Seiten weist eine erhöhte Anzahl von Seitenteilungen auf. Dadurch nimmt die Fragmentierung zu.

Die Fragmentierungsebene eines Indexes kann folgendermaßen festgelegt werden:

  • Durch Vergleichen der Werte für Blockwechsel und Gescannte Blöcke.
    Die Differenz der Werte für Blockwechsel und Gescannte Blöcke sollte so gering wie möglich sein. Dieses Verhältnis gibt den Wert für die Scandichte an. Dieser Wert sollte so hoch wie möglich sein. Er kann durch Verringern der Indexfragmentierung verbessert werden.
    ms175008.note(de-de,SQL.90).gifHinweis:
    Diese Methode funktioniert nicht, wenn sich der Index über mehrere Dateien erstreckt.
  • Grundlegendes zu den Werten Logische Scanfragmentierung und Blockscanfragmentierung.
    Die Fragmentierungsebene der Tabelle wird am besten durch den Wert Logische Scanfragmentierung und in geringerem Maße durch den Wert Blockscanfragmentierung angezeigt. Beide Werte sollten so nahe wie möglich bei Null liegen, ein Wert von 0 % bis 10 % ist jedoch akzeptabel.
    ms175008.note(de-de,SQL.90).gifHinweis:
    Der Wert Blockscanfragmentierung ist hoch, wenn sich der Index über mehrere Dateien erstreckt. Sie können diese Werte verringern, wenn Sie die Indexfragmentierung reduzieren.

Berechtigungen

Sie müssen der Besitzer der Tabelle sein oder ein Mitglied der festen Serverrolle sysadmin bzw. der festen Datenbankrolle db_owner oder db_ddladmin.

Beispiele

A. Anzeigen von Fragmentierungsinformationen für eine Tabelle

Im folgenden Beispiel werden die Fragmentierungsinformationen für die Tabelle Employee angezeigt.

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. Abrufen der Tabellen-ID mit OBJECT_ID und der Index-ID mit "sys.indexes"

Im folgenden Beispiel werden OBJECT_ID und die sys.indexes-Katalogsicht verwendet, um die Tabellen-ID und die Index-ID für den AK_Product_Name-Index der Production.Product -Tabelle in der AdventureWorks -Datenbank abzurufen.

USE AdventureWorks;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id 
FROM sys.indexes
WHERE object_id = @id 
   AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

C. Anzeigen eines abgekürzten Resultsets für eine Tabelle

Im folgenden Beispiel wird ein abgekürztes Resultset für die Product -Tabelle in der AdventureWorks -Datenbank zurückgegeben.

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

D. Anzeigen des vollständigen Resultsets für jeden Index aller Tabellen in einer Datenbank

Im folgenden Beispiel wird ein vollständiges Resultset für jeden Index aller Tabellen in der AdventureWorks -Datenbank zurückgegeben.

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. Defragmentieren der Indizes in einer Datenbank mithilfe von DBCC SHOWCONTIG und DBCC INDEXDEFRAG

Im folgenden Beispiel wird ein einfacher Weg vorgestellt, alle Indizes in einer Datenbank, deren Fragmentierung einen deklarierten Schwellenwert überschreitet, zu defragmentieren.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

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

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

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

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Siehe auch

Verweis

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
DBCC (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)

Andere Ressourcen

Speicherplatzreservierung und -wiederverwendung

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

15. September 2007
Geänderter Inhalt:
  • Im Abschnitt "Hinweise" wurden die Informationen zum Sperrvorgang korrigiert. In SQL Server 2005 verwendet dieser Befehl nur eine beabsichtigte gemeinsame Sperre (intent-shared, IS) für die Tabelle und keine S-Sperre, wie zuvor angegeben.
  • Die Definition der Spalte Zeilen für Heaps wurde verdeutlicht.

17. Juli 2006
Neuer Inhalt:
  • Informationen zum Algorithmus für die Berechnung der Fragmentierung im Abschnitt "Änderungen in SQL Server 2005" wurden hinzugefügt.

05. Dezember 2005
Geänderter Inhalt:
  • Die Beschreibung von LogicalFragmentation wurde korrigiert.