DBCC INDEXDEFRAG (Transact-SQL)
Dotyczy:
programu SQL ServerAzure SQL Managed Instance
Defragmentuje indeksy 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 ALTER INDEX.
Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje
Transact-SQL konwencje składni
Składnia
DBCC INDEXDEFRAG
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
[ WITH NO_INFOMSGS ]
Argumenty
database_name | database_id | 0
Baza danych zawierająca indeks do defragmentacji. Jeśli określono wartość 0, używana jest bieżąca baza danych. Nazwy baz danych muszą być zgodne z regułami dotyczącymi identyfikatorów .
table_name | table_id | view_name | view_id
Tabela lub widok zawierający indeks do defragmentacji. Nazwy tabel i widoków muszą być zgodne z regułami dotyczącymi identyfikatorów.
index_name | index_id
Nazwa lub identyfikator indeksu do defragmentacji. Jeśli nie zostanie określona, instrukcja defragmentuje wszystkie indeksy określonej tabeli lub widoku. Nazwy indeksów muszą być zgodne z regułami dotyczącymi identyfikatorów.
partition_number | 0
Numer partycji indeksu do defragmentacji. Jeśli nie określono wartości lub jeśli określono wartość 0, instrukcja defragmentuje wszystkie partycje w określonym indeksie.
Z NO_INFOMSGS
Pomija wszystkie komunikaty informacyjne, które mają poziomy ważności od 0 do 10.
Uwagi
DBCC INDEXDEFRAG defragmentuje poziom liści indeksu, tak aby fizyczna kolejność stron odpowiadała kolejności logicznej od lewej do prawej węzłów liścia, co poprawia wydajność skanowania indeksów.
Nuta
Po uruchomieniu DBCC INDEXDEFRAG defragmentacja indeksu jest wykonywana szeregowo. Oznacza to, że operacja na pojedynczym indeksie jest wykonywana przy użyciu pojedynczego wątku. Nie ma równoległości. Ponadto operacje na wielu indeksach z tej samej instrukcji DBCC INDEXDEFRAG są wykonywane na jednym indeksie jednocześnie.
DBCC INDEXDEFRAG również kompaktuje strony indeksu, biorąc pod uwagę współczynnik wypełnienia określony podczas tworzenia indeksu. Wszystkie puste strony utworzone z powodu tej kompaktowania są usuwane. Aby uzyskać więcej informacji, zobacz Określanie współczynnika wypełnienia dla indeksu.
Jeśli indeks obejmuje więcej niż jeden plik, DBCC INDEXDEFRAG defragmentuje jeden plik naraz. Strony nie są migrowane między plikami.
DBCC INDEXDEFRAG raportuje szacowany procent ukończony co pięć minut.
DBCC INDEXDEFRAG można zatrzymać w dowolnym momencie procesu, a wszystkie ukończone prace zostaną zachowane.
W przeciwieństwie do DBCC DBREINDEXlub ogólnie operacji tworzenia indeksu, DBCC INDEXDEFRAG jest operacją online. Nie przechowuje blokad długoterminowych. W związku z tym DBCC INDEXDEFRAG nie blokuje uruchamiania zapytań ani aktualizacji. Ponieważ czas defragmentacji jest związany z poziomem fragmentacji, stosunkowo niefragmentowany indeks może być defragmentowany szybciej niż można skompilować nowy indeks. Znacznie fragmentowany indeks może trwać znacznie dłużej niż ponowne kompilowanie.
Defragmentacja jest zawsze w pełni rejestrowana, niezależnie od ustawienia modelu odzyskiwania bazy danych. Aby uzyskać więcej informacji, zobacz ALTER DATABASE (Transact-SQL). Defragmentacja silnie pofragmentowanego indeksu może wygenerować więcej dziennika niż w pełni zarejestrowane tworzenie indeksu. Jednak defragmentacja jest wykonywana jako seria krótkich transakcji, więc duży dziennik jest niepotrzebny, jeśli kopie zapasowe dzienników są często wykonywane lub jeśli ustawienie modelu odzyskiwania jest PROSTE.
Ograniczenia
DBCC INDEXDEFRAG mieszania stron liści indeksu. W związku z tym, jeśli indeks jest przeplatany z innymi indeksami na dysku, uruchomienie DBCC INDEXDEFRAG względem tego indeksu nie powoduje, że wszystkie strony liścia w indeksie są ciągłe. Aby ulepszyć klastrowanie stron, skompiluj indeks.
DBCC INDEXDEFRAG nie można użyć do defragmentacji następujących indeksów:
- Indeks wyłączony.
- Indeks z blokadą strony ustawioną na wartość OFF.
- Indeks przestrzenny.
DBCC INDEXDEFRAG nie jest obsługiwana do użycia w tabelach systemowych.
Zestawy wyników
DBCC INDEXDEFRAG zwraca następujący zestaw wyników (wartości mogą się różnić), jeśli indeks jest określony w instrukcji (chyba że określono WITH NO_INFOMSGS):
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359 346 8
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Uprawnienia
Obiekt wywołujący 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. Defragmentowanie indeksu za pomocą polecenia DBCC INDEXDEFRAG
Poniższy przykład defragmentuje wszystkie partycje indeksu PK_Product_ProductID w tabeli Production.Product w bazie danych AdventureWorks2022.
DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO
B. 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óre są fragmentowane 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