DBCC SHOWCONTIG (Transact-SQL)

Si applica a: SQL Server Istanza gestita di SQL di Azure

Visualizza informazioni sulla frammentazione dei dati e degli indici per la tabella o vista specificata.

Importante

Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Usare invece sys.dm_db_index_physical_stats .

Si applica a: SQL Server 2008 (10.0.x) e versioni successive

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

table_name | table_id | view_name | view_id

Tabella o vista per verificare la presenza di informazioni sulla frammentazione. Se viene omesso, vengono controllate tutte le tabelle e le viste indicizzate nel database corrente. Per ottenere l'ID della tabella o vista, usare la funzione OBJECT_ID.

index_name | index_id

Indice per verificare la presenza di informazioni sulla frammentazione. Se viene omesso, l'istruzione elabora l'indice di base per la tabella o vista specificata. Per ottenere l'ID dell'indice, usare la vista del catalogo sys.indexes.

WITH

Specifica le opzioni per il tipo di informazioni restituite dall'istruzione DBCC.

FAST

Specifica se eseguire un'analisi rapida dell'indice con restituzione di informazioni di output minime. Un'analisi veloce non legge le pagine foglia o a livello di dati dell'indice.

ALL_INDEXES

Visualizza i risultati per tutti gli indici delle tabelle e viste specificate, anche se viene indicato un indice specifico.

TABLERESULTS

Visualizza i risultati come set di righe, con informazioni aggiuntive.

ALL_LEVELS

Supportata per compatibilità con le versioni precedenti. Anche se ALL_LEVELS viene specificato, viene elaborato solo il livello foglia dell'indice o il livello di dati della tabella.

NO_INFOMSGS

Evita la visualizzazione di tutti i messaggi informativi con livello di gravità compreso tra 0 e 10.

Set di risultati

Nella tabella seguente vengono descritte le informazioni del set di risultati.

Statistica	Descrizione
Pagine sottoposte ad analisi	Numero di pagine della tabella o dell'indice.
Extent sottoposti ad analisi	Numero di extent della tabella o dell'indice.
Cambi di extent	Numero di passaggi dell'istruzione DBCC da un extent all'altro durante l'attraversamento delle pagine della tabella o dell'indice.
Media pagine per extent	Numero di pagine per extent nella catena di pagine.
Densità di analisi [conteggio ottimale:conteggio effettivo]	Percentuale. Rapporto tra Conteggio ottimale e Conteggio effettivo. Questo valore è 100 se tutti gli elementi sono contigui, è minore di 100 in presenza di frammentazioni. Conteggio ottimale rappresenta il numero ideale di cambi di extent se tutti gli elementi fossero contigui. Conteggio effettivo rappresenta il numero effettivo di cambi di extent.
Frammentazione analisi logica	Percentuale di pagine non ordinate restituite dall'analisi delle pagine foglia di un indice. Questo numero non è rilevante per gli heap. Una pagina non ordinata è una pagina per la quale la pagina fisica successiva allocata all'indice non è la pagina a cui punta il puntatore di page successivo nella pagina foglia corrente.
Frammentazione analisi extent	Percentuale di extent non ordinati rilevati durante l'analisi delle pagine foglia di un indice. Questo numero non è rilevante per gli heap. Un extent non ordinato è uno per il quale l'extent che contiene la pagina corrente per un indice non è fisicamente l'extent successivo dopo l'extent che contiene la pagina precedente per un indice. Nota: questo numero non ha significato quando l'indice si estende su più file.
Media byte liberi per pagina	Numero medio di byte disponibili nelle pagine sottoposte ad analisi. Maggiore è il numero, minore sarà il livello di riempimento delle pagine. I numeri inferiori sono migliori se l'indice non includerà molti inserimenti casuali. Anche le dimensioni delle righe influiscono su questo valore, che risulta maggiore per righe di grandi dimensioni.
Densità media pagina (piena)	Densità media della pagina, in percentuale. Questo valore tiene conto delle dimensioni delle righe e pertanto rappresenta un'indicazione più precisa dell'effettivo livello di riempimento delle pagine. Sono preferibili valori elevati.

Quando vengono specificati table_id e FAST, DBCC SHOWCONTIG restituisce un set di risultati con solo le colonne seguenti:

• Pagine sottoposte ad analisi
• Cambi di extent
• Densità di analisi [conteggio ottimale:conteggio effettivo]
• Frammentazione analisi extent
• Frammentazione analisi logica

Quando TABLERESULTS viene specificato, DBCC SHOWCONTIG restituisce le colonne seguenti e anche le nove colonne descritte nella tabella precedente.

Statistica	Descrizione
nome oggetto	Nome della tabella o vista elaborata.
ObjectId	ID del nome di oggetto.
IndexName	Nome dell'indice elaborato. NULL per un heap.
IndexId	ID dell'indice. 0 per un heap.
Livello	Livello dell'indice. Il livello 0 corrisponde al livello foglia (o dati) dell'indice. Il livello è 0 per un heap.
Pagine	Numero di pagine che compongono tale livello dell'indice o l'intero heap.
prime righe	Numero di record di dati o dell'indice a tale livello dell'indice. Nel caso di un heap, corrisponde al numero di record di dati dell'intero heap. Per un heap, il numero di record restituiti da questa funzione potrebbe non corrispondere al numero di righe restituite eseguendo un SELECT COUNT(*) oggetto sull'heap. Questo perché una riga potrebbe contenere più record. Ad esempio, in alcune situazioni di aggiornamento, un'unica riga dell'heap potrebbe presentare un record di inoltro e un record inoltrato a seguito dell'operazione di aggiornamento. Inoltre, nell'archiviazione LOB_DATA la maggior parte delle righe LOB viene suddivisa in più record.
MinimumRecordSize	Dimensioni minime dei record in tale livello dell'indice o nell'intero heap.
MaximumRecordSize	Dimensioni massime dei record in tale livello dell'indice o nell'intero heap.
AverageRecordSize	Dimensioni medie dei record in tale livello dell'indice o nell'intero heap.
ForwardedRecords	Numero di record inoltrati in tale livello dell'indice o nell'intero heap.
Extents	Numero di extent in tale livello dell'indice o nell'intero heap.
ExtentSwitches	Numero di passaggi dell'istruzione DBCC da un extent all'altro durante l'attraversamento delle pagine della tabella o dell'indice.
AverageFreeBytes	Numero medio di byte disponibili nelle pagine sottoposte ad analisi. Maggiore è il numero, minore sarà il livello di riempimento delle pagine. I numeri inferiori sono migliori se l'indice non includerà molti inserimenti casuali. Anche le dimensioni delle righe influiscono su questo valore, che risulta maggiore per righe di grandi dimensioni.
AveragePageDensity	Densità media della pagina, in percentuale. Questo valore tiene conto delle dimensioni delle righe e pertanto rappresenta un'indicazione più precisa dell'effettivo livello di riempimento delle pagine. Sono preferibili valori elevati.
ScanDensity	Percentuale. Rapporto tra BestCount e ActualCount. Questo valore è 100 se tutti gli elementi sono contigui, è minore di 100 in presenza di frammentazioni.
BestCount	Il numero ideale di extent cambia se tutto è collegato in modo contiguo.
ActualCount	Numero effettivo di modifiche di extent.
LogicalFragmentation	Percentuale di pagine non ordinate restituite dall'analisi delle pagine foglia di un indice. Questo numero non è rilevante per gli heap. Una pagina non ordinata è una pagina per la quale la pagina fisica successiva allocata all'indice non è la pagina a cui punta il puntatore di pagina successiva nella pagina foglia corrente.
ExtentFragmentation	Percentuale di extent non ordinati rilevati durante l'analisi delle pagine foglia di un indice. Questo numero non è rilevante per gli heap. Un extent non ordinato è uno per il quale l'extent che contiene la pagina corrente per un indice non è fisicamente l'extent successivo dopo l'extent che contiene la pagina precedente per un indice. Nota: questo numero non ha significato quando l'indice si estende su più file.

Quando WITH TABLERESULTS e FAST vengono specificati, il set di risultati è uguale a quando WITH TABLERESULTS viene specificato, ad eccezione delle colonne seguenti con valori Null:

Righe	Extents
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Osservazioni:

L'istruzione DBCC SHOWCONTIG attraversa la catena di pagine a livello foglia dell'indice specificato quando viene specificato index_id . Se si specifica solo table_id oppure index_id è 0, viene eseguita l'analisi delle pagine di dati della tabella specificata. L'operazione richiede esclusivamente un blocco a livello di tabella preventivo condiviso (IS). In questo modo è possibile eseguire tutti gli aggiornamenti e gli inserimenti, con l'eccezione delle operazioni che richiedono un blocco a livello di tabella esclusivo (X). Ciò consente di ottenere una velocità di esecuzione accettabile senza riduzione della concorrenza per il numero di statistiche restituite. Tuttavia, se il comando viene usato solo per misurare la frammentazione, è consigliabile usare l'opzione WITH FAST per ottenere prestazioni ottimali. Un'analisi veloce non legge le pagine foglia o a livello di dati dell'indice. L'opzione WITH FAST non si applica a un heap.

Restrizioni

DBCC SHOWCONTIG non visualizza i dati con tipi di dati ntext, text e image . La mancata visualizzazione è dovuta al fatto gli indici di testo che archiviano dati di tipo text e image non esistono più.

Inoltre, DBCC SHOWCONTIG non supporta alcune nuove funzionalità. Ad esempio:

• Se la tabella o l'indice specificato è partizionata, DBCC SHOWCONTIG visualizza solo la prima partizione della tabella o dell'indice specificato.
• DBCC SHOWCONTIG non visualizza informazioni sull'archiviazione di overflow di righe e altri nuovi tipi di dati fuori riga, ad esempio nvarchar(max), varchar(max), varbinary(max)e xml.
• Gli indici spaziali non sono supportati da DBCC SHOWCONTIG.

Tutte le nuove funzionalità sono completamente supportate dalla DMV sys.dm_db_index_physical_stats (Transact-SQL).

Frammentazione della tabella

DBCC SHOWCONTIG determina se la tabella è fortemente frammentata. La frammentazione si verifica in seguito ai processi di modifica dei dati della tabella (istruzioni INSERT, UPDATE e DELETE). Poiché queste modifiche non vengono normalmente distribuite equamente tra le righe della tabella, la completezza di ogni pagina può variare nel tempo. Nel caso di query che eseguono l'analisi di un'intera tabella o di una parte di tabella, tale frammentazione della tabella potrebbe comportare letture di pagine aggiuntive, operazione che ostacola l'analisi parallela dei dati.

Per ridurre il livello di frammentazione di un indice molto frammentato, è possibile eseguire una delle operazioni seguenti:

• Eliminare e ricreare un indice cluster.

  Quando si ricrea un indice cluster, i dati vengono riorganizzati e si ottengono pagine di dati complete. Il livello di completezza può essere configurato usando l'opzione FILLFACTOR in CREATE INDEX. Questo metodo presenta due svantaggi, ovvero l'indice rimane offline durante l'operazione di eliminazione o ricostruzione e l'operazione è atomica. Se la creazione dell'indice viene interrotta, l'indice non viene ricreato.

• Ridisporre in ordine logico le pagine del livello foglia dell'indice.

  Usare ALTER INDEX...REORGANIZE per riordinare le pagine a livello foglia dell'indice in un ordine logico. L'operazione viene eseguita online e pertanto l'indice rimane disponibile durante l'esecuzione dell'istruzione. È inoltre possibile interrompere l'operazione senza perdere il lavoro completato. Lo svantaggio di questo metodo è che il metodo non funziona come un buon lavoro per riorganizzare i dati come operazione di eliminazione o ricreazione dell'indice cluster.

• Ricompilare l'indice.

  Usare ALTER INDEX con REBUILD per ricompilare l'indice. Per altre informazioni, vedere ALTER INDEX (Transact-SQL).

La statistica Avg. Bytes free per page e Avg. Page density (full) nel set di risultati indica l'interezza delle pagine di indice. Il numero medio di byte liberi per ogni numero di pagina deve essere basso e il numero medio di densità di pagina (pieno) deve essere elevato per un indice che non includerà molti inserimenti casuali. L'eliminazione e la ricreazione di un indice con l'opzione FILLFACTOR specificata possono migliorare le statistiche. Inoltre, ALTER INDEX con REORGANIZE compattare un indice, tenendo conto del relativo FILLFACTOR, e migliorerà le statistiche.

Nota

Per un indice con numerosi inserimenti casuali e pagine molto piene si verificherà un maggior numero di divisioni di pagina e di conseguenza una maggiore frammentazione.

È possibile determinare il livello di frammentazione di un indice nei modi seguenti:

• Tramite il confronto dei valori di Cambi di extent ed Extent sottoposti ad analisi.

  Il valore Cambi di extent deve essere il più possibile prossimo al valore Extent sottoposti ad analisi. Questo rapporto viene calcolato dal valore Densità di analisi. Tale valore deve essere il più alto possibile e può essere migliorato riducendo la frammentazione dell'indice.

  Nota

  Questo metodo non funziona se l'indice è esteso a più file.

• Tramite l'analisi dei valori Frammentazione analisi logica e Frammentazione analisi extent.

  Il valore Frammentazione analisi logica e, anche se in misura minore, il valore Frammentazione analisi extent sono i migliori indicatori del livello di frammentazione di una tabella. Entrambi i valori dovrebbero essere il più possibile prossimi allo zero, anche se possono essere accettabili valori compresi tra 0% e 10%.

  Nota

  Il valore Frammentazione analisi extent sarà elevato se l'indice è esteso a più file. Per ridurre questo valore, è necessario ridurre la frammentazione dell'indice.

Autorizzazioni

L'utente deve essere il proprietario della tabella oppure un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_owner o db_ddladmin.

Esempi

R. Visualizzare le informazioni sulla frammentazione per una tabella

Nell'esempio seguente vengono visualizzate informazioni sulla frammentazione della tabella Employee.

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

B. Usare OBJECT_ID per ottenere l'ID tabella e sys.indexes per ottenere l'ID di indice

Nell'esempio seguente vengono usate la funzione OBJECT_ID e la vista del catalogo sys.indexes per ottenere l'ID di tabella e l'ID di indice per l'indice AK_Product_Name della tabella Production.Product nel database 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. Visualizzare un set di risultati abbreviato per una tabella

Nell'esempio seguente viene restituito un set di risultati abbreviato per la tabella Productnel database AdventureWorks2022.

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

D. Visualizzare il set di risultati completo per ogni indice in ogni tabella di un database

Nell'esempio seguente viene restituito un set completo di risultati relativi a ogni indice di tutte le tabelle del database AdventureWorks2022.

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

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

Vedi anche

• 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)