DBCC INDEXDEFRAG (Transact-SQL)
Si applica a:
SQL Server Istanza gestita di SQL di Azure
Deframmenta gli indici della tabella o vista specificata.
Importante
Questa funzionalità verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. In alternativa, usare ALTER INDEX.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Convenzioni della sintassi Transact-SQL
Sintassi
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 ]
Nota
Per visualizzare la sintassi Transact-SQL per SQL Server 2014 e versioni precedenti, vedere Documentazione delle versioni precedenti.
Argomenti
database_name | database_id | 0
Database contenente l'indice da deframmentare. Se si specifica 0, viene utilizzato il database corrente. I nomi dei database devono essere conformi alle regole per gli identificatori.
table_name | table_id | view_name | view_id
Tabella o vista contenente l'indice da deframmentare. I nomi delle tabelle e delle viste devono essere conformi alle regole per gli identificatori.
index_name | index_id
Nome o ID dell'indice da deframmentare. Se viene omesso, l'istruzione deframmenta tutti gli indici della tabella o vista specificata. I nomi degli indici devono essere conformi alle regole per gli identificatori.
partition_number | 0
Numero di partizione dell'indice da deframmentare. Se non viene specificato o viene specificato 0, l'istruzione deframmenta tutte le partizioni nell'indice specificato.
WITH NO_INFOMSGS
Evita la visualizzazione di tutti i messaggi informativi con livello di gravità compreso tra 0 e 10.
Osservazioni
DBCC INDEXDEFRAG deframmenta il livello foglia di un indice in modo che l'ordine fisico delle pagine corrisponda all'ordine logico da sinistra a destra dei nodi foglia, migliorando quindi le prestazioni di analisi degli indici.
Nota
Quando DBCC INDEXDEFRAG viene eseguita, la deframmentazione dell'indice viene eseguita in modo seriale. Ciò significa che l'operazione su un indice singolo viene eseguita tramite un thread singolo, senza parallelismo. Inoltre, le operazioni su più indici della stessa DBCC INDEXDEFRAG istruzione vengono eseguite su un indice alla volta.
DBCC INDEXDEFRAG compatta anche le pagine di un indice, prendendo in considerazione il fattore di riempimento specificato al momento della creazione dell'indice. Le pagine vuote create in seguito alla compattazione vengono rimosse. Per altre informazioni, vedere Specificare un fattore di riempimento per un indice.
Se un indice si estende su più file, DBCC INDEXDEFRAG deframmenta un file alla volta. Le pagine non vengono migrate tra file.
DBCC INDEXDEFRAG indica la percentuale stimata completata ogni cinque minuti. DBCC INDEXDEFRAG può essere arrestato in qualsiasi momento del processo e qualsiasi lavoro completato viene conservato.
A differenza di DBCC DBREINDEX, o l'operazione di compilazione dell'indice in genere, DBCC INDEXDEFRAG è un'operazione online. Non mantiene blocchi a lungo termine. Pertanto, DBCC INDEXDEFRAG non blocca l'esecuzione di query o aggiornamenti. La deframmentazione di un indice relativamente non frammentato risulta più rapida rispetto alla creazione di un nuovo indice. I tempi di deframmentazione infatti sono correlati al livello di frammentazione. Un indice fortemente frammentato potrebbe richiedere molto più tempo rispetto alla ricompilazione.
La deframmentazione viene sempre registrata indipendentemente dal modello di recupero del database impostato. Per altre informazioni, vedere ALTER DATABASE (Transact-SQL). La deframmentazione di un indice fortemente frammentato può generare più log rispetto a una creazione dell'indice completamente registrata. Dato tuttavia che la deframmentazione viene eseguita come una serie di transazioni brevi, se si eseguono backup del log frequenti o si imposta il modello di recupero su SIMPLE, non è necessario un log molto esteso.
Restrizioni
DBCC INDEXDEFRAG consente di ricompilare le pagine foglia dell'indice sul posto. Pertanto, se un indice viene interleavedato con altri indici su disco, l'esecuzione DBCC INDEXDEFRAG su tale indice non rende contigue tutte le pagine foglia nell'indice. Per migliorare il raggruppamento delle pagine, è necessario ricompilare l'indice.
DBCC INDEXDEFRAG non può essere usato per deframmentare gli indici seguenti:
- Un indice disabilitato.
- Un indice con blocco di pagina impostato su OFF.
- Un indice spaziale.
DBCC INDEXDEFRAG non è supportato per l'uso nelle tabelle di sistema.
Set di risultati
DBCC INDEXDEFRAG restituisce il set di risultati seguente (i valori possono variare) se viene specificato un indice nell'istruzione (a meno che non WITH NO_INFOMSGS venga specificato):
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.
Autorizzazioni
Il chiamante deve essere proprietario della tabella o membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_owner e db_ddladmin.
Esempi
R. Usare DBCC INDEXDEFRAG per deframmentare un indice
Nell'esempio seguente vengono deframmentate tutte le partizioni dell'indice PK_Product_ProductID nella tabella Production.Product del database AdventureWorks2022.
DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO
B. Usare DBCC SHOWCONTIG e DBCC INDEXDEFRAG per deframmentare gli indici in un database
Nell'esempio seguente viene illustrato un metodo semplice per deframmentare tutti gli indici di un database il cui livello di frammentazione è superiore alla soglia massima specificata.
/*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