DBCC SHOWCONTIG (Transact-SQL)

Dotyczy:programu SQL ServerAzure SQL Managed Instance

Wyświetla informacje o fragmentacji danych i indeksów określonej tabeli lub widoku.

Ważny

Ta funkcja zostanie usunięta w przyszłej wersji programu SQL Server. Unikaj używania tej funkcji w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które obecnie korzystają z tej funkcji. Zamiast tego użyj sys.dm_db_index_physical_stats.

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje

Transact-SQL konwencje składni

Składnia

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

Argumenty

table_name | table_id | view_name | view_id

Tabela lub widok do sprawdzania informacji o fragmentacji. Jeśli nie zostanie określony, sprawdzane są wszystkie tabele i widoki indeksowane w bieżącej bazie danych. Aby uzyskać identyfikator tabeli lub widoku, użyj funkcji OBJECT_ID.

index_name | index_id

Indeks do sprawdzania informacji o fragmentacji. Jeśli nie zostanie określony, instrukcja przetwarza indeks podstawowy dla określonej tabeli lub widoku. Aby uzyskać identyfikator indeksu, użyj widoku katalogu sys.indexes.

Z

Określa opcje typu informacji zwracanych przez instrukcję DBCC.

SZYBKI

Określa, czy przeprowadzić szybkie skanowanie indeksu i uzyskać minimalne informacje. Szybkie skanowanie nie odczytuje stron na poziomie liścia ani danych indeksu.

ALL_INDEXES

Wyświetla wyniki dla wszystkich indeksów dla określonych tabel i widoków, nawet jeśli określony indeks jest określony.

TABLERESULTS

Wyświetla wyniki jako zestaw wierszy z dodatkowymi informacjami.

ALL_LEVELS

Zachowano tylko zgodność z poprzednimi wersjami. Nawet jeśli ALL_LEVELS jest określona, przetwarzany jest tylko poziom liści indeksu lub poziom danych tabeli.

NO_INFOMSGS

Pomija wszystkie komunikaty informacyjne, które mają poziomy ważności od 0 do 10.

Zestawy wyników

W poniższej tabeli opisano informacje w zestawie wyników.

Statystyki	Opis
strony zeskanowane	Liczba stron w tabeli lub indeksie.
Zakresy skanowane	Liczba zakresów w tabeli lub indeksie.
przełączniki zakresu	Ile razy instrukcja DBCC została przeniesiona z jednego zakresu do innego, podczas gdy instrukcja przechodziła przez strony tabeli lub indeksu.
średnia liczba stron na zakresu	Liczba stron na zakres w łańcuchu stron.
gęstość skanowania [najlepsza liczba: rzeczywista liczba]	Wartość procentowa. Jest to stosunek najlepszych do liczby rzeczywistej. Ta wartość to 100, jeśli wszystko jest ciągłe; jeśli ta wartość jest mniejsza niż 100, istnieje fragmentacja. najlepsza liczba jest idealną liczbą zmian zakresu, jeśli wszystko jest stale połączone. rzeczywista liczba to rzeczywista liczba zmian zakresu.
fragmentacji skanowania logicznego	Procent stron poza kolejnością zwracanych ze skanowania stron liści indeksu. Ta liczba nie jest istotna dla stert. Strona poza kolejnością to strona, dla której następna strona fizyczna przydzielona do indeksu nie jest stroną wskazywaną przez następnystronicowania e wskaźnik w bieżącej stronie liścia.
fragmentacji skanowania zakresu	Procent zakresów poza kolejnością podczas skanowania stron liści indeksu. Ta liczba nie jest istotna dla stert. Zakres poza kolejnością jest taki, dla którego zakres zawierający bieżącą stronę indeksu nie jest fizycznie następnym zakresem po zakresie, który zawiera poprzednią stronę indeksu. Uwaga: Ta liczba jest bez znaczenia, gdy indeks obejmuje wiele plików.
średnia liczba bajtów na strony	Średnia liczba wolnych bajtów na zeskanowanych stronach. Większa liczba, tym mniej pełne są strony. Niższe liczby są lepsze, jeśli indeks nie będzie miał wielu losowych wstawień. Ta liczba ma również wpływ na rozmiar wiersza; duży rozmiar wiersza może spowodować większą liczbę.
średnia gęstość strony (pełna)	Średnia gęstość stron jako wartość procentowa. Ta wartość uwzględnia rozmiar wiersza. W związku z tym wartość jest dokładniejszym wskazaniem, jak pełne są strony. Tym większa wartość procentowa, tym lepiej.

Po określeniu table_id i fast DBCC SHOWCONTIG zwraca zestaw wyników z tylko następującymi kolumnami:

• strony zeskanowane
• przełączniki zakresu
• gęstość skanowania [najlepsza liczba:rzeczywista liczba]
• fragmentacji skanowania zakresu
• fragmentacji skanowania logicznego

Po określeniu TABLERESULTSDBCC SHOWCONTIG zwraca następujące kolumny, a także dziewięć kolumn opisanych w poprzedniej tabeli.

Statystyki	Opis
nazwa obiektu	Nazwa przetworzonej tabeli lub widoku.
ObjectId	Identyfikator nazwy obiektu.
IndexName	Nazwa przetworzonego indeksu. Wartość NULL dla sterta.
IndexId	Identyfikator indeksu. 0 dla sterta.
poziom	Poziom indeksu. Poziom 0 to poziom liścia lub danych indeksu. Poziom wynosi 0 dla sterta.
Pages	Liczba stron, które składają się na ten poziom indeksu lub całego sterta.
wierszy	Liczba rekordów danych lub indeksów na tym poziomie indeksu. W przypadku sterta ta wartość jest liczbą rekordów danych w całej stercie. W przypadku sterta liczba rekordów zwróconych z tej funkcji może nie być zgodna z liczbą wierszy zwracanych przez uruchomienie SELECT COUNT(*) względem sterta. Dzieje się tak, ponieważ wiersz może zawierać wiele rekordów. Na przykład w niektórych sytuacjach aktualizacji pojedynczy wiersz sterta może mieć rekord przekazywania i przekazany rekord w wyniku operacji aktualizacji. Ponadto większość dużych wierszy biznesowych jest podzielona na wiele rekordów w magazynie LOB_DATA.
MinimumRecordSize	Minimalny rozmiar rekordu na tym poziomie indeksu lub całej sterty.
MaximumRecordSize	Maksymalny rozmiar rekordu na tym poziomie indeksu lub całego sterty.
AverageRecordSize	Średni rozmiar rekordu na tym poziomie indeksu lub całej sterty.
forwardedRecords	Liczba przekazanych rekordów na tym poziomie indeksu lub całego sterta.
Zakresy	Liczba zakresów na tym poziomie indeksu lub całego sterta.
ExtentSwitches	Ile razy instrukcja DBCC została przeniesiona z jednego zakresu do innego, podczas gdy instrukcja przechodziła przez strony tabeli lub indeksu.
averageFreeBytes	Średnia liczba wolnych bajtów na zeskanowanych stronach. Większa liczba, tym mniej pełne są strony. Niższe liczby są lepsze, jeśli indeks nie będzie miał wielu losowych wstawień. Ta liczba ma również wpływ na rozmiar wiersza; duży rozmiar wiersza może spowodować większą liczbę.
AveragePageDensity	Średnia gęstość stron jako wartość procentowa. Ta wartość uwzględnia rozmiar wiersza. W związku z tym wartość jest dokładniejszym wskazaniem, jak pełne są strony. Tym większa wartość procentowa, tym lepiej.
ScanDensity	Wartość procentowa. Jest to stosunek BestCount do ActualCount. Ta wartość to 100, jeśli wszystko jest ciągłe; jeśli ta wartość jest mniejsza niż 100, istnieje fragmentacja.
BestCount	Idealna liczba zmian zakresu, jeśli wszystko jest stale połączone.
ActualCount	Rzeczywista liczba zmian zakresu.
LogicalFragmentation	Procent stron poza kolejnością zwracanych ze skanowania stron liści indeksu. Ta liczba nie jest istotna dla stert. Strona poza kolejnością to strona, dla której następna strona fizyczna przydzielona do indeksu nie jest stroną wskazywaną przez wskaźnik następnej strony na bieżącej stronie liścia.
ExtentFragmentation	Procent zakresów poza kolejnością podczas skanowania stron liści indeksu. Ta liczba nie jest istotna dla stert. Zakres poza kolejnością jest taki, dla którego zakres zawierający bieżącą stronę indeksu nie jest fizycznie następnym zakresem po zakresie, który zawiera poprzednią stronę indeksu. Uwaga: Ta liczba jest bez znaczenia, gdy indeks obejmuje wiele plików.

Po określeniu WITH TABLERESULTS i FAST zestaw wyników jest taki sam jak w przypadku określenia WITH TABLERESULTS, z wyjątkiem następujących kolumn będzie zawierać wartości null:

Wierszy	Zakresów
MinimumRecordSize	averageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
forwardedRecords

Uwagi

Instrukcja DBCC SHOWCONTIG przechodzi przez łańcuch stron na poziomie liści określonego indeksu po określeniu index_id. Jeśli określono tylko table_id lub jeśli index_id wynosi 0, skanowane są strony danych określonej tabeli. Operacja wymaga tylko blokady tabeli udostępnionej intencji (IS). Dzięki temu można wykonywać wszystkie aktualizacje i wstawki, z wyjątkiem tych, które wymagają blokady tabeli na wyłączność (X). Pozwala to na kompromis między szybkością wykonywania a brakiem zmniejszenia współbieżności w stosunku do liczby zwracanych statystyk. Jeśli jednak polecenie jest używane tylko do pomiaru fragmentacji, zalecamy użycie opcji WITH FAST w celu uzyskania optymalnej wydajności. Szybkie skanowanie nie odczytuje stron na poziomie liścia ani danych indeksu. Opcja WITH FAST nie ma zastosowania do sterta.

Ograniczenia

DBCC SHOWCONTIG nie wyświetla danych z ntext, tekstemi obrazami typów danych. Dzieje się tak, ponieważ indeksy tekstowe, które przechowują już dane tekstowe i dane obrazu, nie istnieją.

Ponadto DBCC SHOWCONTIG nie obsługuje niektórych nowych funkcji. Na przykład:

• Jeśli określona tabela lub indeks jest partycjonowany, DBCC SHOWCONTIG wyświetla tylko pierwszą partycję określonej tabeli lub indeksu.
• DBCC SHOWCONTIG nie wyświetla informacji o magazynie przepełnienia wiersza ani innych nowych typów danych poza wierszem, takich jak nvarchar(max), varchar(max), varbinary(max)i xml.
• Indeksy przestrzenne nie są obsługiwane przez DBCC SHOWCONTIG.

Wszystkie nowe funkcje są w pełni obsługiwane przez sys.dm_db_index_physical_stats (Transact-SQL) dynamiczny widok zarządzania.

Fragmentacja tabeli

DBCC SHOWCONTIG określa, czy tabela jest mocno pofragmentowana. Fragmentacja tabeli odbywa się przez proces modyfikacji danych (instrukcje INSERT, UPDATE i DELETE) względem tabeli. Ponieważ te modyfikacje nie są zwykle równomiernie rozłożone między wiersze tabeli, pełna cała każda strona może się różnić w czasie. W przypadku zapytań, które skanują część lub całą tabelę, taka fragmentacja tabeli może spowodować dodatkowe operacje odczytu strony. Utrudnia to równoległe skanowanie danych.

Gdy indeks jest mocno pofragmentowany, dostępne są następujące opcje zmniejszenia fragmentacji:

• Upuść i ponownie utwórz indeks klastrowany.

  Ponowne utworzenie indeksu klastrowanego powoduje reorganizację danych i powoduje pełne strony danych. Poziom pełnej można skonfigurować przy użyciu opcji FILLFACTOR w CREATE INDEX. Wadą tej metody jest to, że indeks jest w trybie offline podczas cyklu upuszczania lub ponownego tworzenia, a operacja jest niepodzielna. Jeśli tworzenie indeksu zostanie przerwane, indeks nie zostanie utworzony ponownie.

• Zmień kolejność stron na poziomie liścia indeksu w kolejności logicznej.

  Użyj ALTER INDEX...REORGANIZE, aby zmienić kolejność stron na poziomie liścia indeksu w kolejności logicznej. Ponieważ ta operacja jest operacją online, indeks jest dostępny, gdy instrukcja jest uruchomiona. Operacja jest również przerywana bez utraty ukończonych prac. Wadą tej metody jest to, że metoda nie wykonuje tak dobrego zadania, aby zreorganizować dane jako klasterowany indeks upuszczania lub ponownego tworzenia operacji.

• Ponownie skompiluj indeks.

  Użyj ALTER INDEX z REBUILD, aby ponownie skompilować indeks. Aby uzyskać więcej informacji, zobacz ALTER INDEX (Transact-SQL).

średnia liczba bajtów wolnych na stronę i średnia gęstość strony (pełna) statystyki w zestawie wyników wskazują pełną pełną liczbę stron indeksu. Liczba Średnia liczba bajtów na stronę powinna być niska, a średnia gęstość strony (pełna) liczba powinna być wysoka dla indeksu, który nie będzie miał wielu losowych wstawiania. Usunięcie i ponowne utworzenie indeksu z określoną opcją FILLFACTOR może poprawić statystyki. Ponadto ALTER INDEX z REORGANIZE skompaktuje indeks, biorąc pod uwagę jego FILLFACTORi poprawi statystyki.

Nuta

Indeks zawierający wiele losowych wstawień i bardzo pełnych stron będzie miał zwiększoną liczbę podziałów stron. Powoduje to większą fragmentację.

Poziom fragmentacji indeksu można określić w następujący sposób:

• Porównując wartości przełączników Extent i Zakresy zeskanowane.

  Wartość przełączników zakresu powinna być jak najbardziej zbliżona do wartości Zakresy zeskanowane. Ten współczynnik jest obliczany jako wartość Gęstość skanowania. Ta wartość powinna być tak wysoka, jak to możliwe, i można je poprawić przez zmniejszenie fragmentacji indeksu.

  Nuta

  Ta metoda nie działa, jeśli indeks obejmuje wiele plików.

• Dzięki zrozumieniu wartości fragmentacji skanowania logicznego i fragmentacji skanowania w zakresie .

  fragmentacja skanowania logicznego i w mniejszym stopniu fragmentacja skanowania zakresu wartości są najlepszymi wskaźnikami poziomu fragmentacji tabeli. Obie te wartości powinny być jak najbardziej zbliżone do zera, chociaż wartość z zakresu od 0 do 10 procent może być akceptowalna.

  Nuta

  Wartość fragmentacji skanowania zakresu będzie wysoka, jeśli indeks obejmuje wiele plików. Aby zmniejszyć te wartości, należy zmniejszyć fragmentację indeksu.

Uprawnienia

Użytkownik musi być właścicielem tabeli lub być członkiem sysadmin stałej roli serwera, db_owner stałej roli bazy danych lub stałej roli bazy danych db_ddladmin.

Przykłady

A. Wyświetlanie informacji o fragmentacji tabeli

W poniższym przykładzie przedstawiono informacje o fragmentacji tabeli Employee.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

B. Użyj OBJECT_ID, aby uzyskać identyfikator tabeli i sys.indexes w celu uzyskania identyfikatora indeksu

W poniższym przykładzie użyto OBJECT_ID i widoku wykazu sys.indexes w celu uzyskania identyfikatora tabeli i identyfikatora indeksu dla indeksu AK_Product_Name tabeli Production.Product w bazie danych AdventureWorks2022.

USE AdventureWorks2022;
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. Wyświetlanie skróconego zestawu wyników dla tabeli

Poniższy przykład zwraca skrócony zestaw wyników dla tabeli Product w bazie danych AdventureWorks2022.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Wyświetlanie pełnego zestawu wyników dla każdego indeksu w każdej tabeli w bazie danych

Poniższy przykład zwraca pełny zestaw wyników tabeli dla każdego indeksu w każdej tabeli w bazie danych AdventureWorks2022.

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

E. Defragmentacja indeksów w bazie danych za pomocą polecenia DBCC SHOWCONTIG i DBCC INDEXDEFRAG

Poniższy przykład przedstawia prosty sposób defragmentacji wszystkich indeksów w bazie danych, która jest fragmentowana powyżej zadeklarowanego progu.

/*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

Zobacz też

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